Sounds like you need to switch to another layout design such as a summary report where you can use sub summary layout parts to replace multiple entries with a single row of data with a sub total, or add a new table with one record for each type to show in your portal, or a large format calculation field displaying the results of an ExecuteSQL function with a "group by" clause to group same types into a single row with a sub total.
Many thanks. I think the ExecuteSQL is beyond me at present. But should be able to manage another layout design using sub summary parts or indeed creating another table for the "leave types". In you opinion ignoring ExecuteSQL, what is the best way forward. I guess creating another table keeps everything neat and manageable for want of a better word.
I can't recommend one over the other. For more on how to set up a summary report (a part of the tutorial on the end shows how to get just one row of data per group), see: Creating Filemaker Pro summary reports--Tutorial
To set up a table where you have just one record for each leave type to do the same type of grouping in a portal, you can set up these relationships:
Personnel::anyfield X LeaveTypes::anyfield
LeaveTypes::gPersonnelID = TimeOff::Personnel ID Match Field AND
LeaveTypes::LeaveType = TimeOff::Type
gPersonnelID is a global field that the OnRecordLoad script trigger on your personnel layout updates with this step:
Set Field [LeaveTypes::gPersonnelID ; Personnel::Personnel ID Match Field ]
Aggregate functions such as Sum can be defined in LeaveTypes to compute subtotals from time off or you can use summary fields defined in Time Off to show the same sub totals.
Since this relationship combines data from multiple records, you will not be able to use it for data entry. This portal is now something that reports sub totals for each leave type.
You still need a portal direct to Time Off as you still need to be able to create multiple records in Time Off of each leave type.
By using two Tutorial: What are Table Occurrences? of Time Off you can have both portals and both your original and this new relationship.
Im still working on this Personnel Form and have altered the solution as your suggestion with a LeaveTypes table. Everything seems to be working correctly. But I'm now struggling now with how to use the GetSummary function or a self join way to enable me to calculate leave days remaining. In the screenshot I need to use the sum (10) of the portal row named "Paid Holiday" so it shows 18 days remaining and not 28 in the top box.
But whatever combination of GetSummary field and break field I'm using, including sorting the portal by LeaveTypes. i can't get it right. Where am i going wrong?
GetSummary is function best used with a summary report, not a portal. It works from a current found set, not a related set of portal records in another table.
I am guessing here that your layout is based on Personnel Records.I am also guessing that "Holiday Allowance" stores the total holidays that a given employee can take such that
Holiday Allowance - Total Paid Holiday = 28
The problem is getting Total Paid Holiday to calculate the correct Sum.
Without using ExecuteSQL, here are two options:
1) Define a calculation field, cHolidayTime in Time Off as:
IF ( Type = "Paid Holiday" ; cLeaveDuration )
Then you can define a calculation field in Personnel Records with this expression:
Holiday Allowance - Sum ( TimeOff::cHolidayTime )
2) define a one row portal to TimeOff and give it this portal filter: TimeOff::Type = "Paid Holiday". Put a summary field from TimeOff, defined to compute the total of cLeaveDuration inside this portal row. However, this total is "display only", you can't easily use it in calculations and scripts elsewhere in your solution.
3) Define a calculation, constPaidHoliday, with "Paid Holiday" as its calculation and a text result type. Set up this relationship to a new occurrence of Time Off:
Personnel Records::Personnel ID Match Field = TimeOff|PaidHolidays::Personnel ID Match Field AND
Personnel Records::constPaidHoliday = TimeOff|PaidHolidays::Type
And a calculation field can then be defined in Personnel Records as:
Holiday Allowance - Sum ( TimeOff|PaidHolidays::cLeaveDuration )
Yes you are correct that the tabbed layout is based upon Personnel Records. Holiday Allowance is a field in Personnel Records that you manually enter the number of days per year each employee receives. In this case its 28 days which is shown in the box above the portal. But it should show 18 days remaining after the 10 Paid Holidays have been correctly deducted. I will work through your valued recommendations now. Maybe ExecuteSQL is something I should read into?
Once again thank you very much for your help to date.