Use the following guidelines when writing your queries for Autofill operations:
You must name all of the columns whose data you wish to retrieve in your query. You cannot use “SELECT * “ to get all of the columns, as Formatta E-Forms Manager needs the column names to map back to form fields when populating a form. For example:
Invalid:
SELECT * FROM branch_locations WHERE region = 'Midwest'
Valid:
SELECT location_id, location_name, city FROM branch_locations WHERE region = 'Midwest'
You must provide aliases for values retrieved by your query when the values rely on functions, concatenations, or involve table names. For example:
Invalid:
SELECT first_name + ' ' + last_name FROM employee WHERE department = 'HR'
Valid:
SELECT first_name + ' ' + last_name AS full_name FROM employee WHERE department = 'HR'
Invalid:
SELECT emp.first_name FROM employee emp WHERE department = 'HR'
Valid:
SELECT emp.first_name AS first_name FROM employee emp WHERE department = 'HR'
Invalid:
SELECT first_name, last_name, to_char(date_of_birth, 'MM/DD/YYYY') FROM employee WHERE department = 'HR'
Valid:
SELECT first_name, last_name, to_char(date_of_birth, 'MM/DD/YYYY') AS birth_date FROM employee WHERE department = 'HR'
You may use table joins in your query, but when doing so, be sure to alias the columns you select so that your result set description does not contain table name references. For example:
Invalid:
SELECT loc.location_id, loc.location_name, reg.city FROM branch_locations loc, regions reg WHERE loc.region_id = reg.region_id AND reg.region_code=5
Valid:
SELECT loc.location_id AS location_id, loc.location_name AS location_name, reg.city AS city FROM ranch_locations loc, regions reg WHERE loc.region_id = reg.region_id AND reg.region_code=5
When your SQL queries use parameters (either hard-coded into the query or supplied from the form) you must be sure to quote non-numeric parameters as you would normally do. For information on providing dynamic query parameters from form fields or from Filler popup dialogs, see the sections on Form Query Parameter Guidelines and Dialog Query Parameter Guidelines.
In order for Formatta E-Forms Manager Autofill to support the widest possible variety of data types, without requiring undue complexity in the Autofill Handler configuration process, Formatta E-Forms Manager automatically converts all data in query results to character string format prior to making it available to form fields. Because of this, you should follow the guidelines below when writing you queries:
Use only standard data types (those which can be read as character strings) and no SQL3 data types such as BLOBs, CLOBs, Arrays, References, etc.
If you want to apply formatting that cannot be easily done within the form to data retrieved, use the database's built-in functions as part of the query, so that the result is already formatted. For example, if you want to retrieve a date but display it in a format such as "December 1, 2012", you might use a date function in your query similar to the one highlighted in the example query below:
SELECT to_char(completion_date, 'MONTH DD, YYYY') FROM article WHERE article_id = $PFF.article_id
Note that while data of all types are converted to character format, this usually means no extra effort on your part for the handling of numeric or other data types. This data is generally re-converted automatically by the form when placed into its fields to take on the data type and format required by the form. This allows formatting, validations, and other formulas that use data from an Autofill operation to behave within a form exactly as if they had been keyed in by a form user.