Branch: Path to Many solutions

Integrator.io
1

I found Plans to add One to Many support to Branching? from a few years ago which suggests a solution that I cannot seem to make work for my purposes, so I am going to revive this idea.

I worked with the team during Office Hours yesterday and came up with the first part of a solution for turning error messages into a data point:

function preSavePage(options) {
  var data = Array.isArray(options.data) ? options.data.slice() : 

var errors = Array.isArray(options.errors) ? options.errors : 

var remainingErrors =


// Mark all successful data records with exists: true
for (var j = 0; j < data.length; j++) {
    if (data[j] && typeof data[j] === 'object') {
      data[j].exists = 1
    }
  }

  for (var i = 0; i < errors.length; i++) {
    var err = errors[i] || {}
    var errStr = JSON.stringify(err)

    // Check if "record not available" appears anywhere in the error
    if (errStr.indexOf('record not available') !== -1) {
      var record = {}
      // If retryData exists for this error, carry forward the original record
      if (
        err.retryDataKey &&
        options.retryData &&
        options.retryData[err.retryDataKey] &&
        options.retryData[err.retryDataKey].data !== undefined
      ) {
        var original = options.retryData[err.retryDataKey].data
        if (original && typeof original === 'object') {
          record = Array.isArray(original) ? original.slice() : Object.assign({}, original)
          record.exists = false
        }
      }
      data.push(record)
      continue
    }
    remainingErrors.push(err)

  }

  return {
    data: data,
    errors: remainingErrors,
    abort: false,
    newErrorsAndRetryData: 

}
}

The above creates an “exists” data point and then I use results mapping to pull data.0.exists into a field called cre. I would then like to use cre to Branch
Branch 1: Continue on cre=1
Branch 2: Log to Error Log on anything else

But cre is added on a Path to Many step. My path:

  1. Find if records need updated per endpoint (up to 5 rows)
  2. Branch (1 row per branch based on existing in Step 1)
  3. Pull json for all records that need updated (in this case 25 sub-records) [Path to many]
  4. Check if Master record exists in destination for each sub-record [Path to many] (using above JS) and assign cre to each record
  5. Branch ← this is the PROBLEM [No Path to many]
    1. Master Record Exists
      1. Check if sub-record exists in destination [Path to many] (using above JS) and assign cre2 to each sub-record
      2. Use cre2 to determine Composite path (POST/PATCH) [Path to many]
      3. Log the update
    2. Master Record Does Not Exist
      1. Log to the error log

I can use the logic in a composite (as shown in 5.1.2 above) assuming both of my solutions should follow the same downstream path).

I know, as a workaround, I can create a new flow step that breaks my data into multiple records (rather than many:1). For this project alone, I would be required to add about a dozen new flows. While that might not seem like an issue to Celigo, I don’t want to run out of flows in my plan or be forced to upgrade because the best I can do is a workaround.

3

If you need to modify the data after result mapping, you can use a postResponseMap script because it gives you access to the entire page of records before proceeding further. I don't fully get the use case without seeing the data, but you could use that script to check if any sub-record is missing something and then mark the master as no good or all good. You could also use a branch where all records go down all branches, and then use one-to-many + input filters for bubble steps, where the input filter selects records where CRE exists (or doesn't), depending on your needs.

4

Thank you for the reply, but I am really confused by “bubble steps”? Googling Celigo + “bubble steps” doesn’t really get me any closer.

I have since changed this flow, but it will come up again (for me at least). Since I can’t “wait until a step is done for all records” (per a previous topic I created), Flow 1 populates several tables, Flow 2 populates a few more tables and this is Flow 3.
Flow 3 start with those modified records, where step 1 is basically

  select max(updated_at) as last_update, '1' as src from TABLE_1 where test_account = {{settings.integration.test_env}} AND update_needed = 1

union select max(updated_at) as last_update, '2' as src from TABLE_2 where test_account = {{settings.integration.test_env}} AND update_needed = 1
union select max(updated_at) as last_update, '3' as src from TABLE_3 where test_account = {{settings.integration.test_env}} AND update_needed = 1
union select max(updated_at) as last_update, '4' as src from TABLE_4 where test_account = {{settings.integration.test_env}} AND update_needed = 1
union select greatest(
(select max(updated_at)                               from TABLE_2 where test_account = {{settings.integration.test_env}} AND update_needed = 1),
(select max(updated_at)                               from TABLE_3 where test_account = {{settings.integration.test_env}} AND update_needed = 1),
(select max(updated_at)                               from TABLE_5 where test_account = {{settings.integration.test_env}} AND update_needed = 1)
) as last_update, '5' as src

So this creates 5 records, one for each source table.
I then branch (to all) along which ones actually need updates. For each src, I then pull all records that need to be updated into a field called data_json.
Next, I look at the destination for each sub-record [path-to-many] and attach cre=1 where exists and cre=0 where not.
Now, I want to use the cre value for EACH sub-record to branch into CREATE/UPDATE, but I cannot branch on path-to-many, so I’m stuck.

As noted earlier, I have since pivoted; I am now just pulling all records in Step 1, so my pathing is different. For this particular case, that was the right way to do it regardless, so I’m not intending to change anything about this flow, but if we can path-to-many on Lookups/Imports, it would stand to reason that we should be able to path-to-many on branch in order to get from lookup to import without having to start over.