1 2 Previous Next 16 Replies Latest reply on Jan 17, 2012 1:16 PM by AnnetteSteinberg

    Sorting Filtered Portal

    AnnetteSteinberg

      Title

      Sorting Filtered Portal

      Post

      Version 11.03 Windows 

      Morning,

      I have related two tables Billing Payment and Bill Payment Follow Up Action

      I have a filtered a portal of the Bill payment Follow Up Action with the following calculation: (each portal record is visible when)     Billing_ Payment Follow Up Action::next FU date without business days  ≥ Get (CurrentDate)

      That part works great.  But, I cannot get those filtered records to sort by the filtered date record.  I've tried lookup fields/calculation fields.  Nothing works.  Your help is appreciated.

        • 1. Re: Sorting Filtered Portal
          philmodjunk

          Did you enter portal set up and use the sort option?

          Is "next FU date without business days" a field in the portal's table or a field from a table related to the portal's table?

          • 2. Re: Sorting Filtered Portal
            AnnetteSteinberg

             Yes I did - but it didn't work - I thought maybe this was because of the calculation I used in the filtered portal part.

            next FU date without..." is a field in from the related table Follow Up Action on the Bill Payment table.

             

            • 3. Re: Sorting Filtered Portal
              philmodjunk

              I'm not clear on your answer.

              Is this field a part of the portal's table or some other table?

              You can't sort a portal on a field if the field is not defined in the  portal's table so I am trying to find out if this is the case here or not.

              • 4. Re: Sorting Filtered Portal
                AnnetteSteinberg

                It's from the Portal Table.

                • 5. Re: Sorting Filtered Portal
                  philmodjunk

                  Then you should be able to sort on it. The filter should not affect the sorting other than be limiting what records are visible in it.

                  What results do you get when you specify sorting on this field?

                  • 6. Re: Sorting Filtered Portal
                    AnnetteSteinberg

                    The results are that it's sorting by another field from the related table, Bill_Payment Follow up Action::Follow Up Action. Here's the whole sequence of events.

                    On the Bill Payment Table I have a Date Billed field (date field) which is related from another table. If a date is selected it triggers a script that populates 12 fields from the related Follow Up Action Table.  Six of those fields are text (initial billing, 7 Day F/U, 14 Day F/U, etc.) and six are date fields.  If the text field is "Initial Billing" it takes the Bill Date field and puts the same date in the Follow Up Action date field.  If the text field is "7 Day F/U" it calculates 7 business days between the bill date and puts that into the next portal row, and so on. This part works great.

                    Here's a picture of the trigger script.

                     

                     

                    • 7. Re: Sorting Filtered Portal
                      philmodjunk

                      What does this script have to do with the sort options specified on your portal?

                      None of the set field steps modify the "next FU date without business days" field on which you want to sort the values. Or is this a calculation field that returns different dates based on the data entered in the Follow Up ACtion field? If so please post the calculation for this field and what "return type" is specified for it. (should be a date return type.)

                      Since they start with the first portal row, this script will overwrite any data already shown in the portal. Don't know if that is what you want this script to do or not.

                      That said, you can simplify the above script be removing all of the go to field steps here as they are not needed.

                      • 8. Re: Sorting Filtered Portal
                        AnnetteSteinberg

                        None of the set field steps modify the "next FU date without business days" field on which you want to sort the values. Or is this a calculation field   that returns different dates based on the data entered in the Follow Up ACtion field? If so please post the calculation for this field and what "return type" is specified for it. (should be a date return type.)

                        "Next FU date without business..." is a calculation field.  Here's the calc.  return is a date/unstored.

                        Case (follow up action = "Initial Billing"; GetAsDate (JPMS::date_bill);
                        follow up action = "7 Day F/U"; (JPMS::date_bill) + 7 + Int ( 7/ 5 ) * 2 + If ( Mod ( 7 ; 5 ) + DayOfWeek ( JPMS::date_bill )  ≥ 7 ; 2 );
                        follow up action = "14 Day F/U"; (JPMS::date_bill) + 14 + Int ( 14/ 5 ) * 2 + If ( Mod ( 14 ; 5 ) + DayOfWeek ( JPMS::date_bill )  ≥ 7 ; 2 );
                        follow up action = "21 Day F/U"; (JPMS::date_bill) + 21 + Int ( 21/ 5 ) * 2 + If ( Mod ( 21 ; 5 ) + DayOfWeek ( JPMS::date_bill )  ≥ 7 ; 2 );
                        follow up action = "30 Day demand"; (JPMS::date_bill) + 30 + Int ( 30/ 5 ) * 2 + If ( Mod ( 30 ; 5 ) + DayOfWeek ( JPMS::date_bill )  ≥ 7 ; 2 );
                        follow up action = "37 Day lien requested"; (JPMS::date_bill) + 37 + Int ( 37/ 5 ) * 2 + If ( Mod ( 37 ; 5 ) + DayOfWeek ( JPMS::date_bill )  ≥ 7 ; 2 ))

                        • 9. Re: Sorting Filtered Portal
                          philmodjunk

                          Is ths field returning the correct value when displayed in your filtered portal?

                          • 10. Re: Sorting Filtered Portal
                            AnnetteSteinberg

                             Yes it is based on the if visible box with this calc: Billing_ Payment Follow Up Action::next FU date without business days  ≥ Get (CurrentDate)

                            • 11. Re: Sorting Filtered Portal
                              philmodjunk

                              Note quite what I was asking, but close. What I'm asking is if you can see this field for every visible portal record and if you see an actual, valid date in each. I'm trying to narrow down the issue to a specific cause here and need to confirm you are seeing actual, valid dates in this field.

                              The fact that the filter works suggests that this is the case, but I want to be sure...

                              • 12. Re: Sorting Filtered Portal
                                AnnetteSteinberg

                                 By the way, as always thanks for your patience.  Yep it's showing a valid date - the date which is greater then or equal to the current date. Here's a picture.

                                • 13. Re: Sorting Filtered Portal
                                  philmodjunk

                                  Ok, and I can also see that it's not sorted in any specific order in the portal. I assume this shot was with a sort order on this field specified in portal setup?

                                  I'm going to play with a demo file before posting again here by creating a 3 table demo file with a portal filter and an unstored calculation field that returns date to see if I can replicate what you have here in simpler form. (If I can this might be a FileMaker bug. If I can't then we can compare demo file to your file to see if we can find a difference in the two files.)

                                  • 14. Re: Sorting Filtered Portal
                                    philmodjunk

                                    The following demo file has a sorted portal that filters by date and correctly sorts on the same date field. The date field is an unstored calculationfield that draws its value from a related record.

                                    http://www.4shared.com/file/I7GVL6rz/SortedFilteredPortalTest.html

                                    Download it and compare it to yours to see if you can spot any significant differences.

                                    1 2 Previous Next