< Back to Top

By category - Reference Tables

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.

Related Topic

Please see for https://support.cleo.com/hc/en-us/articles/1500002585901-Reference-Tables more information on Reference Tables.

Comments

0 comments

Please sign in to leave a comment.