Get and transform data

Key Concepts

• Ways to get data from a Table
• Ways to transform data from a Table
• Ways to get summary and other meta information about a Table

Autogenerated Transcript

Welcome back to Catalytic enter.

last video, we went over the basics of data tables and Catalytic and how you can create them manually or in a Workflow.

In this video, we’ll talk more in depth about data tables and some of the actions that you can use to interact with them in a Workflow.

Before we create a Workflow, I’m just going to upload an Excel spreadsheet to create a data table.

So we can use Workflow actions to do a lookup in update a row.

This spreadsheet was made using a free online random data generator to make a list of email mailing addresses and start dates with my own information added to the end, which will make a Workflow to update.

In order to create this table, I’m gonna navigate over to the data tab, and I’m going to go ahead and create a new table.

This time, we’ll be creating one using a spreadsheet.

So I’m just going to go ahead and upload that here.

You’ll notice that the table adopts the name of the file that we’re uploading, but you can change it in case you’d rather be named something else.

I’m just gonna go ahead and create our table now.

Before we go create a Workflow.

Let’s go over a few more things about tables.

If an uploaded table doesn’t have the credit column field type, you can click on the ellipses in that column and edit its type, which is what I’ll do for the start date column.

Just note that even though you can change the column name as well, you can’t change the field reference name.

But I’m just going to go ahead and change this to a date.

The ellipses in the top right allow you to access the rest of the table settings such as importing and exporting data and editing permissions.

Before we exit, I’m going to add one more column to the table date employed.

Since we’ll be calculating this number by getting the difference between today’s date and the start date in the table.

So let me go ahead and add that column.

It will be days employed.

And I’m going to make sure that that type is an integer or a whole number.

We’ll go ahead and save this.

Now that we’ve imported the spreadsheet as a Catalytic data table, let’s navigate to the Workflows tab and create a new Workflow from scratch.

I’m just going to call it lookup data, calculate days employed and update a row.

Since those are the actions that we’ll be using.

I’m going to keep the description and everything else blank or as is but feel free to fill those out if you’d like to.

And I’m just going to go ahead and create that Workflow.

So similar to last time, I’m not going to add a trigger to this Workflow.

Instead, I’m just going to add form fields for email and mailing address.

So I’ll quickly do that.

There’s our field for email.

And then this will be the field for mailing address.

Using the email address as our primary key, we’re going to use the Lookup step to grab the start date and feed it into a step to calculate the day since that employees started.

Again, this information is going to be coming from that data table that we already uploaded.

The step we’ll be using to actually change a record in our table will be update a vote, but we’ll add that later.

Let’s go ahead and find the lookup DATA step.

I’m just going to describe the process lookup data in a table.

And Catalytic found the correct action.

So what this step does is it allows you to pass in a search term and return the values from columns in the record that match your search term, kind of like VLOOKUP in Excel.

Now let’s configure the lookup step.

Because we’ve imported our spreadsheet as a data table in the Data tab.

Rather than creating it in a Workflow like the last video, we could just select all the values we need by name instead of typing them in manually.

So let’s go ahead and do that.

I’m just gonna go ahead and search for the mailing addresses table that we uploaded.

And again, the column we are looking up our value by will be email, the search term is going to be that email were submitted in our form field.

So you can either do the double handlebar reference or go ahead and copy and paste from the global fields on the right side.

The column that we’re going to be returning is going to be the start date, because again, that is going to be the field that is then fed into another step so we can calculate the number of days you’ve been employed.

We’re going to keep the advanced search type as exact and everything else as is.

So let’s just go ahead and save that step now.

And now before adding our next step, let’s go ahead and collapse this global fields and I’m going to expand the look up data in a table.

So we can go ahead and see what the different outputs of that step look like.

You’ll notice that the output will change depending on what columns you’ve told it to return.

So you’re able to select individual column values to then use later on in your Workflow.

One thing to keep in mind is that if there are more To pull matches, this step will only return the first match.

If you wanted all matches returned, then you would want to use the Apply Filter step.

Let’s go ahead and test our Workflow to see what the output looks like.

But instead of using the test button at the top, I’m just going to test this individual step.

If I click on the ellipsis, to the right of this step, then we’ll have this drop down appear.

And I’m just going to go ahead and select Test step.

This will open a pop up similar to our testing screen where we have to provide the inputs we’ve defined on this step, then we can see what the output looks like without running the entire Workflow.

This can save time in case you have a more complex process and want to just test a single action.

So I’m just going to go ahead and input my email and we’ll start this test so we can see what the output looks like.

And again, we wanted it to return the start date, which is exactly what it did.

Cool.

So again, this step will be feeding that start date into the next step in our process, which will calculate the difference between two dates.

So let’s go ahead and add that step.

I’m just going to describe the process, calculate difference between dates.

And Catalytic grabbed the correct action, so I’ll just go ahead and start configuring it.

The start date that we’re going to pass in will be the output from our lookup step, and the end date is going to be today’s date.

So I’m just going to go ahead and copy the output from our lookup data in a table, step that start date and paste it into our start date field.

Now in order to get today’s date, I’m going to copy from the global fields run start date.

This is a default field with every Workflow, which has a value that is exactly as it sounds, the date that the Workflows started.

So let’s go ahead and copy that and put it at the end date.

I’m just going to keep the units as a day instead of business days.

And I’ll just go ahead and define that return field name or what the output will be.

And I’ll just call that date difference.

Keep it something simple.

So now let’s just go ahead and save our step.

So let’s go ahead and add the next step, which will be used to update our table with the new mailing address and the number of days employed.

So I’ll go ahead and describe that process, update a row in table.

And we grab the correct steps.

So let’s go ahead and configure it.

Again, we’re going to reference everything by name.

Because we’ve already created this table in our data tab.

The first couple of fields are similar to the lookup DATA step, so let’s fill them out.

So we’re looking for a matching vote based off of the email we submit.

Again, that table will be that mailing address table, we are going to be looking up our column by email.

And the value we’re looking up by will be the email address that we are submitting from our form field.

Now we’re going to define what columns we’re updating from a matching row and what values we’re updating it with.

So let’s choose the columns, we’ll be updating the mailing address and the days employed.

One thing I forgot to mention in the last video, if the data you’re working with has special characters, such as commas, or newline characters, then to insert that entire value, you’re going to want to put parentheses around the field reference.

In this example, if we don’t do this, then the address will only be added up to the first comma, since that is the delimiter.

But with the parentheses, the entire value will be inputted commas in all the information bubble will give you this hint as well.

So let’s put that knowledge to use and put parentheses around the mailing address value that we’ll be adding.

And we won’t need them for the days employee field.

But in order to grab that value, I’m just going to expand the Calculate difference between dates, copy this value here.

And we’ll go ahead and paste it into our values field.

And lastly, the update all field will let you decide whether or not you want all matching rows or just the first match found to be updated.

So I’m just going to keep this as is and that will complete the configuration for this step.

So now just go ahead and save that.

Now that we built this Workflow, let’s go ahead and run it from our process page.

And I’ll explain why we’re doing that instead of using the test feature in a minute.

So using this breadcrumb in the top left, let’s just navigate back to our Workflow.

And I’ll click on Start an instance.

I’m going to input my email address and then the new address that I want updated so I’ll just go ahead and put in one of my neighbors addresses and we’ll go ahead and start this process.

So this Workflow finished fairly quickly, but I’m just gonna Go ahead and open up the update a row step.

And we could see that the row was successfully updated.

So now let’s just navigate over to our table and refresh it.

And then we could see that the values were updated.

So scrolling to the bottom, where my information is, we could see that the mailing address was updated with what I submitted.

And then the days employed was calculated inserted into the table as well.

And that completes this process.

But before we end the video, there’s a few more tables, I’d like to talk about the first being an application table, and the second is called a master data table.

I’m gonna navigate back to the data tab, so I can show you what an application table is.

So application tables are default tables that come out of the box with Catalytic in store configuration information that you can access, such as all users or triggers that exist within your instance.

So I’ll go ahead and click into the application table users.

These tables are locked, so you won’t be able to edit the values directly.

These types of tables can store some pretty valuable information.

And we’ll actually be using this application table users in our next video in order to create a batch process.

And now let’s talk about master data tables.

You could find these from the Data tab.

But because they’re associated with a Workflow, it’s easier just to go to that process page and click on this data tab here will have access to the master data table that was created.

The master data table is locked in a similar way to the application table, so you won’t be able to directly edit any of the values in this table.

One thing to note is that test instances are also not stored in the master data table, which is why we ran our instance from the process page.

So that way, it would actually create an instance and all the information about that Workflow is then recorded in this master data table.

We can see all of the information about our instance, including all of the information that was submitted and the value of every actions output fields.

These tables as well as application tables give Catalytic users access to deeper information for analytics.

They can be exported for reporting as well or used in a Workflow to for example, send every active user in your instance of webform to update their mailing address.

This is the batch process that we’ll be building in our next video which will go over how to create a batch process with a data table.

Thanks for tuning in.