In this chapter, you'll find out more about variables, placeholders for varying or changeable data which let developers write flexible tasks, and datasets, a grouping of related pieces of data. Learn how to find, create, and use variables, datasets, and the Expression Builder. Brigette Matz, Automate Trainer/Consultant, gives you a guided tutorial on using each of these features in Automate. In this video you’ll learn:
- How to create and use variables
- An overview of the Expression Builder that incorporates all the elements required to create a properly formatted expression
- How to create and populate a dataset
Watch this chapter now to learn how to get started.
Brigette: A variable is a place holder for a vary or changeable data which enables developers to write flexible tasks. Rather than entering the data directly into a step, a developer can create a variable and use that to represent the data. When the task runs, the variables are replaced with real data. This makes it possible for a task to perform actions on values [00:00:30] that may or may not be known until run time.
It also allows a single task the ability to hold different sets of data. We'll demonstrate the use of a variable by creating a basic task of writing data into an Excel spreadsheet and saving it with a unique file name. First, there are a few different ways to create a variable from the Automate Task Builder. Here under the my action side panel you can see to create variable action which can be dragged over to the task builder template.
This [00:01:00] variable will reference the value of the file path and name for our Excel spreadsheet. I'll actually go ahead and pull up this folder here and right click that folder path, copy that as text, we'll paste that value into our variable and then we'll also add the file name on here. Notice that we have not included the file extension and we'll be doing that later on in this task.
Another way to create a variable is by selecting this add variable button at the top ribbon [00:01:30] of the task builder. It's important to mention that a variable name must contain only alpha numeric characters, must start with a letter and cannot contain spaces. Automate users should make a practice of using specific naming conventions for variables and other data containers such as data sets under raise.
For this task, all of our variables will start with the VAR_character sequence as a naming convention. This particular variable will hold the data that we will write to our [00:02:00] Excel spreadsheet. We will write a list of all the Automate products each within their own cell in that spreadsheet. And as the variables are created, we see them populated under the variables tab over our debug panels on the bottom of the window.
The initial value is also indicated on this view for each variable and you can see here there is a dropdown arrow to expand all of the values related to this first variable. Another way to create [00:02:30] a variable is by entering the keyword variable into our action search bar, down on the left hand corner, and dragging over the create action. We'll configure this third variable for today's date, which will append to the end of our file name before saving.
We'll name this variable but we'll leave the value field blank. Rather than hard coding the data into this variable, we can set it with a more fluid value by formatting a date and time step and assigning that value to the variable. [00:03:00] To accomplish this, I'll type in the keyword date in our action search bar and locate the format date time action. This date time format will change based on the computers current date and time when the task runs.
And the variable will capture the new date and time whenever it is referenced within the task. Here we can see a writeable field with the current date and time format displayed. If we make changes to this format, we can see up here how it will be displayed when Automate calls his value. Since we're going to append today's date to the end of a file name, [00:03:30] we want to make sure it is formatted with acceptable characters to our computer.
I'll replace the forward slashes with underscores. As we know computers do not like to have slashes saved as part of the file names. Then we'll populate our VAR date variable with the newly formatted date and time result. Note that on the variables pin at the bottom of our page, the initial value for our VAR date variable is blank. [00:04:00] We need to run this task in order to declare the value that will reside in that variable so we can use it throughout the task.
After the task is done running, we'll see down here that the initial value is still blank, but the current value has been populated with today's date. Variables can also be populated by using the set variable action, which can be found within the variable actions panel here under [00:04:30] the word variable and by dragging out the set action. This action adds or changes the contents of an already existing variable as needed throughout the task. However, in this task we're not going to need a reset any variables.
Now that we've identified our variables, let's finish building out our task so we can see them work in action. We're going to use our Excel action set to write out the product list information into a spreadsheet. [00:05:00] I'll drag our set cells action and we'll configure it from here. First I'm going to move this window and resize it just a bit so we can still see the variables that we have to clear at the top of the task in case we need to reference them.
In order to write the data, I need to create the file since it doesn't yet exist. Here in this field is where we'll input our folder path and unique file name by referencing the variables we've created above. In order to add our variables, we'll toggle our Expression Builder [00:05:30] and let's talk a little bit more about the Expression Builder before moving on here. Here we have our Expression Builder.
The Expression Builder incorporates all of the elements required to create a properly formatted expression for use in any step of an Automate task. An expression is a combination of variables, constants, functions and operators used to represent a particular value. The Expression Builder is comprised of folders containing all available functions, datasets, variables [00:06:00] and so on.
From the Expression Builder you can call on both basic VBA compatible functions such as the ones listed here or you can call an extended function which are specific to the Automate scripting engine, which extends beyond the capabilities that the VBA compatible engine provides. As you can see, you can reference values more specific to the computer and Automate service which may be helpful for logging tasks, data and air handling.
[00:06:30] Here we have the local variables that we created for this task. We're going to use the VAR file name variable, double click that and click insert to put that into our field. Notice that the variable is enclosed by a set of percent signs, one on each end. All variables referenced within a task must be fully enclosed with the percent signs so that Automate knows to utilize the value that's held by that variable.
After [00:07:00] we have our file path noted, we want to add our date variable here to finish naming the file as well. I'm going to go over to the right hand corner of this input field and click the percent sign to open our Expression Builder, double click VAR date and click the insert button. Again, you'll see that each variable is enclosed with its own set of percent signs. The last piece here is to add that file extension.
Moving down we're going to select our [00:07:30] data source and the data source is going to be a list. We'll reference that list that we assigned to our variable VAR list up here in the beginning of the task. Well write the data starting in the first row and column of our spreadsheet. And to insert our list value, we'll again open the Expression Builder, select VAR list and insert that variable. Moving down the delimiter here that we'll use is a new line.
If you recall when we wrote out that list on our variable, we separated [00:08:00] every product name with a new line. We'll make sure to insert that list vertically on our spreadsheet as well. Let's run this task. Click the run button up here and you'll see automate move through each step pausing there on number five. I was just writing in the list values CXL pop up as I kind of [00:08:30] paste those values in.
The task is completed here and if I check my output panel below we can see that it was completed successfully. I'll open the folder here and just to size a bit so we can compare the file name that we have with the values of our variables. Our files been placed into our AM training test folder with the name product list and then with today's date appended on the end as with our VAR date variable.
[00:09:00] When I open this file here, we'll see that Automate has written out line by line each of those products and we'll see that's done just as they were entered in our VAR list variable. Variables are also commonly used when a task involves collecting data from a source and then performing some action on it. Now that we have a spreadsheet with a list of data [00:09:30] to work with, we'll actually have Automate reopen this list and extract the Automate enterprise information from cell A3.
First we'll create a new variable that will hold the data that we extract from that cell. We'll call this one VAR cell contents. Click okay. And notice that when I [00:10:00] create the variable from the button on the top ribbon there, that it entered the variable as a first step of the task. You'll want to make sure that all variables are declared at the beginning of the task so that they exist when called upon throughout the task steps.
To pull the data out of our spreadsheet, we will use the get cell action and this time we will open the document that we just created. Again, we'll open our Expression Builder here and we'll identify the file name that we'll click and click insert. We're going to input that underscore [00:10:30] as well, and then we're going to put in the VAR date variable for today's date. And of course don't forget the file extension at the end.
Down here we'll tell Automate that we're going to get cell A3 which holds the Automate Enterprise information and we're going to populate our variable with the cell contents. We'll select our VAR cell contents variable here. Let's run this task again [00:11:00] so we can populate that variable with the results of the get cell action. But before that, let's quickly look down at our variables tab again.
The initial value here will be blank and it will stay blank, but we should see the data populate in the current value after we run the task. After running the task, we can see the current value reflects Automate [00:11:30] Enterprise, which was extracted from cell A3 of the spreadsheet. Now that there is a value assigned to that variable, let's execute another step using that variable. What we'll do is a dialogue message box.
We'll drag that over here and this step will open up a dialogue window that will display a message referencing our VAR cell contents variable. So open our expression and we'll pull that into our dialog box here and in front of the variable I'll hard code some text [00:12:00] saying the contents of the cell A3 are and then our variable. And so we'll run this task one last time and when it hits step eight we should see that dialogue we know pop up with our message.
I'll cook my run button and we'll see Automate jump through each step here, pausing slightly on six and seven as it writes in that data and then it extracts the text from cell A3 here. And here we have our dialogue message box that popped up [00:12:30] and it has our hardcoded text as well as the word Automate Enterprise, which was pulled in by referencing that variable. And there you have it.
Now that we understand how variables work, we'll talk more about datasets. A dataset is any named group of records. Unlike variables, data sets are used to represent data in a task that may be different each time a task runs. But unlike standard variables, data sets can contain multiple rows and columns of [00:13:00] information. In this task we need to extract data from a source in order to create a dataset and work with values that are populated there.
We want to start by creating a variable and we'll name that variable VAR file so that we can easily reference that to open the file when we will be extracting the data. Now copy this add folder path address and in this task we're going to use the financial sample data here and we're going to extract all of [00:13:30] the data from this spreadsheet. And then I'll paste in that folder path here and then write out the file name, financial sample, financial_sample.xlsx.
Now that we have the variable that we need in this task, I'll again use the Excel actions to extract multiple data [00:14:00] from that spreadsheet. Here we're going to use the get cells action as we did in our previous task, drag that over and we're going to open that VAR file which had the financial data inside of it. This time, rather than getting data from a single cell which we use to populate a variable in our previous task, we can get a range of cells by either reference or by position.
And we can populate [00:14:30] a dataset which we will name here. Again, it's important to use a standard naming convention for these like DS_ to signify dataset and then we'll call this one financial data. Instead of identifying specific cells from which I want to extract data, I can use the auto detect feature which will grab an extract everything that exists on that spreadsheet. We'll use the first row as column names here as well.
[00:15:00] Down here at the bottom variables tab, we can see that the dataset now exists but with no initial value or current value. If we right click and inspect the dataset, we'll see it's empty. As with the variable, we need to run the task to populate that data set. We'll close this and we'll go ahead and run that task. Now [00:15:30] we see down at the bottom here that the data set has a little arrow next to it.
And if we expand this, we'll see that Automate has extracted all of the columns and rows from that spreadsheet and we can see each of the values that exist for each of those columns. We scroll down a little bit further we'll see that the information has been extracted for all rows of the spreadsheet, each with an individual value. [00:16:00] Let's right click on the dataset header and inspect it to get a better view of what we're working with.
Now we see the format appears exactly the same as it did in our spreadsheet. Now that we've extracted this data, we can have Automate analyze the data based on certain criteria and or reference those dataset values throughout our task. One way of doing this is through a loop. Loop action allows us to take a dataset and loop through the rows one by one. [00:16:30] I'll type in the word loop in our search bar here, and we'll drag out the dataset loop action with each loop action.
With each loop, a series of steps are executed on the current role of the dataset. When the steps are complete, the row number is automatically incremented and a loop begins again for the next row. The loop ends when it reaches the end of the dataset or when a break is inserted. For example here, using our loop action we can select the data set that we want to loop through and analyze [00:17:00] each row and make decisions and review the values.
You can start an end the loop on a specified row if you wish, but if you leave these options unselected Automate will just loop through all of the rows of the dataset. Now that we have a loop set up, we need to insert steps that we want to execute between the beginning and ending of the loop action. In this exercise, we'll put in a single step again using our dialogue message or action [00:17:30] to displace some of the information held in each row of the dataset.
When this is configured and we run the task, we'll have five message boxes pop up displaying that information. Let's open our Expression Builder again to pull up data values that we want to see. Here in the Expression Builder, you'll see now that multiple hidden variables have been created for each cell of data that we extracted from our spreadsheet. There are two slightly [00:18:00] different sets of variables here for the data set.
One set that indicates a row number and one set that does not. When evaluating in a loop, you do not want to use the values with the row number indicated. This is because the loop already assumes that is analyzing the first row, executing some steps, then moving on to the second row and so forth and accounting for each piece of data on that row until it's completed the loop.
However, the row number of variables will definitely be useful if you're not going to be utilizing [00:18:30] them within a loop. For example, if you put a three in the financial data, year, variable, Automate will assume the value for the year column indicated in row number three. Since we are working in a loop with this exercise, we'll utilize the individual column variables. Let's start by displaying the country. We'll pull out the country here and click insert.
We'll make a new line, go back into our Expression Builder [00:19:00] and we'll add in the currency field. We'll add a couple more here as well making a new line after each one. We'll go with units sold for the next one and then finally we'll find the profit amount. Oops, not the row number one. Here's the profit one we want to use. [00:19:30] And as we did in our previous task, I'm going to precede these variable values with some text indicating what they'll reflect.
Notice here that I can put in that dollar sign right before the variable and I'll put a period 00 [00:20:00] right after this variable so that it will show a dollar amount. Now as we run this task, again, Automate will extract the data from the spreadsheet and populate that into our dataset. Then we're going to loop through each row of the dataset and display a message with the selected column values for each row.
We'll get five total message boxes pop up and we'll see that it'll start here from Australia and it will work its way down through Japan displaying that detailed information that we outlined in our [00:20:30] display message action. We see our first pop up here, Australia currency is AUD, the number of units sold, and then the profit there in USD with the format that I wrote out.
We're seeing this kind of jump through the loop really quickly here because we only have one step that we're executing within that loop. Got our United States which was our third sale. Our [00:21:00] fourth sale was in France. And then finally after clicking okay, we'll see that last sale of Japan.'