How to use the loop function to solve my data issue

Workflows
,
1

Hi All,

i really hope somebody can help me, I am using the workflow option and have some issues I really want to get sorted :slight_smile: I am using the retool database, in the workflow I get a list of ean numbers, then I loop through a rest api and get the ean information based on the list from the database. So far so good, works fine when I have around 150 to 250 records. When I have more the api returns bad data and I am not able to push it to my other database. I have tried to add delays to get the rest data but that does not really help or it will be to slow and retool will give a time out.

So based on below screenshot, does anybody know how I can loop this? I now added a slice of 150 records, I would like to get 150 records, update the database, get 150 records, update the database etc etc.

image
image1455×864 131 KB

I added a count result that I might be able to use to see how many times it should loop the 150 records, not sure if that's the way to got, I am a bit stuck :frowning:

WITH total_records AS (
  SELECT COUNT(*) AS count
  FROM import
)
SELECT CEIL(count::NUMERIC / 150) AS rounded_up_result
FROM total_records;
2

Hey @Sander!

This thread convers something similar, I wonder if _.chunk and await might be useful for you as well :thinking:

Could something like this work?

const batches = _.chunk(getean.data, batchSize);
const fullDataSet = [];
for(let eanValues of batches){
  const promises = eanValues.map(/* trigger queries */);
  const results = await Promise.all(promises);
  fullDataSet.push(...results.flat());
}

return fullDataSet;

You might also explore using a loop block here!

3

Thanks for the reply, to be honest your option is out of my coding league, I have some basic knowledge and chatgpt is helping me :slight_smile:

Maybe to make it simpler I created this, still works and I could just create a workflow that would go from 1 to 200, update db, go from 201-400, update db etc etc but their must be a smarter simple way that I can understand

const arr = getean.data;

const startIndex = 801; // Set the start index here (0-based)
const endIndex = 1000; // Set the end index here (exclusive)

const eanValues = arr.map(item => item.ean).slice(startIndex, endIndex);

const promises = eanValues.map((ean) => {
    return new Promise(resolve => {
        resolve(eanloop.trigger({
            additionalScope: {
                ean: ean 
            }
        }));
    });
});

return Promise.all(promises);
4

I see, alright! In that case if you already have a code block that's working for a smaller array I would go in the direction of the loop block. You can create a JavaScript code block that just runs _.chunk on your data (note that the first parameter is the data you want to separate into batches, and the second is the batch size). Then you can reference the result as the iterable for your loop block:

Instead of using the .trigger method for your API query you can also define it as a function block and specify the ean as one of its parameters:

That makes it easy to call!

In your case, you'd probably want to use something like the following in your JS query:

const eanValues = getEan.data.map(item => item.ean);
return _.chunk(eanValues, 200);

But if you define eanLoop as a function block then the code in your loop can likely be exactly the same as in the screenshot above!

const promises = value.map(eanLoop);
return Promise.all(promises);
1 Like