12. Patterns: One-To-Many

There are often cases where you'll want to be able to pull data from a source and send it to multiple targets.

When the targets are completely different (i.e. they're different apps or services), you can use one Workflow to acquire the data and then iterate through a set of sub-Workflow calls to integrate it to all of the targets.

When the targets are the same app or service but require different endpoints or credentials, you can set up a Connection for each target and then dynamically iterate through each Connection from within a single Workflow.

This is what we refer to as a one-to-many pattern. While we'll be looking at a single source with multiple targets, the same principle applies to multiple sources with a single target, too.

Exercise 16: One-To-Many

In this exercise, we'll retrieve a set of contacts from a file and then insert them into multiple different tables in a Microsoft SQL database, simulating multiple different targets for the same data.

Add REST Request to a new Workflow, and create a new Connection. Connect Start.RunNow → REST Request.
In the Connection you just created, set OpenApiUrl to https://zorkco.flowgear.net/openapi.yaml, Url to https://zorkco.flowgear.net, AuthType to Bearer, and AccessToken to dMFROUiQadVEWaHZe8qTEHM9TieeyGo7PBhY9Ln1TwKCsGu-sfgnJUh4OKIrBZLplNtXKWdcJDdqgHjVScr24Q.
Save the Connection, and then click Refresh Metadata.
Back in the Workflow, load the Get Employees Node Sample on REST Request.

Run the Workflow to propagate sample data to the REST Request Node.
Add QuickMap, and connect REST Request → QuickMap.
Connect REST Request.Response → QuickMap.Source.
Add Microsoft SQL Table Update, and connect QuickMap → Microsoft SQL Table Update.
Connect QuickMap.Result → Microsoft SQL Table Update.TableXml.
Create a new Connection on Microsoft SQL Table Update, and rename it to Database1. Use the same credentials given in 09. Working with Databases.

Save and test your Connection before returning to the Workflow.
Back in the Workflow, load the Insert into Contacts Node Sample on Microsoft SQL Table Update.
Click Change/View on QuickMap.Mapping, and map data → Contacts and employee_name → last_name.

Run the Workflow to verify that the contact records are successfully inserted into the SQL database (the RowsAffected Property in the Workflow Logs will show the number of rows inserted).

We will now adapt the Workflow so that it can be used to update multiple targets.
Create a second Microsoft SQL Table Update Connection using the same details as the first but named Database2. This Connection will act as an example of a second target that needs to be updated with the same data.
Obtain the IDs of each of the Connections (Database1 and Database2) that you created by copying the Connection Key Property shown on the Connection.
Add a Variable Bar to the Workflow.
Add a Property on Variable Bar named Connections, and set its Data type to Json (where Text is shown) and its Property type to Configuration Variable (where Default is shown). Set Configuration Variable Name to onetomany.connections. The Property will show as green when you leave focus.
Save the Workflow and click Back (<).
Click ⚙️ Settings → Configuration Variables.

This area allows us to configure data that is independent of the Workflow design. The Properties shown here are the Properties declared as Configuration Variable in the Variable Bar Nodes in the Workflow.

We are going to create a document that lists the IDs of the Connections we need to cycle through for the one-to-many scenario.
Open the Connections Property and set its value to the document below, replacing the brace portions with the Connection IDs you obtained earlier.
```
[
    {
        "connectionId": "{database1connectionid}"
    },
    {
        "connectionId": "{database2connectionid}"
    }
]
```
Click Save. Return to the Workflow by clicking Back (<), and then Design.
Drag Microsoft SQL Table Update one block to the right, then add a For Each Node in the space that is created.
Reconnect the Execution Flows so that you have QuickMap → For Each and For Each.Item (Execution Socket) → Microsoft SQL Table Update.
Connect Variable Bar.Connections → For Each.SourceDocument.

Previously we've used For Each to iterate records. In this case, we're going to use it to iterate the Connections we've defined.

Run the Workflow up until this point, in order to get sample data propagated.
Connect For Each.Item → Microsoft SQL Table Update.Connection, and set the Data Mapping Expression to connectionId.

This technique enables us to assign a Connection based on its ID. Because that ID is coming from a document, we manage it as a Site Configuration.

Run the Workflow once more, and you should see the For Each Node firing its Item Execution Flow twice. When you look at the Connection Property of the Microsoft SQL Table Update Node in the Workflow Logs, you should see the first iteration shows it using the Database1 Connection, and the second iteration shows it using the Database2 Connection.

What's useful about this approach is that if we needed to add additional databases, we would only need to add the Connection IDs into the Configuration, instead of making a Workflow design change.

Save and run your Workflow, and then click Submit Exercise to grade it.