Prompt Fields
When in the SQL Editor, use F8 to assist in entering prompts.
-
If the prompt text contains the word "Date" anywhere in the User Prompt Text, a Calendar Prompt will appear rather than a string-text prompt. This is very helpful when a user has to enter a Date and you want the system to format the Date properly for the SQL statement.
-
If the prompt text does not contain the word "Date" anywhere in the User Prompt Text, a free-form text box will appear.
All Prompt Fields are in the form of: {?Prompt Text to display to user}
For example, the following SQL Statement will prompt the user for a date so the query/report will only list guests checking out on a certain date.
SELECT Firstname, Lastname, RoomNumber, CheckIn, CheckOut FROM FOLIOHD WHERE FolioStatus = 'R' AND checkout = '{?Enter Checkout Date}' ORDER BY RoomNumber
Notice that the apostrophe's are still used since Dates are required to be in apostrophe. Once the user runs the report, and selects a valid date, the SQL Query would look something like this:
SELECT Firstname, Lastname, RoomNumber, CheckIn, CheckOut FROM FOLIOHD WHERE FolioStatus = 'R' AND checkout = '2006-05-31' ORDER BY RoomNumber
If you require data to be in a certain case or format, you can encompass the returned value using value SQL Syntax. For example, here we want to prompt the user to enter a State Abbreviation, and we want to make sure that the Select statement actually selects it properly as UpperCase.
SELECT Firstname, Lastname, city, state, zipcode FROM FOLIOHD WHERE FolioStatus = 'R' AND state = UPPER('{?Enter State to Find}') ORDER BY RoomNumber
Prompt Fields with the same name retain the same value for every instance in the query. Example: if you have a prompt field of '{?Enter Audit Date}' twice, the user is only prompted once for the value.
Format of Prompt Field
The prompt field is in the forms of:
| Simple (Text) | {?Prompt Text for User} |
| Simple (Date) | {?Prompt Text for User with the word Date appearing anywhere} |
| Simple with Datatype | {?Prompt Text for User%@(Clarion picture)} (Use simply %@B for True/False Checkbox) |
%@sxxx string of xxx characters, capitalize first letters | |
%@Sxxx string of xxx characters, capitalize all letters | |
%@nxx.y numeric string of x length with y decimals | |
%@n-xx.y numeric string of x length with y decimals allowing negative | |
| Simple with Default Value | {?Prompt Text[DefaultValue]} |
| Lookup Table | {?Prompt Text for User\\TABLE.FIELD[,ACTUAL VALUE][\\WHERE CLAUSE]} |
| Lookup Table (Multiple) | {?Prompt Text for User\\?TABLE.FIELD[,ACTUAL VALUE][\\WHERE CLAUSE]} |
| Lookup Values | ``` {?Prompt Text for User\"Value |
| Lookup Values (Multiple) | ```{?Prompt Text for User\?"Value |
| Lookup with Date Range | {?Prompt Text for User%%} |
| Set Variable Value | {?Variable\\[SQL Statement]} |
| Special Lookup Window | {?@PICKCURRENCY} to select a valid foreign currency |
{?@PICKRATECODE} to pick a valid rate code | |
{?@PICKROOMTYPE} to pick a valid room type | |
{?@PICKHOLDTYPE} to pick a valid hold type | |
{?@PICKUSER} to pick User Profile initials | |
{?@PICKSALESPER} to pick IQ-POS Employee ID |
Other Forms of Prompt for Value
To Pick from a table:
{?Prompt Text For User\\TABLENAME.FIELDNAME}
-or-
To Pick from a table:
{?Prompt Text For User\\TABLENAME.FIELDNAME\\WHERE TABLENAME.FIELDID = 'ZZ'}
-or-
To Pick from a table:
{?Prompt Text For User\\TABLENAME.FIELDNAME,ALTFIELDNAME\\WHERE TABLENAME.FIELDID = 'ZZ'}
AltFieldName would be the actual value to return rather than the FIELDNAME being displayed.
-or-
To Pick from a list:
{?Prompt Text For User\\"Value1|Value2|Value3"}
-or-
To Get a Date Range:
{?Prompt Text for User%%}
-or-
To Get a Checkbox:
{?Prompt Checkbox Text for User%@B}
-or-
To Get a 10 char field:
{?Prompt Text For User%@s10} (Capitalize first letter entered)
-or-
To Get a 10 char field:
{?Prompt Text For User%@S10} (Capitalize all letters entered)
-or-
To Get a currency field:
{?Prompt Text For User%@n-13.2}
-or-
To set variable via SQL:
{?Variable\\[SQL Statement that results one row, one column]}
-or-
To set a default value:
**{?Variable[Default Value]} **
{?Variable%s4[RACK]}
For example:
SELECT Firstname, Lastname, city, state, zipcode FROM FOLIOHD WHERE CHECKIN BETWEEN {?Enter Lower and Higher Date%%}
is the same as:
SELECT Firstname, Lastname, city, state, zipcode FROM FOLIOHD WHERE CHECKIN BETWEEN '{?Enter Lower Date}' AND '{?Enter Higher Date}'
The difference is that the first option is less clicks for the user running the query, shorter syntax and no single quotes or "AND" needed.
In the following example, the system will show the company name but return the company account number.
SELECT RESERVE.LASTNAME,RESERVE.FIRSTNAME,RESERVE.CHECKIN, RESERVE.CHECKOUT FROM RESERVE
WHERE DIRECTBILL = {?Select Company\\CITYHD.NAME,ACCOUNT\\WHERE CITYHD.DIRECTBILL = 1}
ORDER BY LASTNAME,FIRSTNAME
In the following example, instead of prompting the user for a value, the system will find the highest folio balance and use it in the text of the report footer.
SELECT Number, Firstname, Lastname, Balance FROM FOLIODT
ORDER BY NUMBER
SETREPORTFOOTER 'Highest amount a customer owes is: {?Highest Folio\\[select max(balance) from foliodt]}'