Workiva Support Center

Streamlining your data management even further can be done by combining steps into the same chain. In this Connected Learning Path we will walk through the best way to replace data in a Wdata Table, which is a two step process using one Chain.

Step 1: Run Chain

1. Navigate to the CLP | Upload Data to a Table Chain.
2. Click Execute and then select Run With Inputs.
3. Enter values for your Runtime Inputs:
   • Table ID - ID of the CLP Employee Detail Table
     Check out this document for help finding the ID.
   • File Name - any name you would like to call the file, for this example, we are calling the file "Employee Detail".
   • File Location - https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/employee_detail.csv.
4. Click Start.
5. Once the Chain has completed, click the Import File into Table node to verify success.
   There is now data available in the CLP Employee Detail Table, which we will now replace with the updated dataset, using a single chain to delete the old data and upload the new data.

Step 2: Create a Chain

1. Add a new Chain.
2. Name the Chain: CLP | Replace Data in a Table.
3. Save the Chain.

Step 3: Runtime Inputs

We use the Runtime Inputs Command to kick off the Chain. This allows us to specify the file name and the ID of the Table that the file will be imported to when we run the Chain.

1. Add a Runtime Inputs Command from Chain Trigger Events the Start area.
2. Edit the Runtime Inputs Chain Event by double clicking on the Command.
   Add the three Inputs below:
   • Input 1:
     • Type: TextField
     • Display Name: Table ID
     • Required: Checked
   • Input 2:
     • Type: TextField
     • Display Name: File Name
     • Required: Checked
   • Input 3:
     • Type: TextField
     • Display Name: File Location
     • Required: Checked
3. Save the Command.

Step 4: List Files

In order to delete the old file in the Table, we will need the File ID. To programmatically find the FileID, we will use the List Files Command. This Command uses the Table ID to search for all of the files within that Table and provides us with an output of the files as well as information about those files, including the File ID.

1. Add a List Files Command to the Chain canvas
2. Connect the Runtime Inputs Chain Event to the List Files Command.
3. Double click the Command to configure it. and click on the Table ID field.
4. Within the left Variables panel, click the down arrow to expand Trigger.
   1. Click the down arrow to expand Runtime Inputs.
   2. Select TableID.
5. Save the Command.

Step 5: Un-Import File from Table

Similar to uploading data to a Table, deleting data from a Table is a two-step process. The file will first have to be moved to the staged status before it can be deleted. To set the file as staged, it will need to be unimported. We will use the Un-Import File from Table Command to prepare the dataset to be deleted.

1. Add an Un-import File from Table Command to the Chain canvas.
2. Connect the List Files Command to the Un-import File from Table Command.
3. Double-click the Command to configure it.
   1. Click on the Table ID field.
      1. Within the left Variables panel, click the down arrow to expand Trigger.
      2. Click the down arrow to expand Runtime Inputs.
      3. Select TableID.
   2. Click on the File ID field.
      1. Within the left Variables panel, click the down arrow to expand List Files.
      2. Select Files List.
      3. Click on the List Files - Files List variable to transform the variable.
         1. Click Select Transformation and then select Get Value from JSON.
         2. Click the plus sign to add the variable transformation.
         3. In the Value field type '0' and then press Enter.
         4. After the =0 appears, type 'id' and press Enter again.
            The variable will now read the JSON output from the List Files command and find the id of the first file in the table.

            Tip! Pay special attention to upper case vs lower case when parsing JSON values.

         5. Click Accept.
            
   3. Save the Command.
      

    

   Step 6: Download File

   Next, we can either delete the staged file from the Table, or we can prepare the new file to uploaded. We recommend uploading a new file after un-importing the old file to ensure the process is successful before any data is officially deleted.

   To prepare the new file for upload, we will first have to download the file using the HTTP Request Get Command. We have hosted a file online for the Chain to download to demonstrating downloading files from the cloud.

   1. Add a Get Command from the HTTP Request connector to the Chain canvas.
   2. Connect the Un-import File from Table Command to the Get Command.
   3. Double click the Command to configure it.
      
      1. Click on the URL field.
         1. Within the left Select a Variable panel, click the down arrow to expand Trigger.
         2. Click the down arrow to expand Runtime Inputs.
         3. Select File Location.
      2. Update the Content type field to: application/csv.
   4. Save the Command.

   

   Step 7: Create File

   Just like we did in the Upload Data to Table Connected Learning Path, we will follow the two step process for uploading data to Wdata Tables. The file will first need to be created in the Staged status before it can be imported for final use. We will use the Create File command to create the file within the Wdata Table in the Staged status, indicating it has been created but not yet imported as a dataset. 

   1. Add a Create File Command from the Workiva Connector to the Chain canvas.
   2. Connect the Get Command to the Create File Command.
   3. Double click the Command to configure it.
      
      1. Click on the Table ID field.
         1. Within the left Select a Variable panel, click the down arrow to expand Trigger.
         2. Click the down arrow to expand Runtime Inputs.
         3. Select Table ID.
      2. Click on the File field.
         1. Within the left Select a Variable panel, click the down arrow to expand GET.
         2. Select Response.
      3. Click on the Name field.
         1. Within the left Select a Variable panel, click the down arrow to expand Trigger.
         2. Click the down arrow to expand Runtime Inputs.
         3. Select File Name.
         4. Within the left Select a Variable panel, click the down arrow to expand Runtime.
         5. Select Chain.ExecutionDateTime.
         6. Type ".csv" after your two variables.
            The Chain.ExecutionDateTime variable is used as a way to show when the file was generated and to ensure that each file has a unique name.

   

   Step 8: Import File to Table

   The next step in the process of uploading files to Wdata tables is to import the data, making it usable in Queries. This step takes the staged file and imports it into the designated Table as a dataset.

   1. Add an Import File into Table Command from the Workiva Connector to the Chain canvas.
   2. Connect the Create File Command to the Import File into Table Command.
   3. Double-click the Command to configure it.
      
      • For the Table ID field:
        1. Within the left Select a Variable panel, click the down arrow to expand Trigger.
        2. Click the down arrow to expand Runtime Inputs.
        3. Select Table ID.
      • For the File ID field.
        1. Within the left Select a Variable panel, click the down arrow to expand Create File.
        2. Select Result.
        3. Click on Create File - Result to transform it.
           1. In the Select Transformation dropdown, select Get Value from JSON.
           2. Click the + (plus) button.
           3. In the Value field, type "id" and press Enter.
           4. Click ACCEPT.
   4. Save the Command

   

   Step 9: Delete File

   Now that the new data has been uploaded and is ready for use and the old dataset has been staged, the final step is to officially delete the old data. This step uses the Delete File Command to delete the dataset from the Table.

   1. Add a Delete File Command from the Workiva Connector to the Chain canvas
   2. Connect the Import File into Table Command to the Delete File Command
   3. Double click the Command to configure it.
      To ensure we are deleting the same file that we un-imported, we will generate the file ID the same way as we did for the Un-import File from Table Command:
      • In the File ID field:
        1. Within the left Variables panel, click the down arrow to expand List Files.
        2. Select Files List.
        3. Click on the List Files - Files List variable to transform the variable.
           1. Click Select Transformation and then select Get Value from JSON.
           2. Click the plus sign (+) to add the variable transformation.
              1. In the Value field type '0' and then press Enter.
              2. After the =0 appears, type 'id' and press Enter again.
                 The variable will now read the JSON output from the List Files command and find the id of the first file in the table.

                 Tip! Pay special attention to upper case vs lower case when parsing JSON values.

              3. Click Accept
                 
              4. Save the Command.xx
                 

               

              Step 10: Test the Exercise

              Now that the Chain is complete, test the result.

              1. Click Execute and then select Run With Inputs.
              2. Add values for your Runtime Inputs.
                 • Table ID - ID of the CLP Employee Detail Table
                   Check out this document for help finding the ID
                 • File Name - any name you would like to call the file; for this example, we are calling the file "Employee Detail".
                 • File Location - https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/employee_detail_replace.csv
              3. Click Start
                 • The Chain should have successfully removed the old file and replaced it with the new file into your Table.
                 • You can verify the data was replaced in your table by navigating to the Table in Wdata and reviewing the Datasets in the right-hand panel.

              

              

              To learn more about using Chains to streamline data collection, check out the rest of the Data Centralization Paths!