Automatically Filling Fields with Lookup Rules

Lookup rules for form fields attempt to access data from your pre-configured rules, and use this data for pre-populating fields or auto-suggesting field values. If you have data in an external database, you must import the relevant data into lookup tables before you can use lookups on that data.

Business processes can look up data from lookup table queries, formulas, application connections, web request rules, and decision tables.

To configure lookup rules, click on the Lookup Rules tab in the form designer.

For best performance, turn on pagination for filling a table/collection and use drop-downs instead of checkbox or radio buttons.

Lookups with Lookup Tables

If you have table-like data from an external source, this should be inserted into a lookup table. Then, you can create a query that business processes can use for lookups.

Using lookups with queries, you can autofill fields or auto-suggest field values in the following ways:

  • Auto-fill a checkbox, dropdown, or radio button field with the values of a lookup table column.
  • For a single-value fields like a single line fields and number fields, auto-suggest field values as the user is filling the field.
  • If the lookup table member has only one value, auto-populate a single-value field with that value.

In the following example, we have constructed a query that contains data about possible choices of coffee beans, which has a WHERE clause with an input value of %(Beans) that has "Set as input parameter" selected. We want to auto-populate a drop-down menu on the form with the possible origins of those beans (Kenya, Panama, etc.), as listed in the query.

Table showing business entity query

In the form designer, we have a drop-down field named "Coffee Origins" that offers choices based on the values of CoffeeTypes_Origin in this query. We have configured the form to use the lookup table named Beans, and to fill the Coffee Origins field with values from the column CoffeeTypes_Origin.

Lookup rule using a business entity query

To configure a lookup from a query

These instructions assume that the data you intend to use is already stored in one or more lookup tables, and you have created and published a query that can pull that data from those tables.

  1. In the form designer, navigate to the Lookup Rules tab.
  2. Click Add rule, or, if there are no lookup rules yet, New lookup rule.
  3. Under Select rule type, select Lookup Table Query.
  4. In the next dropdown menu, select the query that you want to use.
  5. Depending on the selected query, you may have to select which fields should match input parameters in order for auto-filling to occur. Select the relevant fields in the drop-down menu in each When condition.
  6. Click Add output parameter .
  7. For each field that you want to auto-fill, specify a Fill condition.
    1. From the menu after Fill, select the field that you want to fill (or to have auto-suggestions for).
    2. From the menu after with, select the column of the query that should be used for the auto-population or auto-suggestion.
    3. Click + to add more Fill conditions.
  8. You may not want to enable to lookup rule right away, especially if you plan to work on it more. Turn off the Enable lookup rule toggle to disable the rule for now. When the form is saved, the lookup rules will be saved whether they are enabled or not.
  9. To add more rules that use this query, click the + icon and repeat steps 2-9 for each rule you want to add.

Lookups Using Formulas

In process automation, you can configure and store formulas to be used across different applications. This saves you the trouble of having to configure the same formula repeatedly to be used in different kinds of processes. With lookup rules, you can automatically fill a field using a formula that has been stored as a rule. For example, if your organization has a complicated formula that calculates the total reimbursement an employee will get for the travel expenses submitted on a form, they can store this formula as a rule and use it in multiple forms or workflows.

To use a formula in a lookup rule

These instructions assume that you have already created the formula as a rule and published it.

  1. In the form designer, navigate to the Lookup Rules tab.
  2. Click Add rule, or, if there are no lookup rules yet, New lookup rule.
  3. Under Select rule type, select Formula.
  4. In the next drop-down menu, select the name of the formula you want to use.
  5. In the drop-down menu after When, select the field whose value should be the input parameter in the formula. Do this for each When statement that appears. The formula will perform calculations on the input values.
  6. Optional: If you have more than one input parameter that must match for the calculation to take place, you can clear the Show Auto Fill Button option if you do not want the process to automatically recalculate field values when the fields that must match have their values changed. If you clear this option, then the form will have an autofill button that the user can click to rerun their calculation after they change any relevant values.
  7. Click Add output parameter.
  8. In the drop-down menu after Fill, select the field that should be filled with the formula's output.
  9. In the drop-down menu after with, select the output parameter that will fill the previously specified field.
  10. If you want more fields to be filled with the output parameter, click the + icon and repeat the previous step.

Lookups Using Decision Tables

Decision tables can be used to capture logic that is used in processes, without having to configure that logic using gateways, outflows, and sequence flows. This means that the same logical rules can be easily shared across multiple processes, without having to configure anew the logic in the process diagram for each process.

In the following example, we have a decision table that decides which level of management a purchase order should be routed to, depending on the dollar amount of the purchase. If the purchase is less than $10,000, it is routed to a lower level manager. If it is more than $10,000, it is routed to a higher level manager.

Sample decision table

We use this decision table in a form to fill a field based on the output from the table. The inputs will come from another field in the same form.

Later on in the process, the value of the Normal Review field can be used to determine where the form is routed. If Normal Review has the value "true", it is routed to a lower level manager. Otherwise, it is routed to a higher level manager.

To configure a lookup rule using a decision table

These instructions assume that you have already configured the decision table and published it.

  1. In the form designer, navigate to the Lookup Rules tab.
  2. Click Add rule, or, if there are no lookup rules yet, New lookup rule.
  3. Under Select rule type, select Decision Table.
  4. In the next drop-down menu, select the name of the decision table you want to use.
  5. For each When condition that appears, select the field values that should match each of the decision table's input parameters.
  6. Click Add output parameter.
  7. From the menu after Fill, select the field that should be filled with one of the output parameters.
  8. From the menu after with, select the output parameter that will be used to fill the previously selected field.
  9. To fill another field with an output parameter, click the + button and repeat the previous two steps.

Lookups Using an Application Connection

Application connections can be used to retrieve information from an external application like a CRM, and fill fields with that information.

  1. In the form designer, navigate to the Lookup Rules tab.
  2. Click Add rule, or, if there are no lookup rules yet, New lookup rule.
  3. Under Select rule type, select Application Connection.
  4. In the next dropdown select the application connection to use.
  5. Under Please select an action, select the action to use for the rule.
  6. For the When condition, select the form field or variable that contains the item needed to complete the action.
  7. Click Add output parameter.
  8. In the drop-down menu after Fill, select the field that should be filled with the action's output.
  9. In the drop-down menu after with, select the output parameter that will fill the previously specified field.
  10. If you want more fields to be filled with the output parameter, click the + icon and repeat the previous step.

Lookups using a web request

Similar to the application connection lookup, web request rules that use an application connection can be used to retrieve information from an external application like a CRM, and fill fields with that information. However, the application connection type is limited to using predefined objects along with a limited number of custom objects with standard CRUD operations. A web request rule through an application connection can send any request, and can also be highly customized using a graphQL query.

  1. In the form designer, navigate to the Lookup Rules tab.
  2. Click Add rule, or, if there are no lookup rules yet, New lookup rule.
  3. Under Select rule type, select Web Request.
  4. In the next dropdown select the rule to use.

    Note: Only web request rules using an application connection will appear in the dropdown.

  5. For the When condition, select the form field or variable that contains the item needed to complete the action.
  6. Click Add output parameter.
  7. In the drop-down menu after Fill, select the field that should be filled with the action's output.
  8. In the drop-down menu after with, select the output parameter that will fill the previously specified field.
  9. If you want more fields to be filled with the output parameter, click the + icon and repeat the previous step.

Other Autofill Methods

Lookup rules are most appropriate for auto-filling fields when you want field data to match data from an external source of data. Providing default values is another way to help users fill out a form more quickly and accurately. You can provide default values by specifying them under Default value when you add a field to a form, or by putting parameters in the form's URL.

Versioning

Rules in process automation are versioned. The versioning of rules interacts with lookup rules as follows.

  • When the lookup rule is first created, it uses the latest version of the process automation rule by default. You can choose to use an earlier version of the process automation rule.
  • If a process automation rule in an existing lookup rule is updated, you can choose to update to the new version of the process automation rule, or remain with the originally selected version.
  • If you choose to use the latest version of a process automation rule in your lookup rule, then save your lookup rules and refresh the page, you will no longer have the option of switching back to an earlier version of the process automation rule.