Help

Re: Automation - Exceed quota 30 table queries, what to do?

1057 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Emilie
4 - Data Explorer
4 - Data Explorer

Hello,

I have this javascript that i want to add in my automation in order to transpose a table but i always have this error message "Exceed quota 30 table queries". How to solve it?

 

// Get the "Forecast artikel" table and the "Transposed" table
const tableForecastArtikel = base.getTable("Forecast Artikel");
const tableTransposed = base.getTable("Transposé");

// Retrieve all records from the "Forecast artikel" table
const queryForecastArtikel = await tableForecastArtikel.selectRecordsAsync();

// Array to store updates
let updates = [];

// Iterate through records in the "Forecast artikel" table
for (const recordForecastArtikel of queryForecastArtikel.records) {
// Get the article name from the "Name" column of "Forecast artikel"
const articleName = recordForecastArtikel.getCellValue("Name");

// Iterate through columns like "Tag 1," "Tag 2," "Tag 3," and so on in "Forecast artikel"
for (let i = 1; i <= 31; i++) { // Make sure to adjust the upper limit based on the number of columns in "Forecast artikel"
const columnName = `Tag ${i}`;
const cellValue = recordForecastArtikel.getCellValue(columnName);

if (!isNaN(cellValue)) {
// Retrieve corresponding records in "Transposed" based on "Name" and "articleName"
const queryTransposed = await tableTransposed.selectRecordsAsync({
filterByFormula: `AND({Name} = '${columnName}', FIND('${articleName}', Name) > 0)`
});

// Update corresponding records in "Transposed" with values from "Forecast artikel"
for (const recordTransposed of queryTransposed.records) {
try {
const transposedName = recordTransposed.name;
const transposedNameParts = transposedName.split(' ');
const lastPart = transposedNameParts[transposedNameParts.length - 1];

if (lastPart === i.toString()) {
updates.push({
id: recordTransposed.id,
fields: {
[articleName]: cellValue,
}
});
}
} catch (error) {
console.error(`Error during update: ${error.message}`);
// Continue with other records in case of an error
}
}
}
}
}

// Split the "updates" array into batches of 30 updates and perform the updates
while (updates.length > 0) {
const batchUpdates = updates.slice(0, 30);
updates = updates.slice(30);

// Use the "updateRecordsAsync" function to update records in batches
await tableTransposed.updateRecordsAsync(batchUpdates);
}

console.log("Data update in the 'Transposed' table from 'Forecast artikel' completed successfully.");

 

Thank you !

Emilie

15 Replies 15

I don’t have links to Bill’s post. It was before the forum changed software platforms. And I am on my phone and the searching this forum on mobile is lousy. 

@MaddieJ 
This could be a useful exercise for you. How many of the articles written by Bill French about this topic can you find? He has posted about this topic multiple times? Bonus points if you can find the thread where Bill first teaches me the technique. Extra bonus points if you can find the thread where I explain to Bill why we have to use square brackets when using a variable as a key name in the hash map. This was really useful content that could continue to help people, if only they could find it. 

NR
5 - Automation Enthusiast
5 - Automation Enthusiast

In case anyone else reads this thread and goes on the hunt for one of the articles that @kuovonne mentioned above like I did.
https://community.airtable.com/t5/show-tell/filtering-with-loops-in-javascript-how-to-avoid-insanity...

Wow, @NR THANK YOU for finding that thread. It brings back so many memories. 

Would you mind sharing how you found it? Did you happen to pick just the right keywords? Were you using search functions that are not available on mobile?

NR
5 - Automation Enthusiast
5 - Automation Enthusiast

I am afraid I didn't use any advanced techniques, this was actually my first time on the forum.

I didn't try to search since I was not really sure of the keywords to use from the original article. Since the thing I was sure was the author (Bill French), I looked for his profile page (https://community.airtable.com/t5/user/viewprofilepage/user-id/30391). Then looked at the titles of his articles from a couple of years ago.

The irony of all this. In 1999 two fellers in a garage figured out how to make stuff findable. Yet, I had to write this and we continue to struggle to find anything on this community. This is a crime against all who would spend great effort to help others. A real shame.

https://community.airtable.com/t5/show-tell/it-s-2023-and-i-can-t-find-me/td-p/140263