Hi Filemaker community,
I am currently working on a resource scheduling solution. The solutions is used to help our human resource manager to create weekly schedules.
The way I have set up is by using five tables as following:
Schedule - This "main" parent table where a schedule is created and all other tables are related to it.
Days - Each schedule creates 7 records in this table, each with its own primary key, and foreign key to relate to the schedule. Makes for easier reporting on my part.
Events - This is where the bulk of the information is found, Each event is related to a day, a person, and a job.
Jobs - Holds job descriptions, estimated time, etc...
People - Personal information about our staff members.
My problem is -
I use a combination of summary fields and calculation fields to get the amount of hours each person works each day, and the amount of days each person works every week. Here lies the problem, To get that working I have to do all the calculation in the EVENTS table via a portal in my SCHEDULE Form layout. But looking at the events portal will show me all the events that each person have in the current schedule, I need to see only one representation for each person and the right amount of hours and days per person. Every filtering of the portal will result in the summary fields displaying 'wrong' information . I imagine the reason is that filtering the portal basically creates a found set, which the summery fields then treat accordingly. I can hide the rows with a calculation or by using SQL, but this, I feel is wrong approach.
If I create a portal to show records from my PEOPLE table, then It works great and I see one record per person in the schedule. But then my calculations, and summary fields don't work.
I am really lost at the moment, hoping someone here could shine a light on the situation.
I attached a picture of my relationships, and a part of my layout that shows related EVENTS and calculations.