cancel
Showing results for 
Search instead for 
Did you mean: 

Performing a lookup for all of the items in an array

snm
Deputy Chef II
Deputy Chef II

I'm working on a recipe that trigger's off of a new API request. In the body of this request, is an array of internal IDs.

Within Workato, I have a lookup table of internal IDs and their matching external IDs. I need to perform a lookup against each item in the array to replace the internal ID with the external ID, and pass that to the intended destination as a comma separated string.

I am unable to figure out the correct formula to achieve this result. Any help would be greatly appreciated.

2 ACCEPTED SOLUTIONS

gary1
Executive Chef II
Executive Chef II

You already have the LUT in place, but for the sake of providing a thorough example...

Start with creating the lookup table. Mine is called "test" with an "internal" and "external" column.

gary1_5-1701123268001.png

Next, in the recipe arrange your IDs into an array of objects. You'll want to use an array of objects because this will allow you to perform all of the lookups in a single bulk task, and you will not need to loop through the whole array one by one.

Here's my ID data:

gary1_1-1701122967883.png

Which outputs this:

gary1_2-1701122990509.png

Next, use Variable > Create List to make a list with a single field for the lookup value. I called mine "map"

gary1_3-1701123037628.png

Next, expand the items section in the action. Add the array of objects as the source list, and then use the lookup formula to perform the lookup.

gary1_6-1701123440502.png

Running this will output the following:

gary1_7-1701123559954.png


If you want to store the internal and external ID, add another item to the list for "internal":

gary1_8-1701123616248.png

And then map in the ID from the array:

gary1_10-1701123657441.png

This will output:

gary1_11-1701123699134.png

This next bit is purely information, but what's actually happening here is that `lookup("test", "internal": [id])` is simply returning the entire row of the lookup table as an object, and then `["external"]` is isolating the value of the "external" key in the object. 

You can see this better if you remove `["external"]` from the formula:

gary1_14-1701123778087.png

Output:

gary1_13-1701123739746.png

Hope this helps!

 

View solution in original post

gary1
Executive Chef II
Executive Chef II

You're welcome, glad to help.

Edit: Before I get into it, for the latter half of your first question, I want to clarify that creating the array of objects is for your input, which I believe are the internal IDs being provided by the API response. You don't need to reformat the LUT at all, this is all handled by the lookup function/formula.

For question 1, this is the trickiest part and IMO Workato needs to provide a cleaner way of doing this.

If your array is one-dimensional like below, then you'll have to jump through some hoops to convert it to an array of objects. (I can't think of an easier way). Step 2:

gary1_0-1701128009888.png

First, use Lists > Create repeat helper list and .size to make an array object the same length/size as the input (in this case, 3). This creates an iterator that allows you to create a loop the same length/size as the array (in this case, 3 times). Step 3:

gary1_1-1701128135421.png

Then, use Variable > Create list to make a new list variable with an "id" item, like this. Step 4:

gary1_2-1701128224222.png

(Important note: even if your IDs are integers, you'll want to store them as strings. This is because all data in the lookup tables is stored as strings, and the lookup function uses a strict data type. If you try to lookup using an integer, you'll get an error.)

In the same action, use the list from repeat helper list (step 3) as the item source list. This essentially tells the batch/bulk add to loop three times (and provide an index for each loop).

The magic happens in the formula for the ID item. Use the message/one-dimensional array from step 2 as the item, and use the index from step 3 (the iterator) to identify the index of the item from step 3. Like this:

gary1_5-1701128468223.png

Because you're doing this in a batch/bulk step, it does all the looping in the background. Under the hood, it's basically doing this:

  • Loop 1/Index 0: ["123","456","789"][0] // "123"
  • Loop 2/Index 1: ["123","456","789"][1] // "456"
  • Loop 3/Index 2: ["123","456","789"][2] // "789"

And because it's a bulk/batch action, it only costs one task and you avoid looping through all 88 IDs.

The output of this step should look like this:

gary1_6-1701128679313.png

Now, you can use this with my previous response for the lookup formula.

For your second question, if you follow my first response and store all of the lookups in a list called "lookup" with an item called "map", you can use the following formula to pluck the "map" values from the array and join them together:

Formula:

gary1_8-1701128972539.png

Output:

gary1_7-1701128955761.png

Add a space after the comma in join for a little more breathing room.

Hope this helps!

View solution in original post

3 REPLIES 3

gary1
Executive Chef II
Executive Chef II

You already have the LUT in place, but for the sake of providing a thorough example...

Start with creating the lookup table. Mine is called "test" with an "internal" and "external" column.

gary1_5-1701123268001.png

Next, in the recipe arrange your IDs into an array of objects. You'll want to use an array of objects because this will allow you to perform all of the lookups in a single bulk task, and you will not need to loop through the whole array one by one.

Here's my ID data:

gary1_1-1701122967883.png

Which outputs this:

gary1_2-1701122990509.png

Next, use Variable > Create List to make a list with a single field for the lookup value. I called mine "map"

gary1_3-1701123037628.png

Next, expand the items section in the action. Add the array of objects as the source list, and then use the lookup formula to perform the lookup.

gary1_6-1701123440502.png

Running this will output the following:

gary1_7-1701123559954.png


If you want to store the internal and external ID, add another item to the list for "internal":

gary1_8-1701123616248.png

And then map in the ID from the array:

gary1_10-1701123657441.png

This will output:

gary1_11-1701123699134.png

This next bit is purely information, but what's actually happening here is that `lookup("test", "internal": [id])` is simply returning the entire row of the lookup table as an object, and then `["external"]` is isolating the value of the "external" key in the object. 

You can see this better if you remove `["external"]` from the formula:

gary1_14-1701123778087.png

Output:

gary1_13-1701123739746.png

Hope this helps!

 

snm
Deputy Chef II
Deputy Chef II

Thank you so much for your time and effort in putting together this walkthrough! A couple of follow-up questions:
1. With regard to arranging the ID's as an array of objects, where would that need to be done exactly and does it have to be the entire list of ID's? My LUT has 88 entries.
2. How can I get the output of the lookup as a comma separated string?

gary1
Executive Chef II
Executive Chef II

You're welcome, glad to help.

Edit: Before I get into it, for the latter half of your first question, I want to clarify that creating the array of objects is for your input, which I believe are the internal IDs being provided by the API response. You don't need to reformat the LUT at all, this is all handled by the lookup function/formula.

For question 1, this is the trickiest part and IMO Workato needs to provide a cleaner way of doing this.

If your array is one-dimensional like below, then you'll have to jump through some hoops to convert it to an array of objects. (I can't think of an easier way). Step 2:

gary1_0-1701128009888.png

First, use Lists > Create repeat helper list and .size to make an array object the same length/size as the input (in this case, 3). This creates an iterator that allows you to create a loop the same length/size as the array (in this case, 3 times). Step 3:

gary1_1-1701128135421.png

Then, use Variable > Create list to make a new list variable with an "id" item, like this. Step 4:

gary1_2-1701128224222.png

(Important note: even if your IDs are integers, you'll want to store them as strings. This is because all data in the lookup tables is stored as strings, and the lookup function uses a strict data type. If you try to lookup using an integer, you'll get an error.)

In the same action, use the list from repeat helper list (step 3) as the item source list. This essentially tells the batch/bulk add to loop three times (and provide an index for each loop).

The magic happens in the formula for the ID item. Use the message/one-dimensional array from step 2 as the item, and use the index from step 3 (the iterator) to identify the index of the item from step 3. Like this:

gary1_5-1701128468223.png

Because you're doing this in a batch/bulk step, it does all the looping in the background. Under the hood, it's basically doing this:

  • Loop 1/Index 0: ["123","456","789"][0] // "123"
  • Loop 2/Index 1: ["123","456","789"][1] // "456"
  • Loop 3/Index 2: ["123","456","789"][2] // "789"

And because it's a bulk/batch action, it only costs one task and you avoid looping through all 88 IDs.

The output of this step should look like this:

gary1_6-1701128679313.png

Now, you can use this with my previous response for the lookup formula.

For your second question, if you follow my first response and store all of the lookups in a list called "lookup" with an item called "map", you can use the following formula to pluck the "map" values from the array and join them together:

Formula:

gary1_8-1701128972539.png

Output:

gary1_7-1701128955761.png

Add a space after the comma in join for a little more breathing room.

Hope this helps!