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.
