Apply Multiple Filters Based on Dropdowns

[Barbie_GTS]

I'm fairly new to FileMaker Pro (I'm just over a month in/trying to build as much as possible under my free trial as there doesn't seem to be a sandbox, before having to pay for licensing when we're not quite ready to go live with the app) currently producing a time management solution for projects managed under my husband's small engineering company.  Our current solution is based on Macro-heavy Excel files that have hit their limit in programmability / aren't user-friendly for collaborative working, hence the branching out into app development to find a solution.  (My day job is as a database analyst for an insurance company so Excel, SQL, Tableau are my main skills.  Learning something new that follows a similar pattern and picking it up fairly quickly isn't beyond me.  I have completed FileMaker Pro Associate certification and am 75% through Specialist, but have watched a lot of how-to videos in-between)

In summary I have tables that cover 'Project', 'Tasks' relating to those projects, 'Employees', a 'Date Lookup' table (as calculating Week Start based on a date doesn't seem to be possible), and a master 'Time' table that captures all of the start/end work times and durations associated to each of the Tasks worked on throughout each day and by whom.  My relationships are working as expected in one-to-many relationships with scripting performed on a form entry for the employees being able to start working on a specific task capturing the start time, then stop and capture the end time to create a duration spent at that point in time for that specific entry.  I've imported all of my master tables from our Excel tables to populate the database tables and it's all flowing as expected.

We invoice out our time monthly based on detailed entries of work completed, as projects run over years into the hundreds of thousands of pounds - this is all done in Xero, so no need to integrate there/output an invoice in FileMaker - we just need a smoother solution for capturing the work completed detail, sundry items used, parts required (Phase 1) and reports/summaries/etc off the back of this down the line (Phase 2).  

I've managed to do most of what we're looking for as a Phase 1 release, but one thing I'm struggling with is populating a weekly summary for the employee logged in, so they can see an overview of everything they have entered for a particular week, enabling them to double-check their entries, have they included enough detail of the task completed, does their total duration logged equal the 40hrs contracted, and see how productive they were ('Tasks' table indicates whether a task is productive/unproductive, so things like annual leave are unproductive).  I can't seem to get a name & week filter to return matching entries in a 'Time' portal, only the name works.

I've played with what table the Layout has as its master and a few different relationship/dropdown options based on a few Google searches, but to no luck.

My Layout:

• Is currently based on the 'Employee' table
• Has a list-detail portal as a drop-down list as the Employee name/FileMaker username for the created entries -
  • This works smoothly to filter the portal to the correct user's 'Time' entries
  • This would be even better if there's no need for a filter for this part, it just returns all entries based on the user logged in
• A portal for the 'Time' table to return day of week, start time, end time, duration, project, job code & detail of task completed
• A portal for 'Date Lookup' table to have a dropdown for week starting 

The portal is successfully returning all entries for the correct person, but not the week too.  I have tried changing the week start dropdown to be based on the lookup field in the 'Time' table, the field in the 'Date Lookup' table, and a self-relationship of Week Start in the Time table, but no luck in the correct week's data only returning.  I have tried adding a filter in the portal setup of If ( Time::Week_Start = Date Lookup::Week Start;1;0) but no luck either.  

Portal Return - this is an anonymised screenshot of my return currently, which is correct for the entries based on the user selected and changes with the dropdown change - the week start selected in the dropdown appears to be overtyping the first row of the returned data (this is a 11/03 entry), but not actually filtering the data.

Any assistance is appreciated.  Loving the solution so far, and most of my other teething issues I have been able to smoothly resolve with a quick Google.

[comment]

I am not sure I fully understand your question. Here are a few points that caught my eye, perhaps they can help:

41 minutes ago, Barbie_GTS said:

calculating Week Start based on a date doesn't seem to be possible

It most certainly is possible. But we need to know on which day-of-week your week starts and what should happen at year (and optionally month) boundaries. At its simplest, the formula:

date - DayOfWeek ( date ) + 1

returns the date of the Sunday immediately preceding or equal to the date.

 

41 minutes ago, Barbie_GTS said:

I have tried adding a filter in the portal setup

Filtering a portal should work, but it will get slower and slower as the number of related records to filter grows. It might be better to construct a dedicated relationship that matches on both EmployeeID and WeekStart (assuming it's calculated in both tables) or calculate both WeekStart and WeekEnd in the parent table and define a range relationship to the child table's date field.

 

41 minutes ago, Barbie_GTS said:

trying to build as much as possible under my free trial

Maybe this method still works? 
https://fmforums.com/topic/21829-fp7-learning-curve-a-no-go-with-boss/#findComment-98111

BTW, your profile says "Version: 11 Client" which makes no sense.

 

Edited January 3 by comment

[Barbie_GTS]

17 minutes ago, comment said:

I am not sure I fully understand your question. Here are a few points that caught my eye, perhaps they can help:

It most certainly is possible. But we need to know on which day-of-week your week starts and what should happen at year (and optionally month) boundaries. At its simplest, the formula:

date - DayOfWeek ( date ) + 1

returns the date of the Sunday immediately preceding or equal to the date.

 

Filtering a portal should work, but it will get slower and slower as the number of related records to filter grows. It might be better to construct a dedicated relationship that matches on both EmployeeID and WeekStart (assuming it's calculated in both tables) or calculate both WeekStart and WeekEnd in the parent table and define a range relationship to the child table's date field.

 

Maybe this method still works? 
https://fmforums.com/topic/21829-fp7-learning-curve-a-no-go-with-boss/#findComment-98111

BTW, your profile says "Version: 11 Client" which makes no sense.

 

Hi Comment

Thanks for your reply.

Re Version - there was nothing in the list that looked like what I have, so just picked something to register 😅 - I have FileMaker Pro 24.1 downloaded.

I tried for ages to Google a week start formula with no luck, so went with a lookup spreadsheet instead as working in weekly summaries is a must for us!  Will give your formula a go to lose a lookup table, thanks!

My question really is can I filter the portal return based on both the user and the week start date, so that the time entries returned are just for those dates in the week selected.  EmployeeID is auto-populated in the 'Time' table from the user entry form based on the CreationUser, and the WeekStart is currently a Looked up field return based on the entry date.  I'll try your suggestion over the weekend

[Barbie_GTS]

Hi Comment

To update - 

Week Commencing Date - this formula worked with a '+2' to have the week starting on a Monday, thanks!

Your suggestion of portal filtering didn't work for me, but after completing some more of the FileMaker Pro Specialist Certification, the next lesson incidentally answered my question!  If only I'd done the next lesson before posting!

I have managed to do this as a Global Field created in my Employee table (table my Layout is based on) that outputs as a Week Commencing dropdown filter to filter the portal using the calculation:

IsEmpty ( Employee::gFilter ) or PatternCount ( Time::Week_Start ; Employee::gFilter )  and some scripting to refresh the output.  

This works beautifully!

[comment]

1 hour ago, Barbie_GTS said:

Week Commencing Date - this formula worked with a '+2' to have the week starting on a Monday, thanks!

That's actually wrong. You may not notice it's wrong if your date is never a Sunday, but in such case your formula will return the date of the following Monday - i.e. the starting day of the next week. The correct formula to use would be:

date - DayOfWeek ( date - 1 ) + 1 ;

 

1 hour ago, Barbie_GTS said:

Your suggestion of portal filtering didn't work for me,

I don't see that I made any suggestion regarding portal filtering - other than to warn you that it will get slow as your number of records increases.

 

1 hour ago, Barbie_GTS said:

 filter the portal using the calculation:

IsEmpty ( Employee::gFilter ) or PatternCount ( Time::Week_Start ; Employee::gFilter )  

I think that could be simplified to:

IsEmpty ( Employee::gFilter ) or Time::Week_Start = Employee::gFilter

If that doesn't work the same way for you then there is something wrong with the data in one (or both) of the fields.

 

[comment]

I need to correct something I wrote earlier:

15 hours ago, comment said:

If that doesn't work the same way for you then there is something wrong with the data in one (or both) of the fields.

This implies that in normal circumstances the two tests should return the same result and that the only difference is the unnecessary complexity added by using PatternCount() instead of a direct comparison.

That is not the case. 

Let's assume that both of the compared fields are calculation fields returning a result of type Date. And that the date format used by the file is m/d/y, with no leading zero for the month.

Now, let's have an example where DateA is Feb 2, 2025 and DateB is Dec 2, 2025. These two are different dates and if the comparison is performed in the date domain:

DateB = DateA

the result will be False.

But the suggested comparison:

PatternCount ( DateB ; DateA )  

will start by converting the dates to Text, and then:

PatternCount ( "12/2/2025" ; "2/2/2025" )  

will return 1 (True).

In addition to a false positive, it is also possible to get a false negative if one or both of the fields contains user-entered data which may or may not have leading zeros.