How to return availability based on "hours/day" (number date type) and date ranges?

I am trying to build a tool for resource (team member) planning and have built the core functionality of the app - assigned team members to projects. However, now, I would like to be able to return team members’ availability (8 hours/day - committed hours/day for a given date range).

Here is how I have the database structure currently set up: Each team member is a thing within the “Team_Member” data type. Each “Team_Member” data type also has a list of “Schedules”. “Schedule” data type has an assigned “team_member” (Team_Member data type), “hours_per_day” (number), and then four (4) options for dates that I have tried to work with to see which works best for what I am trying to accomplish - “date_range”, which is the date range the schedule is attributed to; “date_range_list”, which is a list of each individual date the schedule is attributed to, and “start date”, which is the first date in the range, and “end_date”, which is the last date in the range.


What I am trying to do is be able to return how many hours_per_day a “Team_Member” data type has remaining and during which date range.

As an example, let’s say “Team_Member” #1 has two (2) “Schedules” - Schedule #1 has 2 hours/day from July 1, 2022, to August 31, 2022, and Schedule #2 has 5 hours/day from August 15, 2022, to October 31, 2022.

I would like to be able to display that Team_Member #1 has 6 hours/day of availability (8hrs/day base - 2 hours/day on Schedule #1) from July 1, 2022 to August 15, 2022; 1 hour/day of availability from August 15, 2022 to August 31, 2022 (8hrs/day base - 2 hours/day on Schedule #1 - 5 hours/day on Schedule #2); and 3 hours/day of availability from September 1, 2022 to October 31, 2022 (8hrs/day base - 5 hours/day on Schedule #2).

I know this is a complicated problem, but I am certain there is a solution - albeit likely as complicated or more than the problem itself haha. I would appreciate any help, guidance, or recommendations anyone can provide. Thanks!

IF you want the easiest solution - check out the TopCal plugin :slight_smile:

If you want to go a more complicated but non paid plugin route contact @boston85719 …he has ready made templates you can tweak or he can coach you through building it in your own app.


Thank you for the response!

I’ll check out TopCal and see if it can do everything I’m looking for. If not, I’ll reach out to @boston85719 and go the complicated route.

Thanks @equibodyapp for the shout out.

@brettpellegrin What you are trying to do is complicated in itself regardless of which approach you take of using a plugin or purchasing a ready made template. My templates by the way do not provide this function, so would not be a ready to go out of the box solution for this.

From my understanding of the TopCal plugin, the developer @gf_wolfer has put onto the showcase page some ideas on how to achieve this functionality. Based on your description of what you’d like to see in regards to a total number of hours available per day based on a range ( 3 hours a day from Sept 1 - Oct 31 ) would not be possible unless every single day between those ranges the user only has 3 hours available.

You could set things up to include a group by operator to group by the hours available to show the single dates that have the same number of hours available.

No matter what, it is a complex search that will take time to return the results. Not something I would recommend for any type of app searching for profiles to display based on a user looking to make an appointment with a service provider, but might have an acceptable load time for an administrator using the function for an app used by an organization.

Making it faster would require having a data structure such as a data type that has a field of hours available (just a number), another field for date (single date) and another field for team member (single member)…you would need functions to keep this separate data type synced with the team members actual bookings/availability for a specific date, and functions to populate the database with new entries as time passes (because you wouldn’t want to create entries for every day of the year — not an issue if you consider you could easily restrict the time range users can search for; ie: no more than 3 months into the future).

Then with this data structure you can search for the data type that is where you hold these values. Constrain the search by the date fields. Then in the repeating group you wish to display the members you can reference the datasource ‘each items member’ so that the data type of member and the datasource (searching for the data type that holds the date, hours available and member) are acceptable and result in blue dynamic expressions for the datasource.

I did something similar in another app for matching based on an point values and it improved the speed of the search significantly…just took a bit more work for the other features to support it.

1 Like

This topic was automatically closed after 70 days. New replies are no longer allowed.