hai my name basi , from india in my airtable i trying to make summary table for purpose of make an interphase,
Our airtable have a two table name as, 'JOB NO' and 'SUMMERY copy'
and in 'JOB NO' have 3 field
which has ,
'EMPLOYEE' - employee name
'DATE2' - it formula field with like this '9/8/2023 12:00am' in date phrame
'DUTY TIME' - duration fied
so i am try convert the 'JOB NO' data in to another table like this
if TABLE 'JOB NO' DATA LIKE THIS :-
BASI--11/07/2023--2:0
SKIL--12/07/2023--5:0
BASI--11/07/2023--1:0
BASI--12/07/2023--2:0
SKIL--13/07/2023--5:0
'SUMMERY copy' need BE like this :-
BASI--11/07/2023--3:0
SKIL--12/07/2023--5:0
BASI--12/07/2023--2:0
SKIL--13/07/2023--5:0
employee name need in 'EMPLOYEE' in table 'SUMMERY copy'
date need be in 'DATE' ( date field ) in table 'SUMMERY copy'
sum of duty time from JOB NO in 'SUM'( duration field) in table 'SUMMERY copy'
this script made by chatgpt
let jobsTable = base.getTable("JOB NO copy");
let summaryTable = base.getTable("SUMMERY copy");
let jobsRecords = await jobsTable.selectRecordsAsync();
let summaryRecords = await summaryTable.selectRecordsAsync();
// Group records by employee name and date
let groups = {};
for (let record of jobsRecords.records) {
let employee = record.getCellValue("EMPLOYEE");
let date = record.getCellValue("DATE2");
let dutyTime = record.getCellValue("DUTY TIME");
if (employee && date && dutyTime) {
let key = `${employee}-${date}`;
if (!groups[key]) {
groups[key] = {
employee: employee,
date: date,
sum: 0
};
}
groups[key].sum += dutyTime;
}
}
// Update summary records
for (let key in groups) {
let group = groups[key];
let summaryRecord = summaryRecords.records.find(record => record.getCellValue("EMPLOYEE") == group.employee && record.getCellValue("DATE") == group.date);
if (summaryRecord) {
await summaryTable.updateRecordAsync(summaryRecord, {
"SUM": group.sum
});
} else {
await summaryTable.createRecordAsync({
"EMPLOYEE": group.employee,
"DATE": group.date,
"SUM": group.sum
});
}
}
Due to the 5000 above record limit, the first run won't fully update the record, so I need to adjust the script so that the second run updated data that did't updated the first time. This implies that in each run, updated data that had not been updated previously . any can help with this ?
