Script Creation: Identifying gaps in attendance

[Megan_Snape]

Hi all! I’m an absolute scripting begginer, can I have any support with this which would usually be pretty easy in a spreadsheet but is more challenging in a database.

I need to automatically calculate the ‘deductions’ column which is some thing like

Filter of {Client A} AND {Workshop ID}:
IF(Record No. = 1, 0,
IF({Time In 2} <= {Time Out 1} , 0,
{Time In 2} - {Time Out 1} ))

This assumes the fields are appropriately sorted, where base is ‘Business support admin’, Table is ‘Zoom Calculator’ and fields are ‘Workshop ID’,‘Client Name’,‘Time in’,‘Time out’

Client A records from Workshop ID:
Time in Time out Deductions
1 09:28 09:37 00:00
2 09:34 10:16 00:00
3 09:44 10:16 00:00
4 10:18 10:37 00:02
5 10:19 10:37 00:00
6 10:38 10:44 00:01
7 10:40 10:44 00:00

Any help would be greatly appreciated

[Mike_Pennisi]

Hi, @Megan_Snape,

The formula references fields named “Time In 2” and “Time Out 1”, but the example records have fields named “Time in” and “Time out”. Are there multiple “Time in” and “Time out” values? Or is “Time In 2” the same as “Time in” (and “Time Out 1” the same as “Time out”)?

The latter seems likely except that the example records conflict with that interpretation of the formula. All of the records have a “Time in” value that precedes the “Time out” value, so (again, assuming “Time Out 1” is the same as “Time out”) all of the “Deductions” values ought to be zero.

[Justin_Barrett]

@Megan_Snape Checking in to see if you were able to figure this out. @Mike_Pennisi asked for some further clarification, but we haven’t seen a response.

[Megan_Snape]

Hi all,

Sorry for the wait, I tried to respond but the thread seemed to be closed? Now its open I’ll clarify for anyone looking.

To solve this I had to:

• Make 2 tables, the first one being a ‘Log’ where all Zoom entries are pasted.
• Create a purpose built view in the log, sort by Workshop date, email address, Time in, Time out. Generically Auto number each record as ‘Zoom log 1’, ‘Zoom log 2’ etc.
• Link the table to itself, offset by 1. So ‘Zoom Log 1’ links to ‘Zoom log 2’.
• Lookup the linked Time In - This is the ‘Reentry time’ and email address
• Set up a formula so that if the email matches, compare the Leave time to the Reentry time, if it crosses over put 0, otherwise log a deduction. e.g. client left at 9:50 but returned at 9:45 then no reductions are necessary, but if they returned at 9:52 I calculate a deduction of 2 minutes.
• Link to 2nd table - the Primary table
• Use mixture of Max / Min / Sum rollups to pull the Minimum start time, Maximum end time and Total Deductions into the Primary Table.

This solution works nicely, at some point I might convert to code

[Megan_Snape]

Hi Mike,

Solved in the short term but wold love to know if you have any other brainwaves regarding how to script this. Again, sorry for the delay as I couldnt reply and then work took over!

So that list is all for one client in the same session where one record leads into the other. Zoom has recorded that the client entered at 09:28, left at 09:37, reentered at 9:34, left at 10:16, reentered at 09:44 etc.

I have no idea why? maybe using multiple devices?

I am trying to find any gaps between the leaving and reentry times - overlap is not a problem. and then as per my response below summarise the Minimum entry time, Maximum leave time and any deductions.

[Justin_Barrett]

Thanks for the update, @Megan_Snape. I managed to figure out the pattern you were going for after a few read-throughs. While the solution you came up with will definitely work, a script would save you a lot of time and effort. You could get those results in a matter of seconds, without the need to link records, but the script would take some time to build. If you would like to discuss options, message me directly and we’ll go from there!