Use Before Save and After Save operations to loop through a single origin record multiple times

From Starfish ETL
Revision as of 14:12, 30 October 2015 by Ahoekstra (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

There are many reasons why you might want to loop through a single origin row multiple times. In Act for cample, the contact record may contain an Account and one or more Contacts. Another example would be a Note that needs to be related to multiple records in your destination.

Your first step is to figure out what you a looping on. I generally try to loop on a comma separated list like: 0031a00000AkLxH,0031a00000AkLxg,0031a00000AkLxh. You would want to create global variables in the VBscript Global for your looping values. In your before operation, set your global values to your comma separated list and take off the first one. Process the record. In our example, use a stage to attach the Note to the target record. In your after operation, look at the looping values list to see if any values are still in it. If there are, go back to the first stage.

Example

The example below loops through the results of a SugarGetRelationships call (which returns a comma-seperated list) and processes a single stage multiple times for each entry. If splits the comma separated string into a string, references the individual values in the stage, then checks to see if there are more values to process by evaluating the length of the array.


In VBScript Global, declare two variables:

Dim relationships
Dim index

Create this script as a Before Operation, VBScript Procedure, ExecWhen=OnceEachRow

Sub VBScriptProcedure
    relationships = Split(SugarGetRelationships("Opportunities","@@ORG:id@@","Contacts"), ",")
    index = 0
End Sub

Use this Scripted Field function for the field where you need to reference the current index of the array:

Function ScriptedField
    ScriptedField=relationships(index)
End Function

Create this script as an After Operation, VBScript Procedure, ExecWhen=RepeatEachStage

Sub VBScriptProcedure
    If index<UBound(relationships) Then
        index = index + 1
        GotoStage "OppsContacts"
    End If
End Sub