Update Datasets in a Table via a Chain

With this chain, you can update a table's datasets with files retrieved directly from your systems of record or from a shared drive. For example, use this chain to automatically:

• Replace a table's datasets with the latest versions.
• Append new files to a table's datasets.

To share this chain with multiple chains, such as to update tables with data from multiple solutions or locations, use this chain in a Run Chain event.

Ingredients

To create this chain, you'll use:

• The Runtime Inputs trigger event
• A Workiva connector
• A JSON connector
• A Tabular Transformation connector
• The Run Chain event

Step 1. Create the Chain

1. From Chains , click Build  device_hub, Chains, and Create Chain.
   
2. Under Setup, enter a unique name—such as Update Wdata table—and a description to help identify the chain and its intent.
3. To enable the chain to run in multiple Run Chain events at the same time, select Allow concurrent runs.
4. Click Save.

Step 2. Add the Runtime Inputs Trigger Event

To enable the chain to receive data from commands before the Run Chain event, start with a Runtime Inputs trigger event with variables for:

• The prefix to append to datasets' filenames to help identify the source
• The ID of the table to update
• The data to append or replace in the table

1. Click Trigger Events play_for_work, and move Runtime Inputs touch_app to Start.
2. Select the event's node, and click Edit create.
   
   
3. Under Basic Info, enter a unique name and description to help identify the event.
4. Under Variables, set up the inputs to receive through the event:
   • Click Add Input, select TextField, and enter a display name of File Prefix.
   • Click Add Input, select TextField, and enter a display name of Table ID.
   • Click Add Input, select FileField, and enter a display name of Data.
5. Click Save.

Step 3. Add the Command Group to Identify Datasets to Replace

To identify datasets that start with the same prefix as incoming files, add a serial command group to list the table's datasets as a CSV.

1. Click Command Group repeat, and move Command Group format_list_numbered to the canvas.
2. Drag a link from Start to the group's In.
3. Under Available BizApps, select Workiva, and move List Files to the canvas.
4. Drag a link from Group Start to List Files, and click Edit create for List Files.
   
5. Under Basic Info, enter a unique name and description to help identify the command.
6. Under Command Properties, for Table ID, select Table ID under Trigger, Runtime Inputs.
7. Click Save.
8. Under Available BizApps, select JSON, and move Array to CSV to the canvas.
9. Drag a link from List Files to Array to CSV, and click Edit create for Array to CSV.
   
10. Under Basic Info, enter a unique name and description to help identify the command.
11. Under Command Properties, for JSON Data, select Files List under List Files.
12. To enable a preview of the CSV output, select Preview Result.
    
13. Under Columns, add the columns of the CSV based on the JSON array:

    Column Name	JSONPath
    id	.id
    name	.name

14. Click Save.
15. Under Available BizApps, select Tabular Transformation, and move Filter Rows to the canvas.
16. Drag a link from Array to CSV to Filter Rows, and click Edit create for Filter Rows.
    
17. Under Basic Info, enter a unique name and description to help identify the command.
18. Under Command Properties, for Input File, select Converted File under Array to CSV.
19. In Delimiter, select Tab.
    
20. In Find Pattern, enter ().*.csv, click between the parentheses, and select File Prefix under Trigger, Runtime Inputs.
21. In Match Pattern Type, select Regular Expression.
22. Click Save.
23. Under Available BizApps, select Tabular Transformation, and move Extract Value to the canvas.
24. Drag a link from Filter Rows to Extract Value, and click Edit create for Extract Value.
    
25. Under Basic Info, enter a unique name and description to help identify the command.
26. For Input file, select Filter Row Output under Filter Rows.
27. In Column Index, enter 1, to extract the value from the first column.
    
28. In Delimiter, select Tab.
29. In Row Index, enter 2, to select the value from the first row after the headers.
30. To skip the command when the Filter Rows command finds no file with the prefix and returns only the header row, on the Skip skip_next tab, select Success, and add its rule:
    
    
    • Select a data type of Integer.
    • Select Record Count under Filter Rows.
    • Select an operation of =, and enter 1.
31. Click Save.

Step 4. Add an Un-import File from Table Command

To un-import datasets from the table when a new file has the same prefix, add an Un-import File from Table command:

1. Under Available BizApps, select Workiva, and move Un-import File from Table to the canvas.
2. Drag a link from the group's Out to Un-import File from Table, and click Edit create for Un-import File from Table.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. For Table ID, select Table ID under Trigger, Runtime Inputs.
5. For File ID, select Value under Extract Value.
6. To skip the command when the Filter Rows command has no file to un-import, on the Skip skip_next tab, add the same skip condition as Extract Value.
7. Click Save.

Step 5. Add a Command Group to Create and Import a New Dataset

To create and import a new dataset, create a serial command group:

1. Click Command Group repeat, and move Command Group format_list_numbered to the canvas.
2. Drag a link from Un-import File from Table to the group's In.
3. Under Available BizApps, select Workiva, and move Create File to the canvas.
4. Drag a link from Group Start to Create File, and click Edit create for Create File.
   
5. Under Basic Info, enter a unique name and description to help identify the command.
6. For Table ID, select Table ID under Trigger, Runtime Inputs.
7. For File, select Data under Trigger, Runtime Inputs.
8. To ensure a unique filename, for Name, select File Prefix under Trigger, Runtime Inputs; enter a hyphen -; select Chain.ExecutionDateTime under Runtime; and enter .csv.
9. To simplify the date value of the filename, click Chain.ExecutionDateTime in Name, and add a Parse Date/Time variable transformation:
   
   
   • Select ISO Extended (Platform Standard).
   • In Output date format, enter %m%d%Y%H%M.
10. Click Accept and Save.
11. Under Available BizApps, select Workiva, and move Import File Into Table to the canvas.
12. Drag a link from Create File to Import File into Table, and click Edit create for Import File into Table.
    
13. Under Basic Info, enter a unique name and description to help identify the command.
14. For Table ID, select Table ID under Trigger, Runtime Inputs.
15. For File ID, select Id from the Result output under Create File.
16. Click Save.
17. To enable debugging if the import fails, under Available BizApps, select Workiva, and move List File Errors to the canvas.
18. Drag a link from Import File into Table to List File Errors, and click Edit create for List File Errors.
    
19. Under Basic Info, enter a unique name and description to help identify the command.
20. For File ID, select Id from the File Import output under Import File Into Table.
21. Click Save.

Step 6. Add a Delete File Command

To delete a dataset successfully replaced by a new version, add a Delete File command:

1. Under Available BizApps, select Workiva, and move Delete File to the canvas.
2. Drag a link from the group's Out to Delete File, and click Edit create for Delete File.
   
3. Under Basic Info, enter a unique name and description to help identify the command.
4. For File ID, select Id from the File Import output under Un-import File from Table.
5. Click Save.

Step 7. Add a Command Group to Retain a Dataset as a Failsafe

To retain a dataset if its new version fails to import, add a parallel command group to return the un-imported dataset to the table and delete any files created but not imported:

1. Click Command Group repeat, and move Command Group format_list_numbered to the canvas.
2. Drag a link from the previous group's Out to the new group's In.
3. To run this group only when the import fails, double-click the link between the groups, and select Failure error. 
4. Under Available BizApps, select Workiva, and move Import File Into Table to the canvas.
5. Drag a link from Group Start to Import File Into Table, and click Edit create for Import File Into Table.
   
6. Under Basic Info, enter a unique name and description to help identify the command.
7. For Table ID, select Table ID under Trigger, Runtime Inputs.
8. For File ID, select Id from the File Import output under Un-import File from Table.
9. To skip the command when the Filter Rows command has no file, on the Skip skip_next tab, add the same skip condition as Extract Value.
10. Click Save.
11. Under Available BizApps, select Workiva, and move Delete File to the canvas.
12. Drag a link from Group Start to Delete File, and click Edit create for Delete File.
    
13. Under Basic Info, enter a unique name and description to help identify the command.
14. For File ID, select Id from the Result output under Create File.
15. Click Save and Publish.

Step 8. Use the Chain with a Run Chain Event

After you publish the chain, you can use the chain with a Run Chain event within another chain that retrieves the data from your system of record. In the parent chain:

1. Click Chain Settings, add a variable for the table ID, and click Save.
2. Click Events insert_invitation, move Run Chain device_hub to the canvas, drag a link from its preceding node, and click Edit create.
   
3. Under Basic Info, enter a unique name and description to help identify the event.
4. Under Chain to Run, select the chain created above.
5. Under Chain Runtime Inputs: 
   • For File Prefix, to enable the chain to match datasets to earlier versions, enter the value to append to filenames to identify matches.

     Tip: To append datasets rather than replace them, append the Chain.ExecutionDateTime runtime variable to the prefix to ensure a unique value each time the chain runs. To simplify the value, apply a Parse Date/Time variable transformation with a format of %m%Y.

   • For Table ID, select the table ID variable under Chain.
   • For Data, select the output file to update the table with from an earlier command.
6. Click Save.