Automate Workflow to Dynamically Select Data Source Based on Logic

Let's say you need to automate the data prep process to pick a different data source based on some logic, such as...

if Sales > N, or if the day in the month = 1, or if the record count is < 5.

Multiple data sources can be appended, and a dynamic mechanism can be used to manage record selection.

You can integrate the logic into a formula (in the Field Organiser block) to create a 'flag field' resulting in a boolean value or use a Join block to merge appended multiple sources with the Field Organiser block containing just the Source name.

Example

We have three data sources and wish to use one source as workflow input between the first day of the month and the 5th, another source between the 6th and the 16th, and the third source for the rest of the month.

Firstly, we will append all the data sources using the Append block. This will create a single data source with all data inside a single container. The option to create a [Source] field will label every record and clearly indicate which source it's coming from.

This value can be referenced in our dynamic calculation to create logic where different records are chosen based on some rule.

Method 1

We will connect a Field Organiser block to the appended data and add a 'flag field' with a formula:

IF([Source]=

(IF

(

DATEUNIT(TODAY, "day")>15,"Source1",

DATEUNIT(TODAY, "day")>5,"Source2",

"Source3")),

"IN",

null)

Depending on the current day of the month, the formula will assign "IN" to records from either Source1, Source2, or Source3.

After this, we simply add a Record Filter to filter in those "IN" records.

Method 2

The formula (see below) is added in the Field Organiser block to produce the target source name based on logic. This is followed by a join with the appended data to facilitate choice, matching the values in the [Source] field with this single calculated value.

IF(

DATEUNIT(TODAY, "day")>15,"Source1",

DATEUNIT(TODAY, "day")>5,"Source2",

"Source3")

This formula will produce a single record with the name of the data source.

Note: The Field Organiser block always requires a source, so the Text Input block is included for that specific purpose.

The demo ioz file is attached - it can be downloaded and imported for local exploration.