Skip to main content

I have written the script below to select records from one table in my base and insert them as new records in a second table.  The script is working, however it times out after running for 30 seconds.  

The process is selecting and creating about 200 records.   Is there a more efficient way of writing this script so that it happens faster and doesn't hit the 30 second limit?   I've read that maybe i need to create an array so that multiple records are inserted at once, but I am brand new to scripting and not sure how to achieve this.

Any updates / changes / suggestions would be most welcome!

Thanks

 

 
 

 

//Script Purpose:
//1. Select fields "Client" and "Client Family" from all records in the table "Client Master"
//2. Using these values, create new records in the table "Bank Recs"

//Define source table
let table = base.getTable("Client Master");

//define fields to retrieve
const fldClient = table.getField("Client")
const fldClientFamily = table.getField("Client Family")

//query for all records in the Client Master Table
//Select specific Fields from that table, sort by Client Family then by Client
const queryResult = await table.selectRecordsAsync({fields: [fldClient,fldClientFamily],
sorts: [
// sort by "Client Family" in ascending order
{field: fldClientFamily},
// then by "Client" in ascending order.
{field: fldClient},
]
});
//View Query Result
console.log({queryResult})


//Define target table for creating new records
let targetTable = base.getTable("Bank Recs");

//Loop through all records in the found records
for (let record of queryResult.records) {

await targetTable.createRecordsAsync([
{
fields: {
"Client": record.getCellValueAsString("Client"),"Client Family": record.getCellValueAsString("Client Family")
},
},

]);


}

 

Not sure of your requirements but is there a need to use a script here? Could you just use the native automation methods - Find Records in table1, then in a repeating group, use the create record action and use the data from prior Find Records step.

Not sure of your requirements but is there a need to use a script here? Could you just use the native automation methods - Find Records in table1, then in a repeating group, use the create record action and use the data from prior Find Records step.


I tried this, but the results of the find records step is limited to 100 resulting records.  My found set has about 200 - 220, so I think I need to use a script.

.... unless there is another way around the limit?

createRecordsAsync function can create 50 records at a time.
It would be a change to pass the records to be created once to an array and process 50 records at a time.

How do I pass the records to an array?

I used ChatGPT 3.5 to get it fixed.

// Script Purpose:
// 1. Select fields "Client" and "Client Family" from all records in the table "Client Master"
// 2. Using these values, create new records in the table "Bank Recs"

// Define source table
let table = base.getTable("Client Master");

// Define fields to retrieve
const fldClient = table.getField("Client");
const fldClientFamily = table.getField("Client Family");

// Query for all records in the Client Master Table
// Select specific Fields from that table, sort by Client Family then by Client
const queryResult = await table.selectRecordsAsync({
fields: [fldClient, fldClientFamily],
sorts: [
// Sort by "Client Family" in ascending order
{ field: fldClientFamily },
// Then by "Client" in ascending order.
{ field: fldClient },
],
});

// Create an array to store records to be created
const recordsToCreate = [];

// Loop through all records and add them to the array
for (let record of queryResult.records) {
recordsToCreate.push({
fields: {
"Client": record.getCellValueAsString("Client"),
"Client Family": record.getCellValueAsString("Client Family"),
},
});
}

// Define target table for creating new records
let targetTable = base.getTable("Bank Recs");

// Define batch size for creating records
const batchSize = 50;

// Function to create records in batches
async function createRecordsInBatches(records) {
for (let i = 0; i < records.length; i += batchSize) {
const batchRecords = records.slice(i, i + batchSize);
await targetTable.createRecordsAsync(batchRecords);
}
}

// Call the function to create records in batches
await createRecordsInBatches(recordsToCreate);

Does it work?

I used ChatGPT 3.5 to get it fixed.

// Script Purpose:
// 1. Select fields "Client" and "Client Family" from all records in the table "Client Master"
// 2. Using these values, create new records in the table "Bank Recs"

// Define source table
let table = base.getTable("Client Master");

// Define fields to retrieve
const fldClient = table.getField("Client");
const fldClientFamily = table.getField("Client Family");

// Query for all records in the Client Master Table
// Select specific Fields from that table, sort by Client Family then by Client
const queryResult = await table.selectRecordsAsync({
fields: [fldClient, fldClientFamily],
sorts: [
// Sort by "Client Family" in ascending order
{ field: fldClientFamily },
// Then by "Client" in ascending order.
{ field: fldClient },
],
});

// Create an array to store records to be created
const recordsToCreate = [];

// Loop through all records and add them to the array
for (let record of queryResult.records) {
recordsToCreate.push({
fields: {
"Client": record.getCellValueAsString("Client"),
"Client Family": record.getCellValueAsString("Client Family"),
},
});
}

// Define target table for creating new records
let targetTable = base.getTable("Bank Recs");

// Define batch size for creating records
const batchSize = 50;

// Function to create records in batches
async function createRecordsInBatches(records) {
for (let i = 0; i < records.length; i += batchSize) {
const batchRecords = records.slice(i, i + batchSize);
await targetTable.createRecordsAsync(batchRecords);
}
}

// Call the function to create records in batches
await createRecordsInBatches(recordsToCreate);

Does it work?


YES!!
Thank you.  (and it runs really quickly).  
Really appreciate the help

Reply


Terms & Conditions