In some cases, you may want to retrieve multiple rows of similar data from a database and place it into a form, either into a table object or into standard text fields laid out in a tabular format. Formatta E-Forms Manager Autofill gives you this capability by allowing you to map the data elements of query results that can contain multiple records to repeating sets of fields in a form.
There are several steps in the configuration of this type of Autofill that are slightly different from setting up the other types:
You must define your Autofill Query as the Multiple-Repeating type, so that Formatta E-Forms Manager knows how you intend to map the form to your query.
Your form design must either use Table controls to accept Autofill data for this type of Autofill, or you must follow specific field naming rules if you are using standard text fields.
When you map the Autofill Query columns to field names in the creation of an Autofill Handler, fields shown for selection are grouped by name, so that a single query column can be mapped to an indefinite number of form fields understood as belonging to the same column in a table.
See below for specific instructions on how to configure this type of Autofill.
Create or edit the design the form that will accept the Autofill data and display in a tabular fashion. When it comes to defining the repeating sections of data, you may either use a Table control, or you may create multiple rows of standard fields.
When you use a Table control in your form design, Formatta Designer takes care of naming the table fields, such that each field contains information in its name that identifies the row and column position of the cell it represents. When a form containing a Table control is placed in the Formatta E-Forms Manager catalog, Formatta E-Forms Manager determines that there is a table in the form, and is thus able to make the fields in the table control available for Autofill using a Multiple-Repeating type of Autofill Query.
If you used a Table control in Designer to create that section of the form, the fields representing table cells were automatically assigned names that represent their row and column position in the table, e.g., Table1.ColB[1], Table1.ColB[2], etc. Formatta E-Forms Manager uses this information to allow table fields to be set up for Multiple-Repeating Autofill, and no renaming of table cell fields is necessary.
If instead you want to use standard fields for a multiple-row Autofill operation instead of using a table, you must pay special attention to how you name the fields that you wish to populate. For non-Table fields, Formatta E-Forms Manager expects the following with regard to the naming of repeating fields so that they may be identified by the system for use with this type of Autofill:
Each field name must end with a number representing the row in which the field exists.
Row numbers represented in the field names should be sequential.
Names of fields that fall within the same "column" must be identical up to the row number at the end of the field name.
For example, let's use a tabular layout using standard text fields instead of a table control. The fields in the Description column are named description_1, description_2, description_3, and so on. Fields in the Quantity column are named qty_1, qty_2, qty_3, and so on. The number at the end of each field name identifies the order in which it should be populated relative to the record set retrieved from the database. So you may create as many rows as you wish, but you must be sure to name them so that in each "cell" the number at the end of the field is exactly 1 more than in the previous row, and the rest of the field names must exactly match the field names in the prior row.
NOTE: If you want to use Autofill to populate rows of data that span more than one page of a form, you cannot use a Table control in your form design, since a Table can only sit on a single page. You should instead use standard text fields that conform to the naming convention described above.
To have the form call an Autofill Handler for this type of Autofill, the procedures and options are the same as for the other types of Autofill. See Form Setup for Autofill for more information.
Once you have configured your form with a table control and/or fields named appropriately for tabular Autofill, you can upload the form to Formatta E-Forms Manager then create the Autofill Query that will be used to populate your form with tabular data.
The process for creating this type of query is the same as for other Autofill types, except that on first screen of the Query Creation wizard you must specify Multiple-Repeating for your number of expected results. This tells Formatta E-Forms Manager that an indefinite number of rows will be returned from the query and that this query may be used to populate a form with tabular data.
The Autofill setup for any forms that use the Multiple-Repeating type of query will require the naming convention described in Part 1 above.
As with the other query types, you may use the $PFF, $DIALOG, and $REQUEST parameters to provide data at runtime. For more information on creating queries and providing parameters dynamically, see Autofill Query Setup.
NOTE: Be sure to order the result set using an ORDER BY clause in your SQL query to make sure that the results appear in an expected order in the form.
When defining an Autofill Handler that will provide tabular data to a form, the mapping of query results to form fields differs from the other types of Autofill in the way that query result fields are mapped to the form. Instead of mapping a single query result column to a single form field, you instead map a single query result column to a set of fields in the form.
Since Formatta E-Forms Manager enforces a specific naming convention for tabular fields, it is able to represent entire "columns" in the form as single entities on the Autofill mapping screen, allowing you to easily map query results to entire columns in the same manner and just as easily as you map query results to individual fields when you define the other types of Autofill operations.
The instructions that follow focus mainly on the parts of the Autofill Handler configuration having to do with the Multiple-Repeating (or tabular) Autofill. For more specific instructions on the other aspects of handler configuration, you may also see Autofill Handler Setup.
Provide a name for your Autofill Handler (using the same value supplied in the form if you have set up the form to invoke your Autofill Handler already).
You may choose either the On Download or Remote Command action type, depending on how you want Autofill to be invoked.
Choose the Direct to Form Autofill type.
You may choose the settings for Client Authentication and handling of No Results any way you wish.
Choose the Autofill Data Source and Query that you wish to use. (The Query you choose should be of the Multiple-Repeating type).
This step makes use of the Autofill Query you chose in the prior step, along with the naming of fields used in the form design, allowing you to map query results to repeating fields.
The following describes and provides instructions for each control on this screen:
Query Columns List: This is a list of the columns specified for retrieval in the Autofill Query that will be used with this handler. You may choose one column at a time in this list to map to a set of form fields.
Form Fields List: Each item in this list represents a group of fields in the form in which the fields are named the same except for the row index number at the end of each field name. For example, if your form contains five rows of invoice line items, and each row contains a description field in which the fields are named description_1, description_2, description_3 and so on, this group of fields will show up in the list as description_#. Likewise, if your form contains a table, the fields for each column in the table will be represented as a grouping in this list, with names like Table1.ColB[#].
Map Button / Mapped Field List: Create each mapping of query column to form field group by selecting query column on a left and a field group on the right then clicking the Map button. The mapping you created shows up in the list at the bottom of the screen.
Multiple Results Range: This required setting allows you to specify exactly which rows of fields you want to populate with query results. Many tables use read-only fields in the first row as labels, or supply formula-based rows in the middle or end of a table, none of which you would want to be populate using an Autofill operation. So this is useful when you want to start or end the population of a table on a specific row, or exclude specific rows or ranges from the Autofill operation. To specify the rows or ranges of rows you want to populate, you can:
Use a hyphenated range of rows, such as 1-10: This would populate rows 1 through 10 of the table with the results of the query.
Use a comma delimited list of specific rows, such as 2,3,4,6,10. This would populate rows 2, 3, 4, 6, and 10 with the results of the Autofill query.
Use a combination of hyphenated range(s) and comma delimited specific rows, such as 2, 3-8, 11. This would populate rows 2 through 8 and row 11 with the results of the Autofill query.
Whether you wish to have the Autofill operation skip rows or not, you must always specify a range here. If you want to populate all rows, just specify 1-x where x is the number of rows in the table.
If the Autofill query returns less rows than there are specified in this range value, Formatta E-Forms Manager will populate as many rows as there are results for, in order. However, if the query returns more rows than specified in this range, the Autofill operation will not be performed.
Note: If the Autofill query returns more rows than the results range defined for the form allows, the Autofill operation will not be performed. This keeps Formatta E-Forms Manager from placing an incomplete record set into the form, which would likely have unintended results.