Help

Re: Script - Timing out after 30 seconds. Help to make more efficient

Solved
Jump to Solution
1894 1
cancel
Showing results for 
Search instead for 
Did you mean: 
rmc1977
5 - Automation Enthusiast
5 - Automation Enthusiast

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")
        },
    },
    
  ]);  
  

}

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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?

See Solution in Thread

6 Replies 6
corb1
6 - Interface Innovator
6 - Interface Innovator

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.

rmc1977
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

Sho
11 - Venus
11 - Venus

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.

rmc1977
5 - Automation Enthusiast
5 - Automation Enthusiast

How do I pass the records to an array?

Sho
11 - Venus
11 - Venus

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?

rmc1977
5 - Automation Enthusiast
5 - Automation Enthusiast

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