How to use join fields

While custom IQL fields can help transform your data within a Collection into a more useful state, you might also need to connect data across Collections, Datasets, or Views.

This is a job for join fields. Join fields are like a vlookup table in Excel, but more powerful. Joining data from various Collections, Datasets, or Views on common fields can provide you with a more holistic view of your business data.

Join fields can help with use cases like:

• Relating a claims form to customer policy data
• Tying purchase orders to customer records from your CRM
• Connecting invoices to a vendor database and/or AP system

Create a join field

1. Open any file and click New field.
2. Hover over the three dots beside the field types for the Advanced menu and select Join data.
3. Give your join field a name.
4. Choose the first field you want to join (this must appear in your current Collection).
5. Select the Collection, Dataset, or View that you wish to connect the first Collection to.
6. Finally, choose the field within the second Collection, Dataset, or View and click Create field.

Example 1: Joining two Collections

The context

Let's say you have two Collections containing the same set of purchase orders (PO):

Collection 1: Purchase Order Customer Info

This Collection contains a group of POs from which you’ve extracted customer data:

• Name
• Address
• Company
• Phone number
• PO Date
• PO Number

Collection 2: Purchase Order Items

This Collection contains the same exact set of PO files, but instead of customer data, it has extracted fields pertaining to the items sold:

• PO #
• QTY
• Unit
• Description
• Price
• Total

The problem

You want to bring in all the extracted data from the Collection, Purchase Order Items, into the Collection, Purchase Order Customer Info, so you can see all data from the customer information and inventory items all together.

The solution

Both Collections contain fields with the purchase order number. In one Collection, it’s labeled as “PO Number,” and in the other, it’s labeled as “PO #.” As long as they contain the same data, these fields can be joined regardless of the differences in their names.

We’ll create a join field and use the purchase order number as a bridge between the two Collections. Creating this field in Collection 1 will bring in all the data that corresponds with the PO # in Collection 2.

Step 1

1. Open any file in Collection 1 in Extraction view and click New field.
2. Hover over the three dots next to “New field” to open the Advanced menu.
3. Select Join data.

Step 2

1. Name your new join field — e.g., “Join field”.
2. For “Field on Purchase Order Customer Info” choose PO Number.
3. For “Second Collection, Dataset, or View” choose Purchase Order Items.
4. For “Field on second Collection, Dataset, or View” choose PO #.
5. Hit Create field.

Step 3

Exit Extraction view to return to Collection view and you can see that a join field has been created in Collection 1 (Purchase Order Customer Info) that contains all the data from Collection 2 (Purchase Order Items).

Example 2: Joining a Collection to a Dataset

The context

You work for a company called Benson’s Boutique Goods that receives thousands of invoices per month from various vendors that contain data fields like Company name, Date, Vendor ID, and Total.

Benson’s keeps a spreadsheet called Vendor Database that also contains the Vendor ID field, but it also has a lot of additional information about each vendor not available on an invoice that the accounting department needs.

The problem

In order for the accounting department to process invoices they need all the vendor data available in the Vendor Database. Instead of manually searching for the appropriate Vendor ID, they need a way to automatically look up vendor data that’s tied to a specific invoice and have all that data sent to them in a consolidated manner.

The solution

Both the invoices and Vendor Database contain the field, Vendor ID, so you can use that field as a bridge between the two. The bridge is formed by creating a join field between the extracted Vendor ID from the invoices and the existing Vendor ID field in the Vendor Database.

Step 1

1. Open the Vendor Database CSV file as a Dataset. Learn how.
2. Go to the Vendor Invoices Collection and open any file. Click New field and hover over the three dots to see the Advanced menu.
3. Name your new join field — e.g., “Vendor join”.
4. For “Field on Vendor invoices” choose Vendor ID.
5. For “Second Collection, Dataset, or View” choose Vendor Database.csv (the Dataset created earlier).
6. For “Field on second Collection, Dataset, or View” choose Vendor ID.
7. Hit Create field.

You can see in the screenshot below that your join field in the Vendor Invoices Collection has pulled in all the associated data from the Vendor Database Dataset, using the Vendor ID field to connect the two:

Example 3: Joining a Collection to a View

Step 1

Go to your Collection and click the + button in the top-right corner of your table.

Step 2

1. Name your field, choose Join data under Type, and choose a field that both this Collection and the View in question have in common — e.g., ID.
2. Choose the View you want to join.
3. Select the field in this View that corresponds with the field in your Collection and hit Create.

The ID field will act as the bridge between the Collection and View, and all the data from your View will arrive into your Collection and is displayed under this new join field: