If you want to provide data for your query that is not available in the form itself, but may be supplied by the form user, you can do so by using the special $DIALOG prefix followed by an identifier that you create. This value will represent a field in a pop-up dialog that will be presented to the user just before the query is run. When you use this syntax, Formatta E-Forms Manager sends a dialog window back to the form to gather the requested data from the user, then substitutes this special identifier with the actual values supplied by the user in the query.
For example, if you wanted to prompt a user to provide a region code and state to the query from a pop-up dialog, you would write a query like:
SELECT location_id, location_name, city FROM branch_locations WHERE region = $DIALOG.region_code AND state_code = '$DIALOG.state'
The names you assign to your dialog fields need not match form field names, but they should somehow be descriptive of the data that will be placed into the field. These field names cannot contain spaces or special characters.
If your query needs to use the same parameter value more than once, you may specify the same name for the dialog field multiple times in the query. Formatta E-Forms Manager will collect the parameter value using a single data field in the Filler pop-up dialog rather than prompting the user multiple times for the same information.
As with the use of form query parameters, this type of query also allows you to provide one or multiple parameters from the dialog.
In addition, you may combine the use of form and dialog query parameters if you want to gather some data from the form and other data directly from the user. For example, the query below get its region value from a dialog window and its state_code value from a field in the form named state. Note that in this example, the region column is a numeric column and the state_code column contains character data:
SELECT location_id, location_name, city FROM branch_locations WHERE region = $DIALOG.region_code AND state_code = '$PFF.state'
When you specify a form field as a query parameter using the $DIALOG or $PFF syntax, make sure that you properly enclose the parameter in quotes if the query parameter is for a non-numeric column. If the query parameter is for a numeric column, then you may use it in your query without quotes.