Attempting to modify delete duplicates script to copy one cell to the other before deleting

[Cicilia_Robison]

Hi! I’m sorry if this has been asked before and I didn’t search it properly. I’m trying to modify the “delete duplicates” script. I’m working on a sign in/sign out attendance table. I have 2 form views, one that allows the student to sign in by selecting their name, a linked field, and then entering the date/time they’re checking in, and a very similar one that they add the time they signed out. This results in 2 records, one with the time in and one with the time out. The time out cell is empty when the time in cell is full and vice versa.

What I want it to do is to delete the second entry, but before it does that, combine the two records, so that both the time in field and the time out field are filled. My thought of how to do that is when X comparison field is full, Y comparison field from the record where X comparison field is empty is copied to Y comparison field on X=full record. I’m not certain of the commands to make this happen or if it’s even possible. I’d be grateful for any help, and happy to try to clarify! Thank you!

[Justin_Barrett]

Welcome to the community, @Cicilia_Robison! :grinning_face_with_big_eyes: Were you able to get this working, or do you still need help?

[Cicilia_Robison]

I haven’t been able to figure it out yet! Thanks for checking in :slightly_smiling_face:

[Justin_Barrett]

My pleasure! I can see some potential issues with the approach that you mentioned, and I’m starting to mull over some other options for a check in/out setup that might work better (even for users on free accounts that won’t have access to the scripting app in a few months). Give me a few days to play and I’ll get back to you.

[Justin_Barrett]

@Cicilia_Robison I’m still playing with a random idea that I’m not sure is going to work after all, but I wanted to throw out a possible alternative to your current setup. Instead of using two different forms and having the user manually enter a time, use a single form and let Airtable track that for you by adding a created time field, which will record the precise time the record is made. Then add a single select with two options: “Check In” and “Check Out.” That way the user only needs to select their name from the linked table, pick the appropriate check in/out option, and submit the form. There will still be two records per student per class, but you won’t have empty fields.

The other option that I’m mulling over would do part of what you were originally seeking—merge data from separate records into a single record—but do the bulk of the work without a script. However, because Airtable doesn’t (yet) let you delete records via automation unless it’s inside a script, it would still leave you with records to clean up. I was hoping somehow to come up with a solution that would avoid the need for a script, but it’s just not doable at this time.

[kuovonne]

I think that a script would be the easiest solution to this issue (for someone comfortable writing scripts).

However, I was also intrigued by a no-script method using automations. I came up with a system that creates a new record with both the check in and check out times, and marks the original records for deletion. You can then filter for records to be deleted and easily delete them manually.

My website has the writeup and a working demo. Note that the write up does not include full formulas, and some of the formulas can be tricky. If you are interested in this system but need more assistance, you can book an appointment.