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
- From Chains , click Build
- Under Setup, enter a unique name—such as Update Wdata table—and a description to help identify the chain and its intent.
- To enable the chain to run in multiple Run Chain events at the same time, select Allow concurrent runs.
- 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
- Click Trigger Events , and move Runtime Inputs to Start.
- Select the event's node, and click Edit
- Under Basic Info, enter a unique name and description to help identify the event.
- 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.
- 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.
- Click Command Group , and move Command Group to the canvas.
- Drag a link from Start to the group's In.
- Under Available BizApps, select Workiva, and move List Files to the canvas.
- Drag a link from Group Start to List Files, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- Under Command Properties, for Table ID, select Table ID under Trigger, Runtime Inputs.
- Click Save.
- Under Available BizApps, select JSON, and move Array to CSV to the canvas.
- Drag a link from List Files to Array to CSV, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- Under Command Properties, for JSON Data, select Files List under List Files.
- To enable a preview of the CSV output, select Preview Result.
- Under Columns, add the columns of the CSV based on the JSON array:
Column Name JSONPath id .id
name .name
- Click Save.
- Under Available BizApps, select Tabular Transformation, and move Filter Rows to the canvas.
- Drag a link from Array to CSV to Filter Rows, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- Under Command Properties, for Input File, select Converted File under Array to CSV.
- In Delimiter, select Tab.
- In Find Pattern, enter
().*.csv
, click between the parentheses, and select File Prefix under Trigger, Runtime Inputs. - In Match Pattern Type, select Regular Expression.
- Click Save.
- Under Available BizApps, select Tabular Transformation, and move Extract Value to the canvas.
- Drag a link from Filter Rows to Extract Value, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For Input file, select Filter Row Output under Filter Rows.
- In Column Index, enter 1, to extract the value from the first column.
- In Delimiter, select Tab.
- In Row Index, enter 2, to select the value from the first row after the headers.
- To skip the command when the Filter Rows command finds no file with the prefix and returns only the header row, on the Skip
- Select a data type of Integer.
- Select Record Count under Filter Rows.
- Select an operation of =, and enter 1.
tab, select Success, and add its rule: - 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:
- Under Available BizApps, select Workiva, and move Un-import File from Table to the canvas.
- Drag a link from the group's Out to Un-import File from Table, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For Table ID, select Table ID under Trigger, Runtime Inputs.
- For File ID, select Value under Extract Value.
- To skip the command when the Filter Rows command has no file to un-import, on the Skip tab, add the same skip condition as Extract Value.
- 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:
- Click Command Group , and move Command Group to the canvas.
- Drag a link from Un-import File from Table to the group's In.
- Under Available BizApps, select Workiva, and move Create File to the canvas.
- Drag a link from Group Start to Create File, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For Table ID, select Table ID under Trigger, Runtime Inputs.
- For File, select Data under Trigger, Runtime Inputs.
- 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
. - 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
.
- Click Accept and Save.
- Under Available BizApps, select Workiva, and move Import File Into Table to the canvas.
- Drag a link from Create File to Import File into Table, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For Table ID, select Table ID under Trigger, Runtime Inputs.
- For File ID, select Id from the Result output under Create File.
- Click Save.
- To enable debugging if the import fails, under Available BizApps, select Workiva, and move List File Errors to the canvas.
- Drag a link from Import File into Table to List File Errors, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For File ID, select Id from the File Import output under Import File Into Table.
- Click Save.
Step 6. Add a Delete File Command
To delete a dataset successfully replaced by a new version, add a Delete File command:
- Under Available BizApps, select Workiva, and move Delete File to the canvas.
- Drag a link from the group's Out to Delete File, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For File ID, select Id from the File Import output under Un-import File from Table.
- 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:
- Click Command Group , and move Command Group to the canvas.
- Drag a link from the previous group's Out to the new group's In.
- To run this group only when the import fails, double-click the link between the groups, and select Failure .
- Under Available BizApps, select Workiva, and move Import File Into Table to the canvas.
- Drag a link from Group Start to Import File Into Table, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For Table ID, select Table ID under Trigger, Runtime Inputs.
- For File ID, select Id from the File Import output under Un-import File from Table.
- To skip the command when the Filter Rows command has no file, on the Skip tab, add the same skip condition as Extract Value.
- Click Save.
- Under Available BizApps, select Workiva, and move Delete File to the canvas.
- Drag a link from Group Start to Delete File, and click Edit
- Under Basic Info, enter a unique name and description to help identify the command.
- For File ID, select Id from the Result output under Create File.
- 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:
- Click Chain Settings, add a variable for the table ID, and click Save.
- Click Events
- Under Basic Info, enter a unique name and description to help identify the event.
- Under Chain to Run, select the chain created above.
- 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.
- For File Prefix, to enable the chain to match datasets to earlier versions, enter the value to append to filenames to identify matches.
- Click Save.