Data structures walkthrough

This next walkthrough exercise is to teach you how to create and use data structures within a scenario. In this scenario, we’re going to pull a CSV file from our Fusion exercise files folder in Workfront, parse the CSV file, and then we’re going to aggregate and change some information. To do so, we’re going to create a data structure manually for user ID, date and time logged.

To be successful at this walkthrough exercise, you’ll of course want a new scenario created in your Fusion instance. You might also want to have a copy of the Fusion20 Jan time CSV file pulled up.

You’ll notice that there are seven columns of information and a hundred rows of log time in the original CSV.

After we convert and use a data structure, we’re going to change it into a different time entry per user, per day, and convert the minutes into hours.

Let’s start by creating a new scenario, and entitling it creating and using data structures. We’re going to use Workfront to download a data file, which we will parse to access each record. In the Workfront Fusion exercise files folder, Select Fusion2.0JanTime.csv and click on the document details.

Now, we’re going to copy the first ID number from the URL address.

There are two ID numbers here, but we just want the first one.

Back in our scenario, We’re going to start with the Workfront module. It was the first module, and we want Download Document. We’ve got our connection to Workfront already set up, refresh that if you need to.

The document ID will be the one that we just copied, and this will download the CSV file.

We’re going to rename this module get time file.

Now, we’ll add another module, CSV module and we’ll choose Parse CSV.

Using this module, we’ll pull information from the downloaded CSV file to be used and aggregated later in our scenario.

For the number of columns field, I can go into the CSV file and see that there are seven columns of information which I may aggregate and use later in my scenario. So I’ll put the number seven in this field.

I’ll leave CSV contains headers selected, since I do have headers in my CSV file I’ll set the delimiter type to comma and I’ll put Data from the download document module into the CSV field; that’s okay and I’m going to rename this module parse time file.

Now, we’ll save what we have and we’ll run once, so we can view the output.

If I open the execution inspector on the Parse ZSV, I can see that the information came in as a long string, but we parsed it into separate columns of information which we will aggregate and manipulate in the coming video.

Now that we’ve parsed the information in the CSV file, we want to aggregate the time logged per day, per user. To do that, I’m going to add another module and use the numeric aggregator tool app module.

For the source module, I’m going to choose our parse CSV module just before. For the aggregate function, I’ll choose sum, so we add up all the hours logged per day. For the value, I’m going to go to the pars time module and choose the last column, which is the minutes logged by each user. Since we want to group this information by the user that logged the time as well as the day that they logged it on, I’m going to choose show advanced settings at the bottom. For group by, I’m going to first select what we can consider the unique ID for each user or that email column four, and then I’m going to do a comma in group by a second grouping, or column five the date that the time was submitted. This is one way that you can expand your knowledge upon that group by field using a comma to add additional groupings. Let’s go ahead and select, OK, and let’s rename our numeric aggregator sum logged time by user/day.

Since I know we’re working with minutes at this point, we need to convert the minutes into hours. To do that, I’m going to use the set variable module.

For the variable name, I’ll just simply call it hours. I’ll leave the variable lifetime as one cycle, and for the variable value, I’m going to need to take the result from the previous module and convert it into hours and put it into the right type of format. To do that, I’m going to go to our math function tab in our mapping panel, choose format number and I can see that the first thing that we format is the value mapped over. Then we need to choose by how many decimal points do we want our resulting value to be and then our decimal and our thousand separators. So in the first field, I’m going to go back to my mapping panel of items and choose the result from our numeric aggregator and then I want to go back to my math function tab and choose the division operator and I’ll simply divide by 60. In the next space over, after the first semicolon, I’ll say I only want a round to two decimal places for the result from our format number function and then our decimal separator is going to be the period; our thousand separator will be the comma and then I can click anywhere in my panel to get rid of the mapping panel and OK, to save my work. Let’s rename this module, convert minutes to hours.

Now that we’ve pulled information from the original CSV, aggregated the time logged by user and day, and then changed the time logged from minutes into hours, it’s time to create a new CSV file. In this one, we want to restructure the information from the original CSV into something new. I’m going to use the create CSV advanced module option.

For our source module, In the create CSV module, I’m going to choose our numeric aggregator; that’s where all our information that we want to put into the CSV starts. Since we don’t have a data structure already established for the information we want to gather, we’re going to create a new one by clicking the button to the right.

All entitled this data structure time logged daily sum.

Since I don’t have a sample of data to generate automatically our data structure, I’m going to actually click the add item to create my own from scratch.

The first item in our data structure, we’ll call user ID.

We’ll set the typist text and then we won’t change anything for the default required or the multiline fields. Go ahead and click Add and then Add another item.

We’ll name this one date and then we’ll change the type to date, click Add.

Our last item that we’re going to add is the hours and we’ll change this to number data type and click Add.

Once we have our data structure established, we’ll click save and we’ll notice that in the mapping panel for the CSV, we now have the three fields available for our data structure. I know that the user ID and date information that I want to map over is coming from the numeric aggregator. If I click okay in this mapping panel right now, I can see by opening the execution inspector and scrolling down to the output that the user ID or the email is comma separated from the date value that’s right after.

To gather this information into separate fields, I’m going to use two functions: the get function and the split function.

At this point in the training, you may have discovered that if you know functions very well and how they should be written out and structured you can actually type them out like get with an open paren and then that function will automatically appear. Same thing with split in an open paren.

Per split, if I hover over that function, I know that I need to put an input with a semicolon in the separator after to split apart a string. In this case, I want to take the key, which is a string and then split it apart with a comma separator, which I’ve done right there. Then if I add a semicolon, which is part of the get function, I can say that within this string or split apart string of information, I want to get what value in that array. For this, I know that the user ID or email is the first value in the split apart stream so I can type in the number one and close my get function. For the date, I can do the exact same and change that final value to a two or the second item in the split array or list.

If I select my user ID field and click on CMD or CTRL A, it allows me to copy the entire mapped out function and paste it down below.

I can then change the number one to a number two to grab the date from that string.

Finally, for hours, this is the easy one. I just have to grab the hours from the set variable module, go ahead and click OK, and let’s rename this module create New Time file CSV.

The last thing we need to do to make this scenario complete is to upload our newly created CSV into a project in Workfront. To do this, we’ll need to open the desired project and copy the project ID from the URL.

Back in our scenario, we need to add another module.

This will be an upload document module.

We’ll put the project ID in the related record ID, the related record type, the project.

We’ll choose map for the source file.

Now the document name it’s giving us here is the same as the name of the document we originally downloaded. We’ll just use the same name, but we’ll put updated in front of it. And then for the file content, we’ll take the text output from our create new time file CSV.

We’ll click OK, and we’ll rename this module upload updated time file.

Then we’ll run once to run the scenario.

Now, we can check the output bubble in the upload document module to confirm that the document was uploaded and we can also see it in Workfront. -