An Autofill Query in Formatta E-Forms Manager defines the specific data that will be retrieved from the Autofill Data Source. Formatta E-Forms Manager lets you define an Autofill Query by walking you through a wizard that collects the necessary information to set it up properly. Each query definition requires you to enter specific information that will determine how it is used by Autofill Handlers to retrieve data and populate forms.
To access the Autofill Query Setup wizard in Formatta E-Forms Manager, go to the Administration tab, and choose the DB Integration option.
From the Database Integration screen, choose the Autofill tab and click the New Autofill Query link.
This starts a wizard that allows you to step through the configuration of your query. You may proceed through the wizard using the Next, Back and Cancel buttons. During the configuration process, you will provide information about the query and how input parameters may be supplied to it, along with information about the expected results, using the fields described below:
Query Name: Provides a unique name for the query, which can be referenced when setting up a form for Autofill. Enter a new name for your Autofill Query in this field. This name should be descriptive of the what the query does, as it will be referenced when configuring Autofill Handlers.
Number of Expected Results: Specifies whether the query is supposed to return one or multiple results, or multiple results in a wildcard-type repeating fashion for use in pre-populating tabular data. This determines which types of Autofill Handlers can use the query. For example, a handler that returns "Multiple" results will not be able to populate standard form fields directly - it must instead be used to populate a combo box or choice list in a popup dialog. The "Multiple-Repeating" option will allow the query results to populate tabular data in a form. Click the radio button for the appropriate number of results in your query.
Single: Choose this type of query if you expect only one record to be returned. The results of this type of query can be used to populate form fields directly. If a query of this type finds more than one record, the Autofill operation will not process.
Multiple: Choose this type of query if you want to populate a drop-down list box field (combo-box) in a form with multiple options retrieved from a database, or if you want to present the user with a list of options to choose from in order to populate certain form fields.
Multiple-Repeating: This type of query lets you use an Autofill operation to populate repeating-style rows of data on a form. This is useful for populating tabular data. Use of this type of query requires that you name the fields you want to populate with incrementing numbers for each row of data. For more information on this, see the section on Form Setup for Autofill.
SQL Query: The actual query that will be run against the database when the Autofill Handler is invoked. This data must be input in SQL language to function properly. Your query may also include syntax that is vendor-specific for the database it will be used against. For help writing your query, see the SQL Query Guidelines.
Query Parameters: Formatta E-Forms Manager allows you to dynamically provide data to your queries as query parameters in several different ways, described below:
Dialog Query Parameters: By using the $DIALOG keyword in an Autofill query, you can provide data to the query before it is executed by presenting the form user with a pop-up dialog which gathers information then passes it to the query before it is run. In your query setup, you may specify placeholders for these types of parameters using the $DIALOG keyword, followed by a field identifier that you specify (e.g., $DIALOG.user_id). When you use the $DIALOG keyword in your query, Formatta E-Forms Manager understands that it will need to present a pop-up dialog to the form user before the query is run. The next screen of the wizard will then prompt you for the following information about how to present the dialog window:
Dialog Message: In this field, you can enter a custom message that will be displayed at the top of the dialog window when the form user initiates an Autofill operation. This allows you to provide specific instructions on what type of information the user should provide in the dialog fields.
Dialog Field Label: In this field, you specify the label that will appear next to the dialog field, instructing the form user on what to enter into the field. For example, if the query parameter dialog needs the user to enter an account number, your label might say "Account Number."
Mask Input: If you check this box, the data entry field will show asterisks in place of characters input by the user. this is useful when a query parameter represents sensitive information such as a password.
Display Order: This value determines the order in which the label and field for the query parameter are displayed in the query parameter pop-up window.
Form Query Parameters: By using the $PFF keyword in your query, you can provide data to an Autofill query directly from one or more fields in the form that is open on the end user's desktop. In your query setup, you would specify the actual field names containing the data for these parameters by using the $PFF keyword, followed by a form field name (e.g., $PFF.state_code). If you use this syntax, Formatta E-Forms Manager will read these tokens and present them to you for verification on the next screen.
Request Query Parameters: By using the $REQUEST keyword in an Autofill query, you can tell Formatta E-Forms Manager to gather data needed in the query from querystring parameters in the HTTP request that was used to download the form from Formatta E-Forms Manager. This option allows users to download forms pre-populated with data from specifically-targeted records in a database. You implement this by providing SQL query parameters as request parameters in the published form's unique download URL. For example, by using $REQUEST.invoice_number in your Autofill query along with a setting querystring parameter such as &invoice_id=5 in the form's download URL, you can have Formatta E-Forms Manager populate a form with specific invoice information before the form is downloaded.
Default Mappings: If multiple forms use the same Autofill Query, you may specify default mappings of query results to form fields in order to accelerate the Autofill creation process for these forms. Formatta E-Forms Manager will show you the columns from your SQL query and provide a space next to each into which you may enter a Default Form Field Name. Here you may enter the name of the field on your form you want this data mapped to. Specifying form field names here is optional. If you do, the field names will be automatically mapped to the query when you go through the Autofill Handler Setup for a form that uses this query. During the Autofill Handler Setup, you may override any default query-to-field mappings if you wish.
When you have completed the Autofill Query Setup, click Finish.
Completing Autofill Query Setup returns you to the Autofill section of the DB Integration screen. You now see your queries in a list which is ordered by query name. Each query listing also specifies the number of expected results (Single, Multiple, or Multiple-Repeating) and an indication of whether or not it is in use or editable.
Items in the list include:
Query Name: The unique name assigned to the query definition. Clicking here shows a summary of the query details in a new screen.
# of Expected Results: Specifies whether the query is supposed to return a single result, multiple results, or multiple results in a repeating fashion for tabular data Autofill.
Edit: Allows you to re-enter the wizard and modify your query. Cannot be edited when In Use.
Delete: Allows you to access and delete your query. Cannot be deleted when In Use.
In Use: Specifies that one or more Autofill Handler(s) that use the query in question have been enabled and are in use for forms in the catalog. While in this state, the query cannot be edited or deleted without first disabling the Autofill Handler(s) that use it.
If you want to edit the query, you may click the In Use link to view and/or automatically disable any Autofill Handlers that use the query. If you disable the handlers from this window, you will then be able to edit the query.
Note: If you disable an Autofill Query from this screen, you will then have to go to an Autofill Handler that uses the query to re-enable it.
Once you are finished setting up all your queries, you can now configure your Autofill Handlers.
Once an Autofill Query has been defined, you can test your query in order to verify proper syntax and operation before assigning it to a form. To test an Autofill Query:
Step 1: Choose the Autofill Query you wish to test from the Autofill Query list. Click on the row showing the query to open the Query Details screen.
Step 2: From the Query Details screen, click the "Test Query" button. This will open a dialog for testing the query.
Step 3: On the Test Autofill Query dialog, choose the Data Source against which you want to test a run of the query. If the query requires any parameters (from the form, from a dialog, or from a request parameter), a list of the named parameters will be shown, and next to each an entry field will be available. Into each parameter field, enter a sample value. Use values that you know should result in one or more records being found in the database. Do not include quotes around character or date values, as those should already be accounted for within your query syntax.
Step 4: Click the "Continue" button. The query will be run using any parameter values you provided, against the data source you chose. If the query runs successfully, a message will indicate how many records were retrieved by the query.
If the query fails, the error message provided by the database will appear, and should indicate specifically what is wrong with your query. You should then be able to edit your query to correct any errors.
