Custom Logic Implementation

This article outlines the implementation of a Custom Logic flow that generates an entity subset for task execution. One or more Broadway flows can be designed to define the logic, which ultimately produces an output list of entity IDs. During task execution, this list is returned and used by the TDM process to extract or load data.

TDM supports two execution modes for running Custom Logic flows: Direct Call and Indirect Call. These modes define how the list of entities is retrieved and processed by a task’s batch process.

1. Direct Call Mode — in this mode, the batch process triggers the Custom Logic flow at runtime, which immediately returns the list of entities. The entities are streamed directly from the flow, without any intermediate storage or preloading.

   The Custom Logic flow must contain only one DbCommand with a single SELECT query that retrieves the entity list. Additionally, the associated Business Entity must include only one Root LU. For Business Entities with multiple Root LUs the Indirect Call mode should be used instead.

   The task processes entity IDs in data chunks as they are retrieved, without waiting for the full list to load. This streaming approach significantly improves performance when working with large datasets.

2. Indirect Call Mode — in this mode, the Custom Logic flow runs before the batch process begins, generating a list of entity IDs and storing them in a temporary table within the TDM database. This table is created specifically for the task execution, following the naming convention entity_list_<task exe_id>.

   Unlike Direct Call, the entire entity list is written in advance, and the task retrieves entities by querying this table during execution. Once processing is complete, the table is automatically removed.

   Indirect Call supports complex scenarios, such as Business Entities with multiple Root LUs that require a unified list of entities. It is also useful when the entity list needs to be reviewed, reused, or further processed before task execution.

Custom Logic Flow — Implementation Steps

1. Create the Custom Logic Broadway flow.
2. Add the flow to CustomLogicFlows Actor.

1. Create the Custom Logic Flow

The Custom Logic Broadway flow can be created in either the Shared Objects or a given LU.

The Custom Logic Broadway flow always includes two external input parameters, whose values are provided by the task execution process:

• LU_NAME
• NUM_OF_ENTITIES — the maximum number of entities to be processed by the task execution. The number is set in either the task or the task's overridden parameters.

TDM supports the creation of additional external parameters in the flow, enabling the user to send the values of these parameters in the TDM task. For example, you can add an external parameter named customer_status to the flow. The flow then selects customers for the task based on this input parameter. This way, you can filter customers by their status and still use the same flow to select them.

Notes:

• The input parameter name must not contain spaces or double quotes.

• TDM V8.0 introduced integration of Broadway editors into the TDM portal when populating either data generation or Custom logic parameters in the task tabs. This integration enables users to select valid values from a list, set dates, and configure distributed parameters.

  Click here for more information about TDM integration with Broadway editors and related implementation instructions.

• Sending multiple values in one parameter — you can define a String input parameter to provide a list of values, which the Broadway flow can split into an array using a specified delimiter (e.g.,"CA,NY"). The delimiter is configured in the split Actor within the flow.

• You can define a SELECT statement as an input argument. The SELECT statement can have binding parameters. The parameter values can be provided either through separate input parameters or directly within the SELECT statement. See the CustomLogicSql flow's examples below.

High-Level Structure of Custom Logic

Direct Call Flow

A Direct Call Custom Logic flow should have the following structure:

1. Init — calls the TDMSetSessionGlobals Actor to run the initial setting for the Custom Logic flow execution. The SESSION_GLOBALS input parameter must be defined as an external parameter named SESSION_GLOBALS.

2. DbCommand — defines the SELECT statement to select the task entities. The SELECT statement must return only the entity IDs.

3. customLogicDirectFlowUtil — filters out the reserved entities if needed, and formats the entity IDs for the task execution:

   • Set the NUMBER_OF_ENTITIES input parameter to be external.
   • Link the DBCommand result to the values input parameter.
   • Set the innerFlowClose input parameter to false in order to support the streaming of the resultSet by the inner flow and avoid the Broadway limitation of the maximum number of records (set to 100K by default).
   • The values output parameter must be external.

Indirect Call Flow

• Stage 1:

  • Add logic that requires entities, such as a DbCommand Actor running a SELECT statement on the CRM DB. The Actor needs to return the list of the selected entity IDs.
  • Initialize the entity counter for execution by adding the InitRecordCount TDM Actor (imported from the TDM library).
  • Notes:
  • If the flow needs to obtain an array of parameters, it is recommended to define the external input parameter as a String and to add a Split Actor to the flow in order to separate the values by a delimiter and populate them into a String array.
  • If filtering out reserved entities is not required when running this flow, it is recommended to add a limit to the SQL query. This way, the query would return only a limited number of records.

• Stages 2-4: Loop on the selected entities — set a Transaction within the loop to ensure a single commit for all iterations:

  1. Stage 2: Set the selected entity ID — returned by the Actor of Stage 1 — to a String using the ToString Actor.

  2. Stage 3: Call the CheckReserveAndLoadToEntityList TDM Broadway flow (imported from the TDM library):

  • Input — LU_NAME parameter. This is an external parameter, whose value is populated by the task execution process.

  • Output — recordLoaded. This is the entity number counter, which is loaded into the entity table.

  • This flow executes the following activities on each selected entity ID:

    • Checking whether the entity is reserved for another user in the task's target environment when running a load task without a sequence replacement, a delete task, or a reserve task. If the entity is reserved for another user, the task skips it as it is unavailable.
    • Loading the available entities into the entity table in the TDM DB and updating the entity number counter.

  1. Stage 4: Call the CheckAndStopLoop TDM Actor (imported from the TDM library). Set the NUM_OF_ENTITIES to be an external input parameter, whose value is populated by the task execution process. This Actor checks the number of entities inserted to the entity table, and stops the loop if the custom flow reaches the task's number of entities.

     Example:

     The task needs to retrieve 5 entities. The SELECT statement selects 20 entities. The first 2 selected entities are reserved for another user. The 3rd, 4th, 5th, 6th, and 7th entities are available and are populated in the entity table; the entity loop then stops.

Examples of a Custom Logic flow are shown below:

Example 1 — get the Contract Status as an input parameter and build the SELECT statement accordingly:

Example 2 — get an input String of states, separated by a comma. Split the input String into an array and send it to the SQL query:

An example of input for US states:

• NY,CA

Example 3 — get an input SELECT statement with parameters for the SELECT statement:

Note: When exposing the SQL statement as an external parameter for the user, ensure it runs on a read-only DB connection to prevent any DB updates.

Examples — CustomLogicSql Flow

A new generic Custom Logic flow, CustomLogicSql has been added to the TDM library. This flow executes a given SQL query on a specified DB interface. Edit the flow in order to use it in TDM tasks:

• Populate the interface input parameter in the Run Input SQL Actor, which is currently defined as an empty linked field.
• It is recommended to update the external name of the sql input parameter in the Run Input SQL Actor to a meaningful name (currently set to SQL). For example, SQL_query_on_CRM.
• Add the CustomLogicSql flow to the CustomLogicFlows Actor. Populate the new record as follows:
  • LU_NAME: optional. Can be left empty.
  • FLOW_NAME: CustomLogicSql
  • DESCRIPTION: populated with free text.
  • DIRECT_FLOW: true
  • Redeploy the Web Services to Fabric.
  • If the LU_NAME field is populated with an LU name, redeploy the LU name to Fabric. Else (if the LU_NAME field is empty), redeploy the TDM LU to Fabric.

The following parameters can be set by the task creator:

• sql — mandatory parameter that defines the SELECT query to run on the TDM DB and retrieve the task's entity list.
• sqlParams — optional parameter for setting values in the SELECT query. You can set multiple parameters, separated by a comma.

The CustomLogicSql flow runs in a direct call mode.

Examples of an input SELECT query:

1. Populate both parameters — sql and sqlParams:

   • sql:

     select distinct cust.customer_id from customer cust, activity act, cases cs where cust.customer_id = act.customer_id and act.activity_id = cs.activity_id and cs.status = ? and cs.case_type = ?

   • sqlParams:

     Open,Billing Issue

2. Populate only the sql parameter:

   • sql:

     Select Distinct act.customer_id From activity act, cases ca Where act.activity_id = ca.activity_id And ca.status <> 'Closed' And ca.case_type in ('Device Issue', 'Billing Issue');

Debugging the Custom Logic Flow

1. Run the createLuExternalEntityListTable TDM flow (imported from the TDM library) and populate the input taskExecutionId parameter to create the entity table in the TDM DB.
2. Populate the input parameters and run the customized flow.

2. Add the Flow to CustomLogicFlows Actor

Add the LU name and Custom Logic flow name to the CustomLogicFlows constTable TDM Actor (imported from the TDM library).

View the below example:

Check the DIRECT_FLOW checkbox to enable Direct Call of the Custom Logic flow.

Redeploy the Web Services.