Workiva Support Center

It's now time to bring everything together into one Centralized Reporting Workflow, where we will process new data and refresh the connections that rely on that data using a single Chain. In this Connected Learning Path, we'll use the variables from our control sheet to run workflows that prepare our data while updating the control sheet with a status, providing business users more visibility into when workflows most recently completed.

Step 1: Update Control Sheet to Include Logging

In order to notify end users of the status and most recent update of the processes run by the Control Sheet, we need a place to store the details on when the workflow ran and the status of that workflow. We will save this information in the control sheet.

• From the Workiva Platform, navigate to the CLP Control Sheet Spreadsheet
• Add two new columns after the FileLocation column with the following headers:
  • Status
  • TimeStamp
• Do not add any values below these columns headers

Step 2: Edit Existing Chain

• Navigate to Chain Builder and locate the Chain CLP | Using Values from a Control Sheet
• Click the pencil button to edit the Chain
• Click Chain Settings in the top right 
  • Rename the Chain: CLP | Running Processes and Logging Results within a Control Sheet
  • Under Chain Variables, click the Plus sign twice to create two Chain Variables and configure them as the below:
    
    • Type: Chain Variable (cv)
      
      • Name: cv-Status Column Letter
      • Value: the column letter that the Status header was created in
        • In the provided example, the value is F
    • Type: Chain Variable (cv)
      
      • Name: cv-Time Stamp Column Letter
      • Value: the column letter that the Time Stamp header was created in
        • In the provided example, the value is G
  • The chain should now have four Chain Variables
• Save the Chain

Step 3: Add Row Numbers Command

We will use the Add Row Numbers Command to automatically add row numbers to the control sheet. This will allow us to determine where the logging should occur within the control sheet.

• Double click the link between the Get Sheet Data Command and the Convert Control Sheet to JSON Command
  • Break the link by clicking the Delete button 
    
• Add an Add Row Numbers command from the Tabular Transformation Connector to the Chain canvas
• Connect the Get Sheet Data Command to the Add Row Numbers Command
• Connect the Add Row Numbers Command to the Convert Control Sheet to JSON Command
  
• Double click the Add Row Numbers Command to configure it
  • Click the Input File field
    • Within the Select a Variable panel, click the down arrow to expand Get Sheet Data
    • Select the Data output from the Get Sheet Data Command
• Save the Command

• Now that we have added Row Numbers to the dataset, we need to update subsequent Commands to use the transformed data. In this case, the CSV to JSON Command needs to be updated.
  • Double click the Convert Control Sheet to JSON Command to update it
    • Click the Input File field and delete the Get Sheet Data Output
      • Within the Select a Variable panel, click the down arrow to expand Add Row Numbers
      • Select the Add Row Numbers output from the Add Row Numbers Command
  • Save the Command

Step 4: Run Process to Upload Data to Wdata

We will now use the variables from our control sheet to run a process that uploads the data to a Wdata Table. We will do this using a Run Chain event that kicks off the Upload Data to Wdata Chain from the Uploading Data to a Table Path.

• Now that we've been able to visualize using variables from a control sheet using Handlebars, we no longer need the Handlebars command
  • Hover over the Handlebars Command and click the trashcan to delete
     
    • Confirm deleting the Command by clicking Delete
• Add a Run Chain Event to the Chain canvas
• Connect the Active Flag Conditional Command to the Run Chain Event
• Double click the Command to configure it
  • Name the Command: Run Chain - Upload Data to Wdata
  • Click the Chain dropdown and select the Chain CLP | Upload Data to a Table
  • Configure the Runtime Inputs using variables from the control sheet:
    • Click the Table ID field
      • From the Select a Variable panel, click the down arrow to expand Group Iterator 
      • Select <> JSON File Iteration
      • Click the green JSON File Iteration pill to transform the Variable
        • Click Select a Transformation and choose Get Value from JSON
        • Press the green + sign
        • In the value field type TableID exactly as it is within the control sheet and hit enter
        • Click Accept

• • • Click the File Name field
      • Follow the same steps above to use the variable from the control sheet, using the FileName field instead of TableID
    • Click the File Location field
      • Follow the same steps above to use the variable from the control sheet, using the FileLocation field instead of TableID
• Save the Command

Step 5: Run Process to Refresh Connections

Next, we will use the variables from our control sheet to run a process that refreshes the data in our reporting spreadsheets, making the newly uploaded data available to end users. We will do this using a Run Chain event that kicks off the Refresh List of Connections Chain from the Refresh List of Connections Path.

• Add a Run Chain Event to the Chain canvas
• Connect the Run Chain - Upload Data to Wdata Chain Event to the Run Chain Event
• Double click the Command to configure it
  • Name the Command: Run Chain - Refresh Connections
  • Click the Chain dropdown and select the Chain CLP | Refresh List of Connections
  • Configure the Runtime Inputs using variables from the control sheet:
    • Click the Spreadsheet ID field
      • From the Select a Variable panel, click the down arrow to expand Group Iterator 
      • Select <> JSON File Iteration
      • Click the green JSON File Iteration pill to transform the Variable
        • Click Select a Transformation and choose Get Value from JSON
        • Press the green + sign
        • In the value field type SpreadsheetID exactly as it is within the control sheet and hit enter
        • Click Accept

• Save the Command

Step 6: Populate Status and Time Stamp Command

We will now add the Commands that will be used to generate and populate the values that will be fed back to the control sheet to indicate the workflows were successful. The first step to doing that is to create the file with the status and time.

• Add a Create File Command from the File Utilities Connector to the Chain canvas
• Connect the Run Chain - Refresh Connections Chain Event to the Create File Command
• Double click the Command to configure it
  • Name the Command: Populate Status & Time Stamp
  • In the Text field, type in the the following information: 

Data Upload and Refresh Complete,(*<System.DateTime>*)

• • • Replace the (*System.DateTime*) placeholder with the appropriate Runtime Variable, this will programmatically pull the date and time from Chains
      • From the Select a Variable panel on the left, expand Runtime
      • Select the System.DateTime value
• Save the Command

Step 7: Write Back to the Control Sheet

We will now use the Write Sheet Data Command to write the information populated in the previous Command back to the control sheet. This will give our end users visibility into the process, showing the status of the data load and refresh, and the date when it was updated.

• Add a Write Sheet Data Command from the Workiva Connector to the Chain canvas 
• Link the Populate Status & Time Stamp Command to the Write Sheet Data Command
• Double click the Command to configure it
  • Click on the Spreadsheet ID field
    • Within the Select a Variable panel, click the down arrow to expand Chain
    • Select the cv-Control Spreadsheet ID Chain Variable 
  • Click on the Sheet ID/Name field
    • Within the Select a Variable panel, click the down arrow to expand Chain
    • Select the cv-Control Sheet Name Chain Variable 
  • Click on the Data File field
    • Within the Select a Variable panel, click the down arrow to expand Populate Status & Time Stamp
    • Select the Created File output
  • The Region field will be populated with a combination of Chain Variables and JSON output to tell the Chain the specific cell in the control sheet to write to for the current iteration:
    • Click on the Region Field and populate with the below: 
      
    • cv-Status Column Letter - variable found in the Variables panel under Chain Variables
    • JSON File Iteration - we are using the Row Number of the iteration, plus 1, to specify the region to write the data to, we add one to account for the header row
      • From the Select a Variable panel, click the down arrow to expand Group Iterator 
      • Select <> JSON File Iteration
      • Click the green JSON File Iteration pill to transform the Variable
        • Click Select a Transformation and choose Get Value from JSON
        • Press the green + sign
        • In the value field type Row Number and hit enter
        • Select another transformation and choose Parse Number
        • Click the green + sign
        • Select another transformation and choose Add
        • Click the green + sign
        • In the value field enter 1
        • Click Accept
          
    • Enter a colon after the first JSON File Iteration :
    • cv-Time Stamp Column Letter - variable found in the Variables panel under Chain Variables
    • JSON File Iteration - we are using the Row Number of the iteration, plus 1, to specify the region to write the data to, we add one to account for the header row 
      • Follow the same steps from above for the second JSON File Iteration

• Save the Command

Step 8: Test the Exercise

Now that the Chain is complete, test the result.

• Publish the Chain
• Click Execute and then select Run Chain
• Once the Chain has completed, verify that the workflow has completed by checking the below:

Chain successfully ran: 

Data successfully uploaded to CLP Employee Detail Table:

Connections successfully refreshed in CLP Employee Overview Spreadsheet:

Logging successfully added to Primary Control Sheet:

Congratulations! You have successfully automated a workflow that downloads data from the source, uploads the data to a Table, and refreshes the connections that use that data. This workflow utilized values from a control sheet to determine the data source, the upload destination in Workiva, and the connections to refresh. Apply these principles to your own processes to minimize manual effort and provide end users with completion insights.

Challenge yourself more by adding failure logging to your control sheet by completing the next path: Logging Failures within a Control Sheet!