# Find work booked per person per day

Hey guys,

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:

1. 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)

2. 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

Cheers,
James

Thinking out aloud here, so forgive me if I don’t get this exactly correct.

• I would start by adding the selected date to the Staff table as a Single Value
• Then convert it to an integer using a Math column:
``````Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)
``````
• Create a column in your Bookings table that does the same conversion
• Create a template column in your Staff table that combines the Staff member RowID with the converted Date
• Create a similar column in your Bookings table
• Create a multiple relation column in your Staff table that matches the two template columns.
• Do a rollup through that column to determine each Staff members capacity
• Then use that column to filter the Staff list (ie. only those that have capacity)

Similar sort of approach for this one…

• Write the RowID of the selected Staff member into your Dates table as a Single Value
• Convert the Dates to integers as per the other method
• Create a template that combines the Staff member RowID and the converted date
• Create a multiple relation that matches the template column with the similar template column in your Bookings table.
• Create a rollup to determine the capacity for each Date
• Use that to filter the list of Dates
1 Like

Cool cool cool.

This is the right idea but with some issues.

For this method…

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?

Thanks once again for your time Darren

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.

1 Like

Yep, that’s right.

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…

20230424 0.25,
20230425 0.50,
20230426 0.50,
20230427 0.50,
20230428 0.50,
20230425 0.50,
20230426 0.50,
20230427 0.50,
20230424 0.50

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:

20230424 0.75,
20230425 1.00,
20230426 1.00,
20230427 1.00,
20230428 0.50

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.

yeah, the Chat-GPT javascript is a common technique for this sort of problem. And yes, you could adapt that for use in the Glide JavaScript column.

Could you paste here the same input that you gave to Chat-GPT?

That would be amazing. Initial input below:

I have an array of string values. Each string has three components, separated by a comma. An example of the string values is below:

RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.25
RHEo76fSQreIZbHZwK5wbQ, 20230425, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230426, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230427, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230428, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.00
RHEo76fSQreIZbHZwK5wbQ, 20230425, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230426, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230427, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230428, 0.50
RHEo76fSQreIZbHZwK5wbQ, 20230424, 0.50

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

oh, okay. That changes things then - good thing you told me that

Okay, so try this.

Given this input data:

``````20230424, 0.25
20230425, 0.50
20230426, 0.50
20230427, 0.50
20230428, 0.50
20230424, 0.00
20230425, 0.50
20230426, 0.50
20230427, 0.50
20230428, 0.50
20230424, 0.50
``````

Then using this code in a JavaScript column:

``````let summary = {};
let records = p1.split(/\r?\n/);
while (records.length > 0) {
let [date, effort] = records.shift().split(', ');
if (summary.hasOwnProperty(date)) {
summary[date] += +effort;
}
else {
summary[date] = +effort;
}
}
return Object.entries(summary).map(([key, value]) => key + ", " + value).join('\n');
``````

You should get this result:

``````20230424, 0.75
20230425, 1
20230426, 1
20230427, 1
20230428, 1
``````
1 Like

Does the JS column handle an array as p1?

No, it needs to be a string. A joined list using a new line to separate each record.

1 Like

Nice! That seems to work well. I now have:

20230424, 0.75
20230425, 1
20230426, 1
20230427, 1
20230428, 1

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?

We could adjust the JavaScript so that it just returns a list of dates where the effort is >= 1.
Give me a few minutes and I’ll make that adjustment.

``````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(',');
``````

Which returns:

``````20230425,20230426,20230427,20230428
``````
1 Like

@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.
3 Likes

Lovely, I owe you a couple of beers if you’re ever in Perth!

Seems like when things start getting a complex you can save plenty of rows and columns with some code. The data editor definitely ends up cleaner

I’m sure there are other areas of this app where some JS would tidy things up quite a bit.

I’ll work on this further over the next couple of days and tie it in nicely with the UI. I reckon we’re home and hosed