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

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

There are many reasons why you might want to loop through a single origin row multiple times. In Act for example, 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

    • Related Articles

    • Use the same job to loop through multiple origins

      This example looks at reading from multiple mailboxes for email. Use a variable in the origin connection string. If the job needs to run for another mailbox, it calls the GotoJob function in the “Once After Conn” and pass in the Job's ID. Similar ...

    • Configuring the Origin and Destination

      Configuring the Origin The Origin is the data SOURCE that will be used in a job. You can choose from SQL, XML, OLE DB, ODBC, or SOAP. To get to the origin tab first select the project, then a job and finally select the origin tab. For MS SQL database ...

    • Executing Pre/Post-Process Operations

      Any number of functional operations can be performed before or after a job is processed. To set up the operations, first navigate to job within the project you wish to add the operations to. Once you have selected a job navigate to the mappings tab. ...

    • Exchange Connector

      Additional Documentation This Connector is based on the Microsoft Exchange ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/CEG/ado/ Overview The "Exchange (NEW)" connector ...

    • Using Row Hashing for Incremental Integrations

      Hashing The Whole Record Typically when setting up an ongoing integration, we only want to pull data from your origin which has changed since the last time Connect Creatio ran. This is accomplished using LastRunDate and applying it as a filter ...