Generic Alerts Query
The Filter Condition screen is used whenever a set of filter conditions needs to be defined:
Step 1. Select a Field for Filter Condition. Based on your choice, you can now select from the conditions that are available to you. Select the condition that you want to use as a filter for further queries. For example, to consider only group reservations in your query, you might choose Block Code as your filter field in this query. Notice that the list fields that appear when you select the down arrow identify the Field Name and the Table Name where the field is found to help you in making your choice.
Note: In order to extract guest profile stay data from the correct property and date range (such as for what may be required for ORS/OCIS Campaign Management), one of the following fields for the filter condition must be selected. These required fields for filtering are property and reservation-specific records, which enable the mail filter (Totals Range Screen) to identify the correct property and date range information.
- Cancelations – Last year
- Total Day Use Rooms
- Total Day Use Rooms – Last year
- Total Nights
- Total Nights – Last year
- No Shows
- No Shows – Last year
- Total Revenue
- Total Revenue – Last year
- Total Stays
- Total Stays – Last year
Step 2. Specify a Filter Condition. Based on your choice in step 2, various filter conditions will be available for you to choose from when you select the down arrow. For example, when you are looking for active accounts you want to find those whose Active Flag field contains a Y; therefore, you would select Is Equal To as the filter condition. In the field underneath, enter the value you are looking for (in our example, Y).
Notice that the extra field for the entry of a value appears only if a value is required. For example, if you selected Is Empty for the filter condition rather than Is Equal To, there would be no need for you to enter a value and the data entry field would not appear.
Or, if in Step 2 you selected a filter condition field that calls for a date or range of dates (for example, an arrival date), the filter condition options available in Step 3 might include Is After or Is Between and date entry fields would appear underneath rather than a text entry field.
Step 3. Apply the Filter Condition. Now, select the Add button to copy the query statement you created into the text area.
Repeat these steps to add follow-on statements, if they are required by your query. In our example, where you are looking for contacts in Illinois, you would add another query statement that selects the account address State field as the filter field in Step 2, and Is Equal To IL as the filter condition in Step 3.
By default, as you add new statements they are combined with those that went before using the AND operator. If you want to change an AND operator to an OR operator, highlight the AND statement, click the AND/OR button, and then click the Update button. The AND operator changes to OR. You can also change an OR operator back to an AND operator using this same process: Highlight the OR statement that you want to change to an AND statement, click the AND/OR button, then select the Update button. The OR operator changes back to AND.
To nest statements, place them in parentheses. Highlight the statement where you want the nesting to start and click on the open parent (() button. Select the Update button. Next highlight the statement that you want to be the last statement inside the parent. Click the close parent ()) button. Finally, select the Update button. When you are nesting statements, the number of open parent must equal the number of close parent.
To remove a parent from a conditional statement, click the '(' OR ')' button up to four times (depending on how many parentheses have been used in the statement), until the red parent beside the button disappears. Then click the Update button.
Note: When a date field Filter Condition is selected, a range (Before/After Business Date) can be configured for the Global Alert filter by selecting the check boxes below. By checking any of the check boxes for Day, Month, Year, the user defines which parts of the date OPERA should match. For example, if the Step 1 Filter Condition is Date of Birth and the Step 2 is set to ‘Is between’ and then the check boxes are checked and set up as Business Date -2 and Business Date 2 then OPERA will display this alert for anybody that has a Birthday 2 days before up to 2 days after the current business date. Since in case of the birthday, it does not matter which year the guest was born, the Year check box has to be unchecked so OPERA is not looking for Guests that were born in the current year (since the business date is being matched, which is in the current year). All parts of the date can be included/excluded using those check boxes depending on what the user is attempting to set up.
Note: Although both AND and OR are logical operators, AND is "stronger". It binds the logical expressions on either side of it more tightly than the OR operator does (technically, AND is said to have a "higher precedence" than OR). For example, in this clause:
where Accounts->Industry Code = 'A' OR Accounts- >Industry Code = 'B' AND Accounts- >Country = 'ITALY'
the AND is always acted upon first. You can break this bonding by using parentheses to enclose those expressions you want to be interpreted together. Parentheses override the normal precedence:
where (Accounts->Industry Code = 'A' OR Accounts- >Industry Code = 'B') AND Accounts- >Country = 'ITALY'
Or, to get the same result the query could be written like this:
where Accounts->Country = 'ITALY' AND (Accounts->Industry Code = 'A' OR Accounts- >Industry Code = 'B')
The configured filters will be used to determine if the special will be attached to the reservation.
To edit an existing query, re-enter the existing Global Alert query. In the text area in the lower part of the screen, highlight the statement you want to edit. When you do so, the elements of that query appear where you can change the filter field and filter condition as necessary.
When you have made your changes, select the Update button to record the modified query statement in the text area.
To remove a statement entirely, highlight the statement in the text area and select the Delete button.