I am looking to build an internal portal with the ability for employees to book off annual leave and for Glide tables to count how many working days an employee selects - so the amount of days selected between a Start and End date (date fields).
However, as I understand it this may be a little challenging to build as the current functionality for the ‘date difference column’ can only count the total number of days between two dates, and not working days (Monday to Friday). Does anyone have any ideas or potential workarounds how I could approach this or a way to facilitate this in Glide please?
Thanks very much for this. Extremely helpful. My next task is to establish how I can filter a long list of approved annual leave days, this is a column listing how many days each annual leave request is. I am keen to break down how many approved annual leave days are attributed to each employee. Each row is attributed to a First Name so should I do this from an IF THEN ELSE function? Or would there be a better way?
Assuming you have a list of employees and a list of approved days, with a column storing that user’s ID or email, you can make a multiple relation between the two tables and use a rollup column to sum the number of approved days.
Thanks so much for all your help. I can’t seem to get the Roll Up column so that it produces a total amount per person. When I am selecting ‘Sum’ it is adding up all approved annual leave (from all employees) rather than just one person. Perhaps I am doing something wrong?
If you created the multiple relation that @ThinhDinh mentioned to link the user in the employee table to the user in the approved days table, then your rollup column should be using the relation to create the sum. It should not be directly using the approved days table.
A lot of things to unpack there, so I’ll start with two odd things I noticed.
In the beginning of your video, you showed a Lookup column that returns all users from the Staff table. What is the purpose bof that Lookup since each row in the Annual Leave table seems to to be specific to only one user.
Second thing I noticed later in your video is an IF column that compares the user profile email back to itself. It’s like comparing 1 to 1. 100% of the time it will match because you are comparing the email to itself. If your goal is to only return a number if the signed in user email matches the email in the leave table, then you are comparing the wrong columns. But I also think this is the wrong approach because I assume to want to see everyone’s leave in the staff table. Not just your own.
With that said, why is it necessary to calculate the sum in the Leave table if you just need it in the Staff table? Yes, you can still keep the logic to check if the leave is approved or not in the Leave table, but wouldn’t it be easier to do the sum in the Staff table?
In the Staff table, create a relation that look inks the email in that row to the email in the Leave table.
Then add a Rollup column in the Staff table that uses that relation and calculates the sum only for that matching user.
That way you will get sum of approved annual leave for each individual staff member.
Hi Jeff, this approach worked (your bottom bullet points) thank you so much. I now realise my error when trying to work this too (other than what you had raised). When I was relating a table, I was not then using the Rel table to Roll up from there (I didn’t know the logic worked this).
Thanks everyone - extremely helpful and kind to spare the time to help.