7 Replies Latest reply on Jan 12, 2011 12:21 PM by FentonJones

    Portal filtering - only show most recent related record

    kageryu

      Title

      Portal filtering - only show most recent related record

      Post

      Greetings,

      I am working on a database that currently stores 3 contract-like files in separate container fields of each contracted entity's record. The way it is presently built, newly imported copies of these files overwrite the old ones. I have been asked to upgrade the database so that it will retain previous copies.

      I have created another table to store one of those document types in, and am relating the documents to the entities based on the entity record #. (Fortunately for me, documents are only associated with a single entity). Viewing and accessing all related items via a portal works perfectly. However, I have also been asked to only display the most recent version of each document in the primary layout.

      Each document has an auto-entered creation and mod timestamp, so I've tried filtering the portal using those fields. Per the help article at http://help.filemaker.com/app/answers/detail/a_id/7557/~/portal-filtering-introduced-in-filemaker-pro-11 , I have tried:

      If ( Contracts::Creation Date = Last ( Contracts::Creation Date ) ; 1 ; 0 ) 

      If (Last ( Contracts::Creation Date ) ; 1 ; 0 ) 

      Last ( Contracts::Creation Date ) 

      ... as my filter terms, but the portal still shows me all related records. Perhaps my syntax is off? I also thought that perhaps the portal just wasn't updating correctly, but adding (and clicking) a Refresh Window button didn't make any difference. I've also tried making sure the Contracts table is sorted by Creation Date, just in its own layout view and in the relationship settings, to no avail. I am using FMPA 11, but the file I'm working on resides on a server currently running FMS 10. However, copying it locally to my desktop and opening it directly in FMPA 11 instead of from the server produced the same behavior.

      I managed to find one other related help article at http://help.filemaker.com/app/answers/detail/a_id/2923/~/showing-only-the-most-recently-added-portal-rows  stating, "Showing the most recently added related record can be done easily with a calculation and the Last function. However, if you want to show the last two or three related records in a portal then the task becomes more difficult. In this case, you'll need to use a script to accomplish the task." -- it proceeds to go into detail about how to do the latter, and I can't find information about only showing the most recently added record! Apparently that is too easy to bother detailing but still beyond me. Undecided

      Any help would be greatly appreciated..

        • 1. Re: Portal filtering - only show most recent related record
          Rajkumar

          Portal imports all records of matching criteria. So here what u need to do is, make the portal having nuber of rows = 1, and disable the vertical scroll bar, and sort the portal on "timestamp" basis. I hope it'll solve ur problem.

          • 2. Re: Portal filtering - only show most recent related record
            kageryu

            Hmm.. that does seem to achieve the goals from a UI perspective at least. The other items are still in the portal, but hidden outside of view. So, this might be a sufficient workaround, thanks!

            Still, it seems like the "portal filtering" advertised in the first help article should be able to do this Undecided

            • 3. Re: Portal filtering - only show most recent related record
              philmodjunk

              The portal filtering can do this, it's just not needed.

              Contacts::ContactID = Last ( Contacts::ContactsID )

              Should do it if there is no sort order defined on the relationship (Last will return the most recently created related record only if the relationship is not sorted.)

              • 4. Re: Portal filtering - only show most recent related record
                kageryu

                I just disabled sorting on the relationship, and tried setting the portal filtering to each of these:

                Contracts::Creation Date = Last ( Contracts::Creation Date )

                Contracts::DocID = Last ( Contracts::DocID )

                ... and also tried those nested in an If statement, but still no luck, it still shows all records :(

                • 5. Re: Portal filtering - only show most recent related record
                  philmodjunk

                  Interesting. I would have expected that to work, but my tests confirm your results. Apparently, this expression evaluates from the context of the portal instead of the context of the layout.

                  If I define this calculation field in the layout's table: Last ( PortalTable::IDfield )

                  Then I can use this field in my portal field to restrict the records in the portal to just the last field.

                  But I'd just use the one row portal suggested earlier. That's a much simpler approach.

                  • 6. Re: Portal filtering - only show most recent related record
                    kageryu

                    Ok.. well I'm glad to know that I was trying an approach that theoretically should have worked but didn't, rather than just being an idiot. This isn't a high-security database so the one-row portal technique should do the trick. Thank you both.

                    • 7. Re: Portal filtering - only show most recent related record
                      FentonJones

                      Yes, a Portal Filter is a little different from the way a regular related test evaluates (say in a calculation field in the parent table), in that both sides of the test evaluate in the child table, as you say. Normally this makes things easier. But not in this case. 

                      Another way to do the same (in case you ever need it) would be to create an unstored calculation in the parent table:

                      Last (child portal relationship::field with data )

                      Then point to that during the test. That would also work, as there is a many-to-one relationship from the child to its parent.

                      P.S. The Last() function is unusual, in that it returns the "last value", which is not always from the "last record", even if the relationship is unsorted. If the specified field is blank in the last record, it will return the value of the first previous which has a value. It was originally created to work with Repeating fields, before FileMaker was relational, way before there was anything like Custom Functions. So you can see why it would work that way.