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

**URL:** <https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532>\
**Category:** Questions\
**Created:** [April 28, 2023, 7:21pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532 "2023-04-28T19:21:32Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cecile](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/cecile/32/98_2.png) [@Cecile](https://the.fmsoup.org/u/Cecile)\
**Post date:** [April 28, 2023, 7:21pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/1 "2023-04-28T19:21:32Z")

</div>

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](https://the.fmsoup.org/uploads/short-url/f5qBgYY2ZqixwIm9yhdTmqBdWD9.fmp12) (292 KB)

---

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [April 28, 2023, 7:36pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/2 "2023-04-28T19:36:36Z")

</div>

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:

```auto
  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:

```auto
  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)`

---

<div class="post-metadata">

**Author:** ![Cecile](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/cecile/32/98_2.png) [@Cecile](https://the.fmsoup.org/u/Cecile)\
**Post date:** [April 28, 2023, 7:58pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/3 "2023-04-28T19:58:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [April 28, 2023, 8:27pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/4 "2023-04-28T20:27:50Z")

</div>

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:

```auto
  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; 
    "")

```

---

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [April 28, 2023, 8:46pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/5 "2023-04-28T20:46:37Z")

</div>

Here's a picture and the demo file:

 ![image](https://canada1.discourse-cdn.com/flex030/uploads/fmsoup/original/2X/4/47dc43039517c1db2802af495f304874a5801cbd.jpeg)

Download:

[TestDaysDatesV3.fmp12](https://the.fmsoup.org/uploads/short-url/qer6AupOfBMEeICl3g2nwDuxSB5.fmp12) (512 KB)

---

<div class="post-metadata">

**Author:** ![Cecile](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/cecile/32/98_2.png) [@Cecile](https://the.fmsoup.org/u/Cecile)\
**Post date:** [April 28, 2023, 8:46pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/6 "2023-04-28T20:46:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [April 28, 2023, 8:48pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/7 "2023-04-28T20:48:55Z")

</div>

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:

```auto
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
    )

```

---

<div class="post-metadata">

**Author:** ![Cecile](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/cecile/32/98_2.png) [@Cecile](https://the.fmsoup.org/u/Cecile)\
**Post date:** [April 28, 2023, 8:53pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/8 "2023-04-28T20:53:08Z")

</div>

The demo you attached seems to be the original file... 🙂

---

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [April 28, 2023, 9:00pm UTC](https://the.fmsoup.org/t/feels-like-4th-grade-math-but-it-isnt/3532/9 "2023-04-28T21:00:12Z")

</div>

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