11 Replies Latest reply on Feb 26, 2013 10:23 PM by philmodjunk

    Portal date filter



      Portal date filter


           I'm trying to filter out any records with an effective date in the future, would this not be as simply as turning the portal filter function on and having the following?

           Salary History_Personnel Records::Effective Date  ≤  Get ( CurrentDate )

           For some reason it's not working, I thought that maybe portal filter could not evaluate get functions and so I also tried setting up a script trigger onRecordLoad to get the current date and set a global variable and swapped out "Get (CurrentDate)" with $$CurrentDate in my calculation and it still doesn't work.


           Any help would be greatly appreciated!

        • 1. Re: Portal date filter

               Is Salary History_Personnel Records the exact text selected in the "Show Related Records From" drop down list found in portal setup for this portal?

               Is Effective Date a field of type date or type text?

               The filter expression should work if the answer to the previous two questions is yes.

               If, however, you leave this file open 24/7, you will not see it update with each new day without something such as Refresh Window [Flusch Cached join Results] refreshing the window where you have this layout on display.

          • 2. Re: Portal date filter

                 My mistake, what happened was that I designed the layout and fields before implementing the portal.  I then inserted a portal and sent it to the back but by doing so and not touching the field floating overtop of the portal they had not "taken" to the portal there for did not work properly.  I now moved them by a hair and then back into place and it's working correctly.

                 I currently have the record load trigger still in place from my tests, would you suggest that to be the more effiecnet setup or simply returning to Get (CurrentDate)?

                 As for the 24/7, it's not the intension but that doesn't mean it might not be one day.  Where would you suggest I place a refresh window?

            • 3. Re: Portal date filter

                   I would return to get (CurrentDate). There's no advantage that I see to using the script and you might have portal refresh issues with it.

                   You should not need to take any special steps to refresh the window unless/until you actually start leaving the file open all the time. If that happens, the only option I see to refresh the window would be to use a script with Install On Timer that sets up a script with this step to be executed once every 24 hours.

              • 4. Re: Portal date filter


                     I have encountered the same thing, but oddly, it appears only in FMPro12 v3 and not in v1. Not only that, I can fix it by replacing the "≥" chacter with a logic statement. In the attached screen grabs, related records via a portal from a "MEETING" table either appear, or do not appear, depending on the following.

                This does not work:

                     MEETING to TOP::Date ≥ Get ( CurrentDate )

                     this does work:

                     MEETING to TOP::Date = Get ( CurrentDate ) or MEETING to TOP::Date > Get ( CurrentDate )

                     This suggests a bug in version 3 to me...

                • 5. Re: Portal date filter

                       Better double check the versions. There were a number of problems with portal filter expressions in 12.0v1 that were corrected in the next two releases. I thus would expect to see such an issue with 12.0v1 that was then corrected in 12.0v3.

                       The following screen shot combines a table view of the portal's table with the filtered portal that only shows dates still in the future. I have copied and pasted the exact text used in the portal filter as layout text just above the portal.

                       I used FileMaker 12.0v3 on a Windows XP platform.

                       You may download and examine the file I created for this test from: https://dl.dropbox.com/u/78737945/FilterByDateTest.fmp12

                  • 6. Re: Portal date filter

                         Hmmmm..thanks for the info.

                         I'm sure about the version of the clients we're using (i.e. the portal-filtering problem only occurs with v3 of the client, not v1 of the client), but my files are hosted on FMServer, and when I just checked, indeed, I have not upgraded my server version yet (will do tonioght!).

                         Odd that the problem, then, only occured with v1 server + v3 client, whereas v1 server + v1 client worked. Still, an easy thing to fix...

                    • 7. Re: Portal date filter

                           Yes, you will need to update server to correct the issue.

                      • 8. Re: Portal date filter

                             I am having flakey results with my filtered portal based on date also. Are there any problems with FMP version 11 and portals not showing results that should show up?


                        • 9. Re: Portal date filter

                               Make sure that the fields that store dates are truly of type date and not text.

                               If that isn't the issue, you'll need to describe what you mean by "flaky results".

                          • 10. Re: Portal date filter

                                 The confusing intermittent error was a portal filtered by "date of service" by the global field "Current date" would sometimes show the  related records and sometimes not. This resulted in some elements being entered several times because the person working with the file didn't see the previously entered record. 

                                 I used the hint above to Refresh Window[Flush Cache Join Results] in a script after the global is set and it worked. So the problem was my not realizing that the global might be changed but the portal filter needed to be refreshed, not obvious because the date was changing at the top. 

                                 Thanks for your help,


                            • 11. Re: Portal date filter

                                   There are ways to set up the underlying relationship so that the portal updates without using Refresh WIndow [flush...