Data stores walkthrough
In this exercise we’re using a data store to synchronize company names between a list of companies and Workfront.
This is one part of a one-directional synchronization of companies in Workfront and some other system. For now, it will only sync between a CSV file and Workfront. But it will also maintain a table in a data store that will keep track of the Workfront ID (WFID) and the company ID in the CSV file (CID) for each company. This will allow us to make this a bi-directional synchronization at some point in the future.
Data stores walkthrough
Workfront recommends watching the exercise walkthrough video before trying to recreate the exercise in your own environment.
Transcript
The purpose of this next walkthrough exercise is to introduce you to how you can create data stores to synchronize information between two systems. In this scenario, we’ll synchronize the companies listed within a CSV file and the companies found within your Workfront test drive, we’ll create three different paths for creating a company if it doesn’t exist within Workfront, updating the data store once that company has been created, and then synchronizing a record within the data store if it already exists within the CSV file and Workfront. To be successful, you want your fusion system pulled up and a new scenario created. In a separate tab, you may want to have your data stores section pulled up so that you can view the records created once you run your scenario.
You’ll also want to have your copy of the company’s list from the fusion exercise files folder.
And then, in your Workfront test drive, you can navigate to the setup area, and in your companies section you can refresh the page after you run your scenario to see the new companies created.
Let’s start by creating a new scenario. We want to pre-select Workfront, CSV and data store.
We’re going to name this using data stores to sync data.
We’re going to go to the documents area in Workfront, and in the fusion exercise files folder we’re going to be downloading company.
We’ll get the ID for companies here, from the url.
Now we’ll go back and continue our scenario. So we’re going to start with Workfront as the initial module, and download document. Document ID is the one we just copied. The connection, we’ve already set up previously.
And we’re going to rename this get companies file.
Now we’ll add another module.
We’ll choose the CSV app and our CSV for the number of columns. I’ll go to the companies file.
And there’s two columns of information that we’re interested in, an ID number within this file and the company name.
Okay, so two columns, our headers, comma’s in delimiter type, and we’re going to get data from the Workfront download module.
And we’ll rename this parse companies file.
So we’ll now click save and we’ll go ahead and run our scenario once, just so we can look at the output and confirm that what we’re getting is bundles with the column one as that ID and then column two as the company name.
Now it’s time for our first data storage module.
Grab another module.
This time we’ll choose data store. And from the available list of modules, we’re going to choose search records.
We’re choosing search records to see if there’s any record that matches what’s in the CSV file. Since we don’t have a data store already created for this scenario, we’re going to have to go through a two-stage process of creating the data store, and then creating the data structure to populate the columns in the data store. So we’ll start by clicking the add button to create a new data store, and we’ll call it company sync.
The next step is to create the data structure, or the columns of information which will make up our data store. I’ll click the add button here to create a new data structure. We’ll call this the same name, company sync, but we’re going to put, in parentheses, struc. This is for the data structure. Doing the struc in parentheses at the end, or some other type of flag, will help delineate this data structure from the list being associated with the data store.
We’ll create four fields for our data structure.
First one will be CID.
This is the company ID in the CSV file.
And we’ll create company name and then Workfront ID and then a created date.
Now for this one, we got to be sure and remember to choose the date as the as the type.
Let’s save on the data structure. And then for the data storage size we’re going to choose one megabyte, which will be more than enough for the number of companies that we’re going to sync.
We’ll click save.
And then we want to determine if the information on the companies in the CSV file has already been mapped into the data store. To do this, we’ll set up a filter where CID is equal to the ID of the company from our parse CSV module.
That’s right here.
The last step we want to take, and you want to make sure you do not miss this, is to click show advanced settings, right down here.
There’s an option to continue the execution of the route or module, even if the module returns with no results. In this case, we do want to continue on, even if the CID is not found within the data store, so make sure you click yes on this option and select okay.
We’ll rename this module to matching companies.
Now let’s do a similar action in Workfront and search for any company that already exists that might be contained within the CSV file.
So we’ll add another module in the Workfront app, and this will be search record.
For the record type, we’ll choose company.
And for the search criteria, we’re going to choose the name from the list.
Make sure it’s equal to, or see if it’s equal to, the company name from the CSV file.
If we find a match in a preexisting company name within Workfront, same as the company name within the CSV file, we’ll want the ID and the name of the company as output from this module.
We’ll select ID and name.
Click okay, and we’ll rename this matching companies as well.
Click to save the scenario.
In the following videos, we’ll create different paths based off of whether the company exists within Workfront, or within the data store.
To add our different paths based off of the information we find in the first four modules, I’ll click to the right of our Workfront search module and add a router.
For our first path on the top path, we’re going to create a company in Workfront if it doesn’t already exist but it is within the CSV file. I’ll click into this module in the Workfront app and select create record.
For the record type, I want to create a company.
And the only thing I need to map over is the name, which I can find using command or control G and typing name.
I’ll pull the name from the parse companies file module and select okay. We want to make sure that only new companies are created if they don’t already exist, so I’ll add a filter after the router.
For our label, I’ll say not in Workfront.
For our condition, I will go to our matching company’s search module and I’ll say the ID does not exist, then select okay. Let’s rename this module create company.
In the next video we’re going to create a second path, where once a company is created we’ll then update the data store with the company name and ID. To prepare for the second routing path, I know that I’ll need to transfer the company ID from the first path, up here, into the second, and I’ll do that with a set variable module.
For the variable name, I’ll just title it Workfront ID.
The variable lifetime will be one cycle, and the variable value is going to be the ID from our create company module.
I’ll rename this last module set Workfront ID.
In our first path, we’re creating a company if it doesn’t exist in Workfront already, and then we’re passing down the ID into our second path. Our second path should only be processed if the record doesn’t exist within the data store, so let’s start by setting up that filter first.
For this, we’ll say not in data store.
And as for the condition, we’re going to say that the key right here does not exist.
Similar to the path above, but for the Workfront ID. Go ahead and click okay, then let’s first grab the variable from our set variable in the path above using a get variable module.
Remember, the variable name has to exactly match what was in the path above. In this case, Workfront ID.
Click okay and rename this module get Workfront ID.
Now let’s update the record within the data store.
We’ll add another module, and within the data store application you’ll find add/replace a record. For this, we’ve already established the data store, which we call the company sync in the first video, so select that. And we’ll start by leaving the key at the top blank, since we’re not trying to replace an existing record, but create a new one. For the CID or the company ID from the CSV file, we’ll grab that from the parse companies file module.
For the company name, we’ll grab that from the CSV file as well.
For Workfront ID, we’re going to grab the value from our get Workfront ID module just before us in this path.
And finally, for the created date, we’re going to do a format date using the now function. To get that information, I’ll go to the date and time tab in our mapping panel and I’ll choose the format date function.
In the first base of format date I’ll choose now, and after the semicolon I’ll type in, in all caps, month, month, slash, a day, slash, year, so I can capture that information in a digestible format. Go ahead and click okay, and we’ll rename this create company entry.
In the next video, we’ll create a third path for where the company exists in the CSV file and the company exists within Workfront, but we need that synchronized record within the data store.
Let’s start by clicking the middle of the router to set up our third path, and then down in our toolbar we can click align to clean up our scenario. For our third path, we’re going to update the data store if a company exists within the CSV file and already exists within Workfront. We’ll start by setting up our filter first. For the label, we’ll say company exists comma not in data store.
For the condition, we’re going to say that the key in the data store for the search records module does not exist.
We’ll click the add and rule, and then we’ll say that the company name in the CSV file equals the name of the company found within Workfront.
If those names match, key does not exist for the record within the data store, we know we need to create that record to sync the information. For our next module, we can actually clone the create company entry module from the path above, and we’ll connect it to the rest and then get rid of this other module.
If we open our data store module, we can keep everything except for the Workfront ID value. That does not exist in this path. What we do want to grab is the ID from the matching company search right here. Once we do that, we can click okay and rename this create company entry as well.
Time to run this entire scenario to synchronize the companies in the CSV file and the companies that we need to create or update within Workfront and the data store. Let’s click run once and see what happens.
Looking at the execution inspectors alone, we can see that we’ve processed all 100 records. They passed through the beginning stage gate of trying to find matching records within the data store, or trying to find matching companies within Workfront. Because all of the companies within the CSV file are brand new, we could guess that they’d all come down this first path and would be created in Workfront. Then we pass the information down to the second path. And since these companies don’t yet exist within the data store, we’ve updated or created a new record there. Down below, in our third path, We know that we shouldn’t see anything passed through because there aren’t any matching companies within the CSV file that already exist as companies within Workfront, so no actions were taken in that last path.
If I come over to my other tab, I already have our company sync data store pulled up. And if I refresh the page, it’s going to show me a limited number in the beginning, but I’ll see that I now have all the companies being synchronized between the CSV file with the CID, the name, and the Workfront ID of the company created.
You can view more records by clicking on the additional pages that are available here until you can see all 100.
It’s also important to note that if you’re testing and you want to remove these records, you can select one and then click delete up here at the top. You can also select multiple ones and delete them all at once.
Going over to Workfront, we’ll refresh this page.
We started out with two default records, Agency X and Swains Incorporated, but now we have 102 companies available. -
Final note
Now that you’ve finished learning about data structures and data stores, you may be asking yourself, “When should you use them?”
Data structures are most commonly used for serializing or parsing data formats such as JSON, XML, CSV, and others. Data structures give you the ability to control the structure of your data and even validate data. The most common reason you use a data structure is to create valid data to send to an API that expects JSON or XML. In these cases, you’ll want to use either the JSON or XML app along with your data structure to make sure data is in the correct format.
Data stores should only be used to store persistent data that needs to be accessed by more than one scenario execution. For example, you may store metadata about the last record processed for advanced use cases requiring precise control over processing.
Data stores are not designed to be used as a data warehouse or logging. Data stores are not accessible outside of Workfront Fusion and most interactions with data stores are through a Workfront Fusion scenario. Consequently, it’s not possible to connect a data store to an analytics or reporting tool that would be expected for data warehouse and logging use cases. Workfront Fusion’s role in use cases like these would be to populate a system appropriate for organizing and storing data (e.g. SQL, MariaDB).
Want to learn more? We recommend the following:
Workfront Fusion documentation
recommendation-more-help
c9fbcf61-6d19-481e-a9ab-f54a0ae0ee8a