Hey Wingo, give the following script a shot. You’ll need to install a scripting app, update the table name, employee field names and such, but this should work.
I’ve got it set up in this base so you can see an example of a full base set up + the script
The idea is that you just input the total number of clock in / outs you have, and, for each one of those, the script will create a new record in the new table, and it’ll do so for each employee
Let me know if there are any errors and I’ll fix them up
After it gets converted to shifts, you should be able to get the data you’re looking for pretty easily by converting the employees to a linked row I reckon; let me know if you wanna discuss any of that too
//Fill in your data here
let dataTable = base.getTable([NAME OF THE TABLE THAT CONTAINS YOUR CURRENT DATA])
let tableToUpdate = base.getTable([NAME OF THE TABLE THAT YOU ARE PUTTING THE NEW SHIFT DATA INTO])
let employeeFieldName = [NAME OF THE FIELD THAT CONTAINS THE EMPLOYEE INFORMATION FOR BOTH TABLES]
let numClockIns = [THE HIGHEST NUMBER OF CLOCK INS]
// e.g. if the larget clock in field you have is `CLOCK IN - 1000`, then put the number 1000 here
// Example set up
// let dataTable = base.getTable('Data Table')
// let tableToUpdate = base.getTable('Shifts')
// let employeeFieldName = 'Employee'
// let numClockIns = 2
//=====
let fieldsToGet = [employeeFieldName]
for (let i = 0; i < numClockIns; i++){
let clockInNumber = i + 1;
fieldsToGet.push('Clock in - ' + clockInNumber)
fieldsToGet.push('Clock out - ' + clockInNumber)
}
let query = await dataTable.selectRecordsAsync({
fields: fieldsToGet
})
let recordsToCreate = new Array
for (let record of query.records){
for (let i = 0; i < numClockIns; i++){
let recordToCreate = { fields: {}}
let clockInNumber = i + 1;
recordToCreate.fields[employeeFieldName] = record.getCellValueAsString(employeeFieldName)
recordToCreate.fields['Clock in'] = record.getCellValue('Clock in - ' + clockInNumber)
recordToCreate.fields['Clock out'] = record.getCellValue('Clock out - ' + clockInNumber)
recordsToCreate.push(recordToCreate)
}
}
while (recordsToCreate.length > 0) {
await tableToUpdate.createRecordsAsync(recordsToCreate.slice(0, 50));
recordsToCreate = recordsToCreate.slice(50);
}
