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