Feels like 4th grade math... but it isn't

I have been trying to get how to think about the following problem for months now. So I decided I need help to learn how to look at the problem correctly.

I built a "simple" database (let's say simpler than what I deal with in real life) from scratch with fake everything.

The "simple" question I am trying to answer is the following:

How many workdays each activity were effectively held

The rules are the following:

  • If a teacher is absent, activity doesn't occur on that day.
  • If an installation is unavailable, activity doesn't occur on that day (even if teacher is present)
  • If a teacher is absent installation is deemed unavailable (cuz he has the keys)
  • Installations unavailibility and Teachers absence may overlap

We only consider workdays. For this, a table exists which has for each unique date a workday number, dates that are holidays and weekends will repeat the last business day date workday number until the next business day date. (See file; much easier to understand by looking at it!)

Durations are calculated by substracting smallest workday number from biggest workday number.

My idea was to deduct the number of days of installation unavailibility and teacher absence from the Activity duration span. However, since some installation unavailibilities and teacher's absence overlap, I can't just add these together.

Find all records of Abs status (1 record is a span of days; start of status, end of status) for Teacher (TeacherAttendance) that teaches the activity
Find all records of Unavail. status (1 record is a span of days; start of status, end of status) for Installation (InstallationAvailability) that service the activity

I conceptualized what needs to be done, but I can't figure out how to do it in FileMaker so that I have a field called Activity Duration Net with the info for the activity in the activity table...

Do this for each activity:
(If teacher absence span record starts within Activity Span, list all WorkDays included in [teacher absence record] span.
if Teacher Abs StartDAte >= Activity StartDate and < Activity EndDate, List all WorkDay numbers between Teacher Abs StartDate and EndDate

(If installation unavailibility span record starts within Activity Span, list all Work Days included in [installation unavailibility record]span.
if Installation Unav. StartDate >= Activity StartDAte and < Activity EndDate List all WorkDay numbers between Installation Unav. StartDate and EndDAte

(now add together the list of workday numbers included in the Teacher absence span and the list of workday numbers incuded in the Installation unavailibility span. Remove duplicate number. Count the unique values: that is the number of days an activity could not be held)
Count (keepUniqueValues(Concatenate List TeachersAbsWorkDaysNumbers and List Installation Unavail. WorkDayNumbers)

Find workday number for Activity StartDate
Find workday number for Activity EndDate
End Number - Start Number = Activity Duration
Activity Duration - Number of Days that Activity could not be held = Activity Duration Net.

TestDaysDates.fmp12 (292 KB)

I would add a table Activity_Date which would hold one record per Activity per Date.

Relate this back to the InstallationsStatus table by Activity ID and Date (using <= and >= for StatusStartDate and StatusEndDate to match the date range)

Then put calcuations in the Activity_Date table:

  Activity_Date::cIsActiveToday =  Case(
    InstallationStatus::Status = "Available"; 1; 
    InstallationStatus::Status = "Unavailable"; 0; 
    "")

Then, you can easily run a report to figure out the number of days, using summary fields, e.g.

Run Activity Report:

  Go To Layout(Activity_Date)
  Perform find ( [ whatever activities and date ranges you want] )
  Sort (by activity, date)

Summary fields:
sNumberOfDaysHeld = Sum(Activity_Date::cIsActiveToday)

1 Like

How would you remove the non workdays ?

I do not have the dates of availibility for the the status: just the dates where the status starts and when the status ends. Statuses are spans (like 5 days, 6 days etc) in the following manner:

Start date = 2022-04-05------> in the workdays table, the workday number for that row is 5
End date = 2022-04-09-------->in the workdays table, the workday number for that row is 7

Span = 7-5 = 2 workdays (+1 for end day) = 3

If I get a list of all the dates between 2022-04-05 and 2022-04-09, it gives me
Date..............Workday#, Weekday
|2022-04-05|5|............Friday
|2022-04-06|5|............Saturday
|2022-04-07|5|............Sunday
|2022-04-08|6|............Monday
|2022-04-09|7|............Tuesday

List of Workdays in the span: 5, 5, 5, 6, 7
count of Unique workdays in the span: 3 <------ The actual duration of status in workdays.

Your solution tries to calculate based on the start and end days. As you've discovered, this isn't easy.

My solution lets each date + activity combination calculate its own availability, so each record has either 1, 0, or "".

Then, you can simply do a summary across these to get the totals.

You could handle non-workdays two ways:

  • don't put those dates in the Activity_Date table at all (e.g. the table would only hold records for Mon-Fri, excluding Sat-Sun)

  • each Activity_Date record would calculate it's own status, like this:

  Activity_Date::cIsActiveToday =  
   Case(
    DayOfWeek(Date) = 1 ; "0"; // Sunday is not a work day
    DayOfWeek(Date) = 7 ; "0"; // Saturday is not a work day
    InstallationStatus::Status = "Available"; 1; 
    InstallationStatus::Status = "Unavailable"; 0; 
    "")

Here's a picture and the demo file:

Download:

TestDaysDatesV3.fmp12 (512 KB)

I like your solution and it is the path I would have naturally taken if I was not dealing with someone else solution which data architecture is the way I described. The workdays are mapped in a table as i described before. The mapping includes statutory holidays, company's designated holidays, weekends, etc. So it is not something I can do without.

I think you can do both - add this solution on top of the existing data arcitecture, then simply write a script which populates the Activity_Date table when you need to run a report. You don't need to change any other parts of the solution.

Edit to add: my solution only handles when the Installation is not avaialable, but you can extend this idea for other situations.

If you need to figure out when the Teacher is absent, you can set up a simlar relationship between the Activity_Date table to the TeacherAttendance table, etc.

the logic gets a little messy, but I think it's tractable:

cTeacherAvailable = [...]

cInstallationAvailable= 
Case(
    DayOfWeek(Date) = 1 ; 0; // Sunday is not a work day
    DayOfWeek(Date) = 7 ; 0; // Saturday is not a work day
    InstallationStatus::Status = "Available"; 1; 
    InstallationStatus::Status = "Unavailable"; 0; 
)

cIsActiveToday =  
   Case(
    cTeacherIsAvailable = 0; 0 ; // no teacher? no activity
    cInstallationAvailable   // otherwise, if a teacher is available, use the installation's availability
    )

The demo you attached seems to be the original file... :slightly_smiling_face:

Sorry, try again now with V3.

The way I approached this is something I've learned both from FileMaker and from Object-Oriented-Programming.

The key thing is to figure out what is the natural "unit" or "object" of analysis, and then put all the logic inside this object.

In this case, the natural unit is "one activity on a particular calendar date" - so by creating a table with one row per activity per date, you have a nice platform to work from, and can put all the relations and calculations there.

I'm sure there are other approaches, but I like this approach as it's fairly easy to debug once you get it set up, since you can see all the parts that are making up the calculations...

2 Likes