Amongst others, I have a table of dynamic dates (now + 90 days), a table of staff and a table of existing field bookings.
I am trying to determine when each staff member is at maximum capacity for each day, and at that point would no longer allow bookings to be made for that staff member. Each booking consumes between 0 and 1 “capacity units” if you like and there is a relation between each day in the dates table and all existing bookings for that day. Each staff member should not exceed a capacity of 1 per day.
Ultimately I’d like to be able to search for staff to book in two different ways:
By date and then by staff (ie show the user a list of dates where one or more staff is available to book, then once the date is selected, only show the staff that haven’t exceeded capacity on that day)
By staff and then by date (ie show the user a list of staff, then once the staff is selected, only show the dates on which they do not exceed capacity)
Ideally I’d like to be able to set it up so that bookings can be made in a regular form screen or container (to save updates).
Let me know if I can provide any extra details to help clarify.
Here’s hoping that somebody has experience with this or a similar problem
How would the user know which dates have any availability before selecting a date? Often dates will be fully booked with a lead time of around a week or two. It wouldn’t be ideal to click around until you find a date with available capacity. Most of the time, bookings would be made on the next available date, so it would be nice only to show available dates initially.
Side note - adding as a single value would use an update each time the user selects a date (i.e. custom form). Ideally, I’d like a method to run through a regular form screen or form container - to do this would require computing daily staff capacity for each possible booking date (now + 90) before selecting one, right?
mmm, good points. To be honest, it was late when I replied last night and I didn’t give it a whole lot of thought.
This is tricky. I assume that if any staff member has availability on any given date, then that date should be available for booking, yes?
I’m thinking that you might be able to apply a bit of reverse logic using arrays of dates. That is, get a list of dates for each staff member where they are fully booked (not available), and then count how many of each you have for each of the 90 days. Then subtract that number from the total number of staff. For any dates that have a positive remainder, you can mark those as available dates. I have a fuzzy idea in my head of how this could work, but it’s a bit difficult to describe. Any chance you can give me some sample data to play around with?
I’m not sure if this will be possible, it’s something I’d need to experiment with.
I like this train of thought. I’m messing around with a template column on the BOOKINGS table, combining “Date & Effort Req’d”. Then, there’s a multiple relation to another table (either the STAFF table or a separate one where each row has a StaffID) to separate bookings by staff member. On the STAFF/helper table, using a lookup column, we get an array of Date & Effort Req’d, like below for StaffID RHEo76fSQreIZbHZwK5wbQ…
Ideally, there is some magic (maybe through array functions or javascript) that can sum the effort required per staff member per day, returning the summary below:
We now know whether each staff member is available on any given day. Above, RHEo76fSQreIZbHZwK5wbQ is available on April 24 and April 28.
If I can then extract only the dates where the effort value is >= 1.00 for that staff member, there can be a multiple relation on the DATES table to pull through the StaffID of anyone unavailable on any given day. I specified >= 1.00 because sometimes a manager may manually add extra bookings that take you over 1.00 which would be the usual threshold
Also on the DATES table, I have some array calcs happening where each date (row) begins with an array of all staff and then step by step remove arrays of staff who are unavailable for one reason or another (e.g. on annual leave, or fully booked in this case). I am left with a list of available staff which is how I filter the staff and date selection in the app.
I asked ChatGPT how to sum the effort req’d per staff member per day and got the following back.
I don’t really understand the Javascript ChatGPT output and don’t know if this is possible in Glide’s JS column (let alone if this is an efficient way to go about it).
I would expect to have around 400 bookings per month, and so calculating this for the entire bookable period (90 days) means there could potentially be around 1200 entries being processed. Hopefully, this would be okay performance-wise.
The first component is a unique identifier for a staff member, of which there could be many.
The second component is a date, formatted YYYYMMDD.
The third component is a number ranging from 0.00 to 1.00, which indicates effort required to complete a job.
Using javascript, I need to return an array of values that sums the effort required per staff member per day. For example, “RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.50” would be summed with “RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.25” and “RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.00” to return “RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.75”. How can I do this?
I should clarify that the STAFF ID is no longer required in that JavaScript as the table will already be one row per staff id. So it’s just the date and effort req’d
The last piece of the puzzle is to extract only the dates where the effort value is >= 1.00 for that staff member. Then, there can be a multiple relation on the DATES table to pull through the StaffID of anyone at full capacity on any given day. I specified >= 1.00 because sometimes a manager may manually add extra bookings that take you over 1.00, which would be the usual threshold
Using a template column, I currently have a brute force way where I replace each possible increment of 0.25 between 1.00 and 5.00 with nothing. There is undoubtedly a better way
I was playing around with regex in the extract multiple matching text column but couldn’t get it working - is this an option?
let summary = {};
let records = p1.split(/\r?\n/);
let dates = [];
while (records.length > 0) {
let [date, effort] = records.shift().split(', ');
if (summary.hasOwnProperty(date)) {
summary[date] += +effort;
}
else {
summary[date] = +effort;
}
}
for (var date in summary) {
if (summary[date] >= 1) {
dates.push(date);
}
}
return dates.join(',');
@james - now that we’re using JavaScript, I think you can simplify this significantly and get rid of that helper table. Consider the following:
In your Bookings table, I understand you already have a template column that combines the Date and Effort, ie 20230424 0.75
Presumably you don’t allow bookings in the past, so we only need to consider future dates. Therefore, add an if-then-else column:
– If Date is after today, then template column.
Now back in your Users/Staff table you need a multiple relation that matches UserID with UserID in your Bookings table (you probably already have that).
Create a Joined List through that relation, taking the output of the if-then-else column and using a new line separator.
Now feed that Joined List column into that last JavaScript snippet that I gave you.
Use a Split Text column on the output of the JavaScript column. This will give you an array of (future) dates for each person where they are not available.
Finally, create a relation that matches this array with the date selected in your Bookings form (after converting it to YYYYMMDD format).
Then you use the state of that relation to filter the Staff list. If it’s empty, that means the Staff member has availability on the selected date, so include them in the list.
