Select the Table Lookup data source option to retrieve data from an existing data table, company user table, data provider table or system user table.
When any of the components (Table Lookup, Math or Function) is selected, a pop-up window is displayed giving the possibility of performing one out of four actions:
Create New - to create a new record
Copy Existing - to create a new record from copy an existing one
Select Existing - to reuse a previously created record
Run Existing - to execute the source, and given an entered context - Assignment ID, Cost Estimate ID or Cost Estimate Line Item ID - retrieve the result
To create the then section with a table lookup component for the bonus data table, the user can select the Table Lookup option to configure the logic. First select Table Lookup as the option.
For this example, we will be creating one from scratch. Select Create New option from the Table Lookup selector. The Table Lookup Level modal appears. Select either Standard Tables or System User Tables by clicking on it.
After selecting the table level, enter a name for the table lookup function and select the Data Table from the drop-down list. If Standard Table was selected, the list shows data tables, company level user tables and data provider tables otherwise it shows System User Tables. Next select the Output Column from where the final output is to be found and retrieved.
By default, the Aggregate Function will show the Top Row. It is possible to select other aggregate functions by selecting a different value from the drop-down list. To retrieve the correct value, the Table Lookup function offers the ability to filter the data. Click
to create the filters. There is no limit to the number of filters that can be added. Click on
and add as many sorting criteria as desired. For this example, the first operand in the filter condition is based on the Output Column of the selected Data Table. Creating filter conditions is done in the same way to creating conditions at the business rule level.
We will need to add a descending sort by effective date. Add Effective Date and tick the Desc checkbox.
Click
or
to run and test the function or
to save the record. When Table Lookup functions are run, it is possible to specify how many rows of return which can be useful for troubleshooting. This is not possible when clicking
on the Maths and Functions modals.
Things to Note
When a table lookup component is saved, it will automatically appear on the Business Rules Components grid
Depending on the way you want to use your Table Lookups, you may design your business rules in a few ways. See How can Table Lookups be used within Business Rules
When creating filters on Table Lookups consider the size of the table being referenced. See How to ensure Business Rules are performing efficiently.