< Back to Top

BP Tasks and Ruleset Actions used with Reference Tables

This article provides the following information.

Reference Table Lookup Action (Ruleset)

To retrieve records from the reference table, use LookupRefTable Ruleset action (available in the com.cleo.cic.util package) instead of building SQL objects. You must add com.cleo.cic.util project as a dependency to the integration project in order to utilize the action.

Note: This is only for retrieving records from a Reference Table; for other tables (ex. those external to CIC), always use SQL Access objects.  

Properties

Table name Stores name of the reference table (Eg. D365BC_Partners)
Columns Provide list of columns to be retrieved Eg. sell_to_customer_id,first_name,last_name
Multi record action Specifies action to be taken in case multiple records are returned by the query. Supported values: 
  • fail (default value if nothing is specified)
  • use_first
  • use_last
No record action Specifies action to be taken in case no records are returned by the query. Supported values:
  • return_null
  • fail (default value if nothing is specified)
Condition Key 1, Condition Value 1 Condition Key and Condition Value Pairs from 1 through 6 can be used as values in the SQL WHERE clause to retrieve value using ‘AND’ operator. Other operators are not supported at this time.
Outputs Output 0, Output 1 Output Fields 0 through 9 can be used to assign variables for the response values. 
eg. v_sell_to_id,v_first_name, v_last_name

(Sample Reference Table)

(Sample Lookup Ref Table Query)

 

Reference Table Modification Action (Ruleset)

To insert/update/delete records from the reference table, use the QueryRefTable Ruleset action (available in the com.cleo.cic.util package) instead of building SQL Access objects. You must add the com.cleo.cic.util project as a dependency to the integration project to utilize QueryRefTable.

Note: This is only for interacting with Ref Tables; for other tables (those external to CIC), always use SQL Access objects. 

QueryRefTable supports the following operations:

  1. Insert records into a Ref Table
  2. Update Ref Table records
  3. Delete records from Ref Table

QueryRefTable has the following properties:

Operation Supported operations are insert, delete and update
Table name Name of the table
Column name 1 to 6 Name of the columns for which data is to be inserted (ex., when data must be inserted for column edi_id in a table, pass edi_id here)
Column value 1 to 6 Value to be inserted for column names mentioned above (ex., for above example when pass the value for the column edi_id)
ConditionKey1 to ConditionKey6 Column names whose values have to be checked in the WHERE clause.
ConditionValues1 to ConditionValues6 Values to be compared against in the WHERE clause.
QueryRefTable action Returns the number of rows affected by the query.

Reference Table Lookup Action Local Testing (Ruleset)

Follow these steps to configure local testing for the reference table lookup task, which is a custom action executed within a ruleset. 

  1. Export the Reference Table(s): Export the reference table(s) from the cockpit that are referenced in the ruleset.

  1. Set Up the Workspace: In your workspace (the same directory as your util project),
    create a new folder named “RefTableExport.” Place the exported reference table files
    (.xlsx) into this folder.

  2. Run the Transformation Directly: Once the reference table files are in place, you can
    run the transformation directly from the ruleset. This will invoke the reference table
    lookup action within the ruleset, and the data retrieved by the lookup will be sourced
    from the local files you placed in the “RefTableExport” folder, rather than from the
    deployed version of the reference table. This enables you to test the lookup behaviour
    using local data.

    When the same action is deployed, however, the reference table will fetch data directly
    from the database instead of the local files.

Reference Table Lookup Task, Modification Task & Local Testing (Business Process)

Reference Tables are an essential component in many business processes, providing a structured way to store, retrieve, and manage data that supports process automation and decision-making. Two new Business Process tasks have been introduced to enhance the functionality and usability of Reference Tables. These new tasks streamline workflows, improve efficiency, and enable more dynamic data handling. They are:

  • LookUpRefTable
  • QueryRefTable

LookUpRefTable

This task performs select operations directly from a Business Process.

Parameter Type Description
Output List This is where the result will be stored, in form of a list.
Table name String Identifies the ref table.
Columns String Defines column name from which data is to be selected.
Multi record action String Select either use first or use last.
No record action String Select either to fail or return null.
Condition key 1-6 String These represent the WHERE clause of the query. These key and values are what we are matching against.
Condition value 1-6 String

Example

Suppose we have a reference table named D365BC_Items with the columns: erp_key, description, and gtin.

To retrieve the gtin for a customer with erp_key 2000-S:

  • Table name: D365BC_Items
  • Columns: gtin
  • Condition key 1: erp_key
  • Condition value 1: 2000-S
  • Multi record action: use_first
  • No record action: return_null
  • Output: gtinList

This would return the gtin associated with the customer with erp_key 2000-S into the list variable gtinList.

QueryRefTable

This task performs insert, update, and delete operations on reference tables. 

Parameter Type Description
Operation String Used to specify operation type: insert, update, or delete.
Table name String Identifies the ref table.
No record action  String Select either to fail or return null.
Column name 1 - 6 String Defines the column(s) that the operation will take place.
Column value 1 - 6 String
Condition key 1-6 String These represent the WHERE clause of the query. These key and values are what we are matching against.
Condition value 1-6 String

Examples:

To update a gtin value for the erp_key 2000-S in D365BC_Items table:

  • Operation: update
  • Table name: D365BC_Items
  • Column name 1: gtin
  • Column value 1: Test
  • Condition key 1: erp_key
  • Condition value 1: 2000-S
  • No record action: fail

This will update the gtin value to Test for the customer with erp_key 2000-S.

To insert a new record:

  • Operation: insert
  • Table name: D365BC_Items
  • Column name 1: erp_key
  • Column value 1: 3000-S
  • Column name 2: description
  • Column value 2: Demo item
  • Column name 3: gtin
  • Column value 3: 123
  • No record action: return null

To delete a record with erp_key 2000-S from the D365BC_Items table:

  • Operation: delete
  • Table name: D365BC_Items
  • Condition key 1: erp_key
  • Condition value 1: 2000-S
  • No record action: return null

This will remove the record where erp_key is 2000-S from the D365BC_Items reference table.

Recorded Demo

Boundary Review Demo - 06/20/2025

Related articles

Comments

0 comments

Please sign in to leave a comment.