Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 07, 2020 02:32 PM
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
Jul 20, 2020 04:15 PM
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.
Jul 31, 2020 08:49 AM
@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.
Sep 07, 2020 02:39 PM
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:
This solution works nicely, at some point I might convert to code
Sep 07, 2020 02:46 PM
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.
Sep 07, 2020 08:45 PM
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!