Performing a lookup for all of the items in an array

[snm]

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.

[gary1 · Accepted solution]

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.

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:

Which outputs this:

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

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.

Running this will output the following:

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

And then map in the ID from the array:

This will output:

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:

Output:

Hope this helps!

[gary1 · Accepted solution]

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.

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:

Which outputs this:

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

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.

Running this will output the following:

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

And then map in the ID from the array:

This will output:

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:

Output:

Hope this helps!

[snm]

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 · Accepted solution]

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:

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:

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

(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:

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:

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:

Output:

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

Hope this helps!