Query View Builder
The Data Pool supports joining in process queries to facilitate data population/propagation in a form but not to control drop-down/prompt field results. The Query View Builder utility aims to fill this gap by taking process queries and producing/building SQL view definitions with the same SQL logic. These generated views can then be leveraged in the Data Pool to control drop-down and prompt field results.
Navigation: Manage GT eForms > GT Utilities > Query View Builder
Note: The Query View Builder is currently only supported on Oracle databases. Contact support@gideontaylor.com for help extending the tool for other database platforms.
Query View Builder is intended to be used by advanced power-users only. It does not perform any validation on SQL/views produced with the tool or prevent the user from creating views that do not work, perform badly, or otherwise do not function as desired.
Additionally, the tool is not intended to be used to convert queries with great complexity. We expect there to be cases in which the tool is insufficient and developer time will be required to produce a view normally in App Designer.
Using Query View Builder
Search Page
The Find an Existing Value search page allows you to search for existing views that have been created/started with the Query View Builder. Only views created by the utility will return in the search.
The Add a New Value search page allows you to specify a name for a new SQL View definition. GT recommends following a consistent naming convention for views created with the tool. If a user species a Record (Table) Name that already exists in the system, the following error will surface:
Creating/Editing a View
Selecting a Query Object
The first step in creating/editing a view produced with Query View Builder is selecting a query in the Query Name prompt field.
Note: Query View Builder only supports the use of process query definitions. If a target query is not a process query, a process query version of the query should be created. Using "Save As" while editing a query in Query Manager will allow this. You should verify the new process query still runs correctly before using it with Query View Builder.
Setting Field Properties
After selecting a process query, a new section will appear which lists all fields added to the Fields tab of the query.
| Field | Description |
|---|---|
| Key | Keys are the primary keys for the table, the combination of which should produce a single, unique row. Key fields will appear in the Data Pool for joining. NOTE: A field cannot be both a Key and Alternate Search Key. Picking one will remove the checkbox from the other. |
| Alternate Search Keys | Alternate Search Keys appear as prompt criteria fields that can be used to limit prompt search results. |
| List Box Items | List Box Items will appear as results fields in prompt searches. |
Adjusting SQL (Advanced Users Only)
The View SQL section is intended to be used by advanced users only, as it is very possible to produce bad SQL that will not build, or will cause problems in the form. As such, it is collapsed by default.
Any manual changes to default SQL is done at the user's own risk. When necessary, it should be done very carefully and by someone with extensive SQL experience. Query View Builder does not perform any validation to ensure the SQL is both a) valid syntactically, and b) will perform the intended function.
Building the View
When ready, click the Generate View button. The behavior of this button differs when creating a brand new view vs. editing an existing view.
Brand New Views
For brand new views, clicking the Generate View button will produce a series of messages, including a fatal PeopleCode SQL error will occur. This is expected behavior and can be safely ignored if it is the first time the view is being created.
The first message will look like this:
Upon clicking Yes, a fatal PeopleCode SQL error will appear:
After which, a message that looks like this will appear:
Then, you will see a message that looks like this:
Note: GT recognizes that surfacing the fatal PeopleCode SQL error is not ideal. There are limitations in handling certain kinds of SQL errors, like this one, that are outside GT's control.
Existing Views
When editing existing views, clicking the Generate View button will produce a series of messages:
The first message will look like this:
Upon clicking Yes, a successful build message should appear:
What to Do If No Success Message Appears
If a fatal SQL error occurs and no success message came up, check the query to confirm it works and validate any changes that were made in the "View SQL" section.
Enabling/Disabling Query View Builder
A new setting has been added to the Form Installation Table which can be used to enable/disable the use of Query View Builder in the environment. By default, Query View Builder will be enabled.
Documentation for the Form Installation Table can be found here.
Migrating Query View Builder Views
Views created by the Query View Builder do not create matching SQL objects, so they cannot be migrated via App Designer project. It is recommended to migrate via DMS by exporting the 'G3QUERY_VIEWS' and 'G3QRY_VIEW_FLDS' records where 'RECNAME' equals the name of the view you are exporting.
Note: GT will release a fix soon after
3.70.00that will make migrating views built with the Query View Builder easier.