How-To Work With Lists

Foxtrot offers a wide variety of actions and features to allow you to work with data in list formats. Lists are essentially a data set structured as a table, allowing you to work with a lot of information at the same time in a single object instead of, for example, having to use many variables. Generally speaking, there are two types of lists in Foxtrot;

• Custom lists
  • These lists can be created using a wide variety of methods, for example, extracted from a web table, an Excel workbook, or an SQL statement.
• Files & Folders lists
  • These lists can only be generated using the Create List action, storing a list of files, folders, or both from a specified directory.

Why would you use lists? Lists enable you to, for example, loop through a list of data (use the Loop action with the option List). Lists are also a way of transfering a lot of data with a few actions, fast. For example, it is possible to navigate to a website, target a web table, use the Get List action to load all the data from the web table into a list to then use the Send List action to an exact cell in Excel - with two actions, you have transfered a full set of data from one application to another.

This article will help you get started working with lists. After summarizing the full list of actions related to the list functionalities, we will look at the Files & Folders list type before exploring some of the many options of Custom lists.

Full List of List Actions

Here is a short description of the actions that are currently available in Foxtrot related to lists:

Name	Description
Clear List	Deletes all rows from the list
Clear List Column	Deletes all values from the list column
Clear List Row	Deletes all values from the list row
Create List	Creates a new Files & Folders list or Custom list
Create List Column	Creates a new column in the selected list
Create List Row	Creates a new row in the selected list
Delete List	Deletes the selected list
Delete List Column	Deletes the selected column in the selected list
Delete List Row	Deletes the selected row in the selected list
Duplicate List	Makes a copy of the selected list
Duplicate List Column	Makes a copy of the selected column in the selected list
Format List Column	Formats an exact column or all columns in a list using formulas
Get List	Generates a list of data from the selected target
Get List Info	Gets information from the selected list, such as number of items
List Column Exists	Evaluates whether a specific column exists in the selected list
List Exists	Evaluates whether a specifc list exists in the project
Merge Lists	Appends all data from a second list to the selected list
Move List Column	Changes the position of the selected column in the selected list
Move List Row	Changes the position of the selected row in the selected list
Open List	Generates a list of data from a selected file
Query List	Updates the selected list or generate a new list using criteria
Remove List Duplicates	Removes all duplicate items in the selected list
Rename List	Changes the name of the selected list
Rename List Column	Changes the name of the selected column in the selected list
Reverse List	Reverses the order of the items in the selected list
Save List	Outputs the data from the selected list in a file or variable
Send List	Writes the data from the selected list to the selected target
Set List Value	Sets the value of a specific field in the selected list
Shuffle List	Randomly updates the order of the items in the selected items
Sort List	Sorts the order of the items in the selected list
Subtract List	Subtracts all items from a second list in the selected list

 

Files & Folders lists

Files & Foldesr lists are designed to enable you to retrieve a list of files, folders, or both from a specified directory that you can then either use to loop through or extract certain information about the files and/or folders. A typical usage of Files & Folders lists is to generate the list to retrieve a list of PDF files in a specific folder to loop through the number of PDF files to extract the text and perform some actions using the text.

To generate a Files & Folders list, use the Create List action via the action list:

Here is an example using the action to retrieve all PDF files from an "Invoices" folder:

Under lists, you can now find a list with the specified name containing an item for each file in the folder:

You can now, for example, create a Loop action from the action list and select the generated Files & Folders list to loop through:

Now, make sure that your loop is "activated" - indicated with the "(X of Y) -, then you can use all the values from the Files & Folders list in the "Loop" tab of the Expression Builder in any action inside of the loop. Here is an example:

In this next screenshot, notice how a full loop is set up using this approach. The loop used in below screenshot is inspired by the method presented in this article:

Custom lists

Custom lists are basically any list that is not a Files & Folders list. The difference between the two is that Files & Folders lists are lists generated by Foxtrot with a specific set of columns known by the engine, therefore, you have the great option to pull all the list values directly from the Expression Builder using tokens as illustrated above. Since the engine of Foxtrot cannot predict the layout of custom lists, this is not available here. Instead, you can use the lookup functionality.

You can create custom lists in various ways. These are some of the typical methods used to generate custom lists in Foxtrot:

• The Create List action available in the action list to create an empty custom list with specified columns. After creating the list, you can use the actions Create List Row and Set List Value to add data to the list.
• The Open List action available in the action list to load data from various file types using the import engine of Foxtrot.
• The Get List action available when target specific control types to load the data from an open application into a list. This action is available when targeting, for example:
  • Excel cell ranges and tables 
  • Web tables
  • List controls
• The Execute SQL action available in the action list to query an SQL database to load data from a table into a list.

Whatever method you use to create the custom list, the general concept is the same. To explain the many features and options available when working with custom lists, we will take a look at an example of extracting data from a web table, format the data in the list before finally sending it to Excel.

Let us assume we need to extract all the data from the web table from this website:

First step will be to target the first cell of the web table, that is the recommended best practice for targeting web tables if you wish to extract all the data. Then, in the target preview, you can click on the arrows to the right to switch the target from Exact Target to Table:

Now, use the action Get List action in order to retrieve the data from the web table:

In the settings of the action, give the list a name, decide whether or not it should overwrite any existing action with the same name, and the "Include headers" option allows you to indicate whether the data you are retrieving contains column headers already or not. If this option is NOT selected, Foxtrot will generate some generic column names "Col 1", "Col 2", etc.

We now have the list of data inside of Foxtrot. For the sake of the example, let us assume that we need to perform some changes to the list before sending it to Excel. It is recommended to always make a copy of the original set of data before performing any changes, this way, you always have the original list as reference if something should go wrong. After making a copy of the list, let us make some changes to the list:

• First, let us delete the last column as we are not interested in that.
• Second, we need to make sure that there are no surrounding spaces in any of the columns (notice the second column actually has spaces in front of all the numbers, which is of course not optimal)
• Third, we are only interested in the institutions with total assets equal to or greater than 500,000.
• Fourth, in our environment we actually need dots instead of commas as thousand separators.

IMPORTANT: It is essential that you perform step number three before step number four since math calculations and evaluations should always be performed on data in American format.

First step is to create a copy of the list. We do that using the Duplicate List action:

Next, we use the Delete List Column available in the action list to delete the desired column:

Now, in order to remove any surrounding spaces in any cell across all columns, we can use the Format List Column action to trim everything.

We have now reached the third step, where we are looking to remove all list items NOT equal to or greater than total assets 500,000. To perform a step like this, we use the powerful action Query List that enables you to either manipulate existing lists or generate new lists with the items meeting your criteria. Notice how we simply save the output back to the same list.

Lastly, we use the Format List Column again to replace all commas with dots to properly format the numbers of the list according to what we need.

Now we are done applying changes to the list. This is the output.

Last step of this example is to send the data to Excel. To save a list to Excel, you can either write the list to an opened workbook using the Send List action (via targeting) or generate a new Excel file with the list using the Save List action (via the action list). Let us have a look at the Send List action. First, target the cell from where you would like the list to start, for example, cell A1.

Now, set up the action as you desire and execute it.

This will be the output.

This should give you an idea of how to work with custom lists, retrieving data from a source, format the dataset and use it.

Loop Custom lists using lookups

In above example, we do not actually loop through the list of data, we simply send all of it to Excel. However, in many cases, you need to loop through the custom list of data. As mentioned, Files & Folders lists allow you to access all the specific tokens in the Expression Builder when working in a loop with this list type, however, that is not available for custom lists. Instead, you can use the lookup functionality. It is very similar to the Excel VLOOKUP formula and requires the following arguments to be specified:

• Select the list from which the data needs to be retrieved from
• Look for: a value that the LOOKUP searches for
• Look in: a column to search for the specified value
• Retrieve: a column from which the value from the same position needs to be retrieved

Let us look at an example of the concept using the data from above example. Of course, this is a quite fictive example of using the data from the list, but it should give you an idea of how to use list lookups in loops.

Start by creating a Loop action via the action list:

Hereafter, we can start grabbing the data from the list using list lookups. It is recommended to create variables to store the data from the list to be used during the loop. Here is an example. First, make sure that your loop is activated. Then, select the Create Variable action. In the value field, expand the Expression Builder in order to make a list lookup. Basically, this works like lookups in Excel. First, you specify the list to perform the lookup in, then, you specify what to look for, and in loops you should look for the current loop number in the Item No column. Finally, you specify what to retrieve:

And this would be the final formula for the list lookup after clicking "Add".

This will create a variable with the value of the first institution name of the list.

If you proceed to loop number two, the value of the variable will be updated to store the institution name of item number two of the list.

That is the core basic of looping through custom lists in Foxtrot. Of course, you can create as many variables as you need for the specific loop.

And, list lookups are not only relevant in loops. You can even use list lookups for other things, for example, if you know the institution name after extracting the value from some data source like an email, you can use a lookup to retrieve the total assets of the institution by using the institution name as the lookup value to retrieve the total assets.