May 05, 2023 01:43 AM - edited May 05, 2023 01:54 AM
I use the script below to retrieve the latest date from all records in table 'personal expenses', and want to insert this value to all records in table 'personal budget' (this table is in the same base).
The script works if I update the records in the same table 'personal expenses', but not when updating the other table 'personal budget'. In that case I receive this error:
j: Error: Failed hyperAssert: undefined
at main on line 33
Can someone help me with debugging? I'm in script noob and assume there's a missing link between selecting the records in tablePersonalExpenses and updating the records in tablePersonalBudget. Thanks in advance!
// Replace "Table Name" with the name of your table
let tablePersonalExpenses = base.getTable("personal expenses");
let tablePersonalBudget = base.getTable("personal budget");
console.log(tablePersonalExpenses)
console.log(tablePersonalBudget)
// Replace "Date" with the name of the date field you want to find the latest date of
let query = await tablePersonalExpenses.selectRecordsAsync({
fields: ["Date"],
sorts: [{field: "Date", direction: "desc"}],
maxRecords: 1
});
// Get the latest date value
let latestDate = query.records[0].getCellValue("Date");
console.log(latestDate)
// Update the value of field "" in all records with the highest value
let updateRecords = query.records.map(function(record) {
return {
id: record.id,
fields: {
"Latest Import Date": latestDate
}
};
});
// Update records - Only up to 50 updates are allowed at one time, so do it in batches
while (updateRecords.length > 0) {
await tablePersonalBudget.updateRecordsAsync(updateRecords.slice(0, 50));
updateRecords = updateRecords.slice(50);
}
Solved! Go to Solution.
May 05, 2023 02:25 AM - edited May 05, 2023 02:34 AM
Update: after some trial and error I indeed found out I forgot something: to query the records in the personal budget to update. This is the final code which works:
// Select the tables
let tablePersonalExpenses = base.getTable("personal expenses");
let tablePersonalBudget = base.getTable("personal budget");
// Query the records in personal expenses table for the dates and sort them descending
let query = await tablePersonalExpenses.selectRecordsAsync({
fields: ["Date"],
sorts: [{field: "Date", direction: "desc"}],
maxRecords: 1
});
// Get the latest date value (= 1 record in descending sorted order)
let latestDate = query.records[0].getCellValue("Date");
// Print the latest date
console.log("Latest import date: ", latestDate)
// Query the records in personal budget table for the "Latest Import Date" field
let tablePersonalBudgetQuery = await tablePersonalBudget.selectRecordsAsync({
fields: ["Latest Import Date"]
});
// Update the value of field "Latest Import Data" in all queried records with the highest value
let updateRecords = tablePersonalBudgetQuery.records.map(function(record) {
return {
id: record.id,
fields: {
"Latest Import Date": latestDate
}
};
});
// Update records - Only up to 50 updates are allowed at one time, so do it in batches
while (updateRecords.length > 0) {
await tablePersonalBudget.updateRecordsAsync(updateRecords.slice(0, 50));
updateRecords = updateRecords.slice(50);
}
May 05, 2023 02:25 AM - edited May 05, 2023 02:34 AM
Update: after some trial and error I indeed found out I forgot something: to query the records in the personal budget to update. This is the final code which works:
// Select the tables
let tablePersonalExpenses = base.getTable("personal expenses");
let tablePersonalBudget = base.getTable("personal budget");
// Query the records in personal expenses table for the dates and sort them descending
let query = await tablePersonalExpenses.selectRecordsAsync({
fields: ["Date"],
sorts: [{field: "Date", direction: "desc"}],
maxRecords: 1
});
// Get the latest date value (= 1 record in descending sorted order)
let latestDate = query.records[0].getCellValue("Date");
// Print the latest date
console.log("Latest import date: ", latestDate)
// Query the records in personal budget table for the "Latest Import Date" field
let tablePersonalBudgetQuery = await tablePersonalBudget.selectRecordsAsync({
fields: ["Latest Import Date"]
});
// Update the value of field "Latest Import Data" in all queried records with the highest value
let updateRecords = tablePersonalBudgetQuery.records.map(function(record) {
return {
id: record.id,
fields: {
"Latest Import Date": latestDate
}
};
});
// Update records - Only up to 50 updates are allowed at one time, so do it in batches
while (updateRecords.length > 0) {
await tablePersonalBudget.updateRecordsAsync(updateRecords.slice(0, 50));
updateRecords = updateRecords.slice(50);
}
May 05, 2023 07:56 AM
Thank you for sharing your script writing experience and the solution.
Out of curiosity, why are you using the maxRecords parameter in your script?
May 05, 2023 09:45 AM - edited May 08, 2023 04:09 AM
You're welcome and thanks! Regarding your question:
@kuovonne wrote:Thank you for sharing your script writing experience and the solution.
Out of curiosity, why are you using the maxRecords parameter in your script?
To be honest this script is a result of various online explorations and copy/pasting, this maxRecords was part of such a paste. Looking at it now, it indeed has no real function for the actual result. Maybe there's a bit value in terms of performance? I've removed it from my script.