How to loop through an array of results and hit an API for each value in a specified column

Queries and Resources
,
1

Hi there,

Saw similar posts in the forum about this but couldn't get my head around them as the examples differed from mine and was hoping for some advice.
I have an app that currently does 3 things.

  1. It pulls up a list of files in a queue using a database query and displays them in a table.
  2. The 'doc-id' column of the table contains values that are used in our API call.
  3. A button in the app triggers a PUT request to the API that will clear out a secific document in the following format:
    https://website.co/files/{{table_results.selectedRow.data.doc-id }}/mark_as_ignored

This is fine but there can be a lot of documents to clear and doing them one by one is boring.
It seems to me that I can make another query that loops through the column of results and will run a PUT request for each doc id. I've tried doing that myself but I'm in syntax hell now and I'm not even sure what to select as my resource.
Any tips?

2

Hello!

We have an example in our docs like this:


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

You should be able to replace rows with the id and run through the array of ids.

Could you share the query you have right now?

1 Like
3

THanks for your quick response! Sure, here's a slightly modified version of my query:

SELECT
  a.id AS "Doc-id",
  l.clientID AS "Client ID",
  l.name,
  a.status as "Status"
FROM database.accounts a JOIN database.ledger l ON aId = l.id 
WHERE l.clientId = {{textinput1.value}} AND a.status = 'PENDING'
ORDER BY a.id ASC;

Then as mentioned previously, I have a button in app that I use to run the PUT request to remove the file that's pending.
Not sure how to use the code you linked to do what I need to do here, let me know if screenshots of the app are required!

4

Following on from this, here is my attempt at using the code you linked @jSims but it's not running for me:

var rows = [{{tableresults..selectedRow.data.doc-id}} ];

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);

  query5.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);

Not sure how to set my array length to the length of my results in that table..

5

Still searching for a solution here if anyone could provide one!

6

Could you describe what's happening now when you say it's not running? What error messages are you seeing?

You won't need the {{ }} in a JS query. Also, table.selectedRow.data is more than likely just one value.

I think if you'd like to iterate through every id you would use something like table1.data.id which will be an array of objects.

image
image1182×356 30.6 KB

7

Hey J, thanks for getting back to me!
Here's what I've got so far:

query
query2204×1042 156 KB

It just runs infinitely and says 'query 5 ran successfully' over and over.
I'm sorry if what I'm struggling with is a little basic!
Thanks

8

No need to apologize!

Don't forget this step! Or it will run infinitely.

Are you seeing the console.log("Running query for row", data)?

Also, line 1 doesn't need to be in [ ] as it's an array already and you've actually made a nested array. (You can confirm that by looking at your left-hand panel .data property on the off chance it's not!)

9

Ah sorry I have added that and it still gives me the same issues and runs infinitely.
Here's what I've got so far.

query_2
query_21218×990 165 KB

I'm not sure where to call in the trigger for my API request 'ignore_rec_file' , or why it keeps loading infinitely, thanks again for your assistance!

10

Ok! Can you write to support so that we can step into your app? If you can link this community post when you write in that would be a huge help! You can use the chat icon in the bottom right of "Edit" mode. :smile:

1 Like