Skip to main content

Hi

I have a simple script I run each night, which simply attempts to tick a box in about 100 records, which then drives an automation to fire off some reports on activity that day (1 per user).

 
Unfortunately it never completes, it tells me it has failed. It works for many records, but not all. I have similar scripts for other reports.
 
I'm using a script because it fires at a specific time. Any ideas how I can improve this please so that it will process all before timing out. The table in question only has 100 records or so.
 
Thanks

 

let table = base.getTable("Users");
let view = table.getView("Users for Daily Report");
let query = await view.selectRecordsAsync();
 
for (let record of query.records) {
table.updateRecordAsync(record, {
"Trigger Daily User Report": true
})
}

It's probably because you're updating every record one by one.  Try using `updateRecordsAsync`instead

Alternatively, you could just output the record IDs and use a repeating group on that list with an "Update Record" action

You can use some other technique.
For example, you can link all table to a new single-record table with checkbox, and add it as lookup

 


Or you can use MOD(value(record_id()),15) as a pseudo-random value to add minutes from 0 to 15.
 Example: field 'Report run time'

 

DATEADD(TODAY(),425+MOD(VALUE(RECORD_ID()),15),'minutes')

 

Edit constant (425 in this example) to adjust time range according to your desired time. For timezone before UTC, add 60*hours to reach midnight.

field 'Minutes left'

 

IF(DATETIME_DIFF({Report run time},NOW(),'minutes')=0,'generating report..',
IF(DATETIME_DIFF({Report run time},NOW(),'minutes')<0,'report done',
DATETIME_DIFF({Report run time},NOW(),'minutes')
)
)

 

Add automation triggered on {Minutes left} contains 'report'
it will 'recharge' each new day, when 'report done' will be changed to a number

But if you want to continue with scripting,
You should use await with async operatiors, and multiple update operator table.updateRecordsAsync(array of updates).
You need to break that array into batches no more than 50 records in a batch. 
finally it might be:

 

let table = base.getTable('Users')
let view = table.getView("Users for Daily Report");
let query = await view.selectRecordsAsync()
let fields = {"Trigger Daily User Report": true}
let updates = query.records.map( ({id}) => ({id,fields}) )
while (updates.length) await table.updateRecordsAsync(updates.splice(0,50))

 


Note: when transforming {objects} in map operator, or other with arrow-function, they must be enclosed in ({round brackets}), so operator will know it's not a beginning of
{
  function block; 
}

You can use some other technique.
For example, you can link all table to a new single-record table with checkbox, and add it as lookup

 


Or you can use MOD(value(record_id()),15) as a pseudo-random value to add minutes from 0 to 15.
 Example: field 'Report run time'

 

DATEADD(TODAY(),425+MOD(VALUE(RECORD_ID()),15),'minutes')

 

Edit constant (425 in this example) to adjust time range according to your desired time. For timezone before UTC, add 60*hours to reach midnight.

field 'Minutes left'

 

IF(DATETIME_DIFF({Report run time},NOW(),'minutes')=0,'generating report..',
IF(DATETIME_DIFF({Report run time},NOW(),'minutes')<0,'report done',
DATETIME_DIFF({Report run time},NOW(),'minutes')
)
)

 

Add automation triggered on {Minutes left} contains 'report'
it will 'recharge' each new day, when 'report done' will be changed to a number

But if you want to continue with scripting,
You should use await with async operatiors, and multiple update operator table.updateRecordsAsync(array of updates).
You need to break that array into batches no more than 50 records in a batch. 
finally it might be:

 

let table = base.getTable('Users')
let view = table.getView("Users for Daily Report");
let query = await view.selectRecordsAsync()
let fields = {"Trigger Daily User Report": true}
let updates = query.records.map( ({id}) => ({id,fields}) )
while (updates.length) await table.updateRecordsAsync(updates.splice(0,50))

 


Note: when transforming {objects} in map operator, or other with arrow-function, they must be enclosed in ({round brackets}), so operator will know it's not a beginning of
{
  function block; 
}


Thank you for these suggestions. I will try them.

You can use some other technique.
For example, you can link all table to a new single-record table with checkbox, and add it as lookup

 


Or you can use MOD(value(record_id()),15) as a pseudo-random value to add minutes from 0 to 15.
 Example: field 'Report run time'

 

DATEADD(TODAY(),425+MOD(VALUE(RECORD_ID()),15),'minutes')

 

Edit constant (425 in this example) to adjust time range according to your desired time. For timezone before UTC, add 60*hours to reach midnight.

field 'Minutes left'

 

IF(DATETIME_DIFF({Report run time},NOW(),'minutes')=0,'generating report..',
IF(DATETIME_DIFF({Report run time},NOW(),'minutes')<0,'report done',
DATETIME_DIFF({Report run time},NOW(),'minutes')
)
)

 

Add automation triggered on {Minutes left} contains 'report'
it will 'recharge' each new day, when 'report done' will be changed to a number

But if you want to continue with scripting,
You should use await with async operatiors, and multiple update operator table.updateRecordsAsync(array of updates).
You need to break that array into batches no more than 50 records in a batch. 
finally it might be:

 

let table = base.getTable('Users')
let view = table.getView("Users for Daily Report");
let query = await view.selectRecordsAsync()
let fields = {"Trigger Daily User Report": true}
let updates = query.records.map( ({id}) => ({id,fields}) )
while (updates.length) await table.updateRecordsAsync(updates.splice(0,50))

 


Note: when transforming {objects} in map operator, or other with arrow-function, they must be enclosed in ({round brackets}), so operator will know it's not a beginning of
{
  function block; 
}


I finally got around to implementing this and the script change you demonstrated above works perfectly.  Thank you!

Is there any downside to running the scripts as it seemed you preferred a non script approach? Using this short script triggered at a given time daily is a lot easier and less impact on the size of the db than the alternatives suggested.

Reply


Terms & Conditions