Running a query on an Array

[evanhouten]

Trying to run a REST API query on an array. I followed and tried to adapt what the article below stated, but it seems like the query is passing the Object not the value. I'm not sure how to correct that. The only difference being that I pulled the information into the table from another SQL query and didn't just paste in the data. Also the URL for the query is in the middle instead of the end of the URL like in the article so couldn't place put in URL parameters.

Any help would be great! Warning, I'm new to this so baby steps would be appreciated.

https://docs.retool.com/docs/scripting-retool#section-triggering-a-query-for-each-item-in-an-array

[Kenny]

Hi @evanhouten ! You're in the right place for documentation if you're trying to run a query for each item in an array! If you scroll down a little bit on that page you'll see a JavaScript section for triggering a querying for each item in an array which is a little different from the screenshot you posted.

Looking at your errors, it seems like you're passing in an entire object into your REST API query URL, which the query doesn't like, and throwing you those errors, but we can fix that! You'll just need to specify a key within your object.

For example:

If your object looks like this:

myObject = {
  name: "bob",
  job_title: "builder"
}

Then you'll want to key into that object and pull whichever data you're trying to populate the URL with. If you're trying to use 'name' from the object example, here's what it would looks like:

api/v1/devices/{{myObject.data.name}}/switch/ports.

and here's what it should evaluate to:

api/v1/devices/bob/switch/ports.

and here's what that may look like in your app:

Hope this helps!

[evanhouten]

@Kenny

myObject = {
  name: "bob",
  job_title: "builder"
}

Where would I define the object? In the script or in the query itself?

Also the URL format is different, where in the example they were putting the URL variable at the end, the API call I have to use is replacing the value in the middle of the URL.

(URL prefix)api/v1/"Property that needs to be replaced"/switch/ports

[Kenny]

Great question @evanhouten! If you already have a query set for your data and want to key into it, you can simple use double-curly brackets to grab the data from that query. For example, if your query's name is "getMyData", you can use {{getMyData.data}} to reference that information.

Another option is to create a transformer (docs). This allows you to save variables and use it all throughout Retool. You would access information from it the same way as the query above. For example, if your transformer's name is "transformer1" and it returned:

return {name: "bob",job_title: "builder"}

You can access "bob" from "transformer1" data by writing {{transformer1.value.name}}.

When trying to add a variable in between the URL, you should be able to use what was mentioned above. Again, with the example for 'bob':

• if using a transformer you can write: api/v1/{{transformer1.value.name}}/switch/ports
• if using a query you can write: api/v1/{{getMyData.data.name}}/switch/ports

Hope this is helpful and works for you!

[evanhouten]

I must not be understanding you correctly. Currently I have the following:

query11 - pulling all device information from the organization
Switchinfo - SQL query pulling just serial numbers from query11
table1 - displaying a list of serial numbers from Switchinfo
query1 - POST REST API query with table1.data[ i ] variable in the place we need to input all the data

button1 - This runs the script from the original post.

If I reference an object like you mentioned, do I need to put in every value into that object to let the script run through each of them? I tried that by making another sql query and added a few serial numbers and it didn't work. Using your example my SQL query has Bob, Evan, Mary etc... How can I have it read only one of those at a time and move onto the next?

At this point I've tried several other options I've found online and none really seem to be working for me. I'm sure it's something simple that I'm doing wrong, but I'm lost at this point...

Example 1

table1.data.forEach(row => query1.trigger)

Example 2

const arr = SwitchInfo.value //your array of things to iterate over
const query = query1 // your query to trigger for each

const promises = arr.map((item) => {
return query.trigger({
additionalScope: {
Switchinfo: item //assignment_id would be available inside {{assignment_id}} in the target query when run
}
});
});

return Promise.all(promises)

Example 3 - This one I tried with just entering several different serial #s for the a values.

var rows = [{ a: 1 }, { a: 2 }, { a: 3 }, { a: 4 }];

function runQuery (i) {
if (i >= rows.length) {
console.log('Finished running all queries');
return;
}
var data = rows[i];
console.log('Running query for row', data);

query1.trigger({
additionalScope: {
data: data
},
// You can use the argument to get the data with the onSuccess function
onSuccess: function(data) {
runQuery(i + 1);
}
});
}

runQuery(0);

[Kenny]

Sorry for not being as clear @evanhouten . Perhaps I'm misunderstanding what you're building, but looking over your examples I do see a fix you could try.

Example 1:

There seems to be a syntax error. If you're trying to get query1 to trigger for each row of data, you're just missing the open-close parenthesis invocation sign. You'll just need to change the code to table1.data.forEach(row => query1.trigger() ).

There seems to be a lot going on here and I think it might be more helpful in understanding what you're trying to do if we're able to take a look inside your app. Would you mind writing into Support (bottom right '?' icon from any app > Support), or the big Intercom chat icon on the bottom right of our docs.retool.com pages?