10 Replies Latest reply on Jun 23, 2015 12:23 PM by scottlynch

    Portal Relationship Problem How To Remove Repeated Values



      Portal Relationship Problem How To Remove Repeated Values


      I’m trying to adapt a personell starter solution and I’ve run into an issue with filtering a portal relationship that i can’t seem to work through. I have attached a picture which probably describes my issue better than i can explain it.

      In the Time Off Portal I enter the employee leave data as shown in the picture. Originally I was using FilterValue calculations and summary fields to give me the individual counts of each leave type. This worked well, but I was having to create one record for every single day of leave. 

      So I created a field (cEndMinusStart) that counts the days between the start and end date which also works. 

      The problem I’m now having is filtering the leave type counts as can be seen in the second  picture to remove the duplicates or repeated occurrences.

      How do i filter the relationship to show only one value of each leave type i.e. holiday, sick etc. I’ve being working on this for sometime and from what ive read, it seems I need a table occurrence or self join of the Time Off table, which i've setup. 

      But I’m struggling with the logic and obviously doing something wrong. Any help or push in the right direction would be very much appreciated.

      I have managed to calculate the days total for each leave type value by creating a Qty calculation field, Qty = Sum ( Time Off 2::cEndMinusStart )

      Many thanks.



        • 1. Re: Portal Relationship Problem How To Remove Repeated Values

          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.

          • 2. Re: Portal Relationship Problem How To Remove Repeated Values

            Hi Phil,

            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.


            • 3. Re: Portal Relationship Problem How To Remove Repeated Values

              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.

              • 4. Re: Portal Relationship Problem How To Remove Repeated Values

                Hi Phil,

                Many thanks for your help so far

                I have been working through your instructions and believe i have them set up as you suggested using a new "Leave Type" table. The issues I’m now having is actually entering the records. The portal was originally based upon the TimeOff table which still shows the existing records, but cannot enter any new records. I have “Allow creation of records" checked in the relationship box.

                I have created seven records in the "Leave Type" table as suggested to represent all types of leave, i.e. Holiday, Unpaid Leave, Sick Leave etc etc.

                Do I now need to base the record entering portal on the new "Leave Type”  table, which means adding new start date fields etc. 

                Also what does the gPersonnelID global field actually create along with the OnRecordLoad script. I’ve read up on them and understand that they store values and are used for all records in the file. 

                I guess i’m struggling on how to integrate all the amendments together. I think I’m near but just missing something obvious..any push in the right direction would be a great help. Many thanks.

                • 5. Re: Portal Relationship Problem How To Remove Repeated Values

                  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.

                  • 6. Re: Portal Relationship Problem How To Remove Repeated Values

                    Hi Phil,
                    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? 

                    • 7. Re: Portal Relationship Problem How To Remove Repeated Values
                      /files/042a4d4f13/Screen_Shot_2015-06-23_at_14.30.01.png 901x668
                      • 8. Re: Portal Relationship Problem How To Remove Repeated Values
                        /files/e398babf26/Screen_Shot_2015-06-23_at_14.28.58.png 894x382
                        • 9. Re: Portal Relationship Problem How To Remove Repeated Values

                          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 )

                          Caulkins Consulting, Home of Adventures In FileMaking

                          • 10. Re: Portal Relationship Problem How To Remove Repeated Values

                            Hi Phil,
                            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.