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 and 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, create a new Connection for it and connect Start.RunNow → REST Request.
In the Connection you just created, set OpenApiUrl to https://zorkco.flowgear.net/openapi.yaml and Url to https://zorkco.flowgear.net, AuthType to Bearer and AccessToken to dMFROUiQadVEWaHZe8qTEHM9TieeyGo7PBhY9Ln1TwKCsGu-sfgnJUh4OKIrBZLplNtXKWdcJDdqgHjVScr24Q.
Save the Connection, 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, connect REST Request → QuickMap.
Connect REST Request.Response → QuickMap.Source.
Add Microsoft SQL Table Update, connect QuickMap → Microsoft SQL Table Update.
Connect QuickMap.Result → Microsoft SQL Table Update.TableXml.
Create a new Connection on Microsoft SQL Table Update, name it Database1. Use the same credentials given for 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 ID's 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 Variable Bar.Connections, 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 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 ID's 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 out the brace portions with the Connection ID's you obtained earlier.
```
[
	{
		"connectionId": "{database1connectionid}"
	},
	{
		"connectionId": "{database2connectionid}"
	}
]
```
Click Save and return to the Workflow by clicking Back (<), then Design.
Drag Microsoft SQL Table Update one block to the right, then add For Each 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 to this point in order to get sample data propagated.
Connect For Each.Item → Microsoft SQL Table Update.Connection, 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 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 ID's in to the Configuration instead of making a Workflow design change.

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