1 2 Previous Next 16 Replies Latest reply on Sep 5, 2014 9:06 PM by user19752

    Hide "doubles" in portal

    Boutsy

      Hello,

       

      It should be simple, but I just can't find the way to do it....

      I'm using FMP13 and I would like to make a portal in wich only 1 record of each "type" is shown. Let's say we get the data from a table "X" with the field "First Name". In that table we have 5 people with the name "Bob", 3 with the name "john" and 1 with the name Alan.

      I would like to have a portal in wich you see the names Bob, John and Alan only once. It should give the same result as making a value list that refers to the table "X" in which you select "Use values from field" X::First Name. Doing this, you would get a list with only Bob, John and Alan.

      Can this be done by filtering??

       

      thanks,

       

      Marc

        • 1. Re: Hide "doubles" in portal
          erolst

          Boutsy wrote:

          It should be simple,

           

          Not really …

          Boutsy wrote:

          Can this be done by filtering??

           

          In the portal table, create a summary field “CountOf ( running ) yourNameField”. As the sort field, select the field coming from the table occurrence that your relationship is based on. Sort either the relationship itself or the portal by that field.

           

          In the portal filter, add the condition that PortalTO::sCountOf = 1.

           

          Another way is to create a self-join by name, then single out one record by comparing some datum (entry timestamp, ID …) to the Max() or Min() of that value of all matching records; if it is, return a 1 as result. Then use that field for filtering.

           

          Be aware that in both scenarios the field isn't stored, and that portal filtering is inherently slow when applying it to a large number of related records.

          • 2. Re: Hide "doubles" in portal
            dmb_fmp

            I'm finding that getting a distinct list of First Names is the simple part. The hard part is getting that distinct list to appear in a Portal...it feels like a riddle.

            • 3. Re: Hide "doubles" in portal
              erolst

              dmb_fmp wrote:

              The hard part is getting that distinct list to appear in a Portal

               

              The “hard part” is to work with the fact that a portal shows related records, not a list.

              dmb_fmp wrote:

              it feels like a riddle.

              Post #2?

              • 4. Re: Hide "doubles" in portal
                dmb_fmp

                erolst wrote:

                 

                In the portal table, create a summary field “CountOf ( running ) yourNameField”. As the sort field, select the field coming from the table occurrence that your relationship is based on. Sort either the relationship itself or the portal by that field.

                 

                In the portal filter, add the condition that PortalTO::sCountOf = 1.

                Trying this technique I am not getting what I think is the desired result. What have I done wrong?

                 

                Screen Shot 2014-09-04 at 10.17.22 AM.png

                • 5. Re: Hide "doubles" in portal
                  dmb_fmp

                  erolst wrote:

                  dmb_fmp wrote:

                  it feels like a riddle.

                  Post #2?

                  No, not Post #2 (I wrote this reply to the OP before I saw your reply). I apologize for the confusion. What feels like a riddle is trying to do this simply.

                  • 6. Re: Hide "doubles" in portal
                    erolst

                    dmb_fmp wrote:

                     

                    Trying this technique I am not getting what I think is the desired result. What have I done wrong?

                     

                    Nothing, I think – because I can't get it to work either (though I was positive I had used this already, or I wouldn't have suggested  … but it probably was for Conditional Formatting or Hide).

                     

                    My only idea is that the act of filtering itself makes the summary evaluation fail; if you look at a sorted, unfiltered portal, you see that the summary field works correctly; but the filter doesn't (or at least not as expected).

                     

                    Never mind, there's always the other method … see attached file.

                    • 7. Re: Hide "doubles" in portal
                      Boutsy

                      Thank you all. It was very helpfull and resolves my problem.

                       

                      Boutsy

                      • 8. Re: Hide "doubles" in portal
                        TomHays

                        Like others have stated, this isn't simple.

                         

                        As erolst illustrated, you can solve this problem by adding additional fields to the X table to identify the duplicates along with supporting relationships and table occurrences.  You can then filter using data in the fields that appear directly in the X table.

                         

                         

                        A second approach is to use a global script variable to store the unique values encountered as FileMaker evaluates each record it is filtering for the portal.  This approach requires no new relationships, fields, or table occurrences.  At minimum it requires only the portal filtering calculation and another object on the layout with a conditional formatting calculation.

                         

                        Use this calculation for your filter (assuming that the portal displays records in the related X table occurrence and that the field is called Firstname).

                         

                        Let(

                        [

                        theValue = X::Firstname;

                        posn = Position($$allValuesSoFar; theValue; 1; 1);

                        $$allValuesSoFar =

                        Case(

                           $$allValuesSoFar = ""; theValue;

                           posn > 0; $$allValuesSoFar;

                           $$allValuesSoFar & "¶" & theValue

                        )

                        ];

                        posn = 0

                        )

                         

                        In practice you will discover that this will work only the first time you view the portal on the layout.  On second and later viewings, the global variable already has a value.  Solve this in whatever means is convenient in your work flow.

                        (1) You can use a Layout Script trigger (On Record Load) and a set variable script step to assign $$allValuesSoFar to be empty.

                        (2) You can add an object such as a button with a Conditional Formatting calculation that assigns $$allValuesSoFar to be empty with a Let() statement. This will work to empty the variable before it is used for the portal filter calculation if you change the object to be drawn before the portal because it appears "behind" it in the layout's object layering (Arrange --> Send to Back).

                         

                         

                         

                        I haven't tried this, but I think a third approach would be to generate a list of unique values for the portal and assign it to a global variable via a script or a calculation using Let().  To get this list you can even define a value list and get a copy of its (unique) values using ValueListItems().

                        Once the list of values is in the global variable, you can then display the list using the Virtual List technique and have the virtual table appear in your portal.

                         

                        -Tom

                        • 9. Re: Hide "doubles" in portal
                          erolst

                          TomHays wrote:

                          adding additional fields … along with supporting relationships and table occurrences.

                          Just one of each, if you please

                           

                          But after reading your comments, I rethought the problem, and now think that the following is the easiest portal filter implementation:

                           

                          Let ( [

                            allIDs = Substitute ( List ( Children::ID ) ; ¶ ; "," ) ;

                            uniqueIDForName = ExecuteSQL ( " SELECT MIN ( ID ) FROM Children WHERE ID IN (" & allIDs & ") GROUP BY nameFirst " ; "" ; "" )

                            ] ;

                            not IsEmpty ( FilterValues ( Children::ID ; uniqueIDForName ) )

                          )

                           

                          which of course is doing exactly the same as the calculation that uses the self-join – but without the overhead.

                           

                          Probably not too scalable, but then neither is portal filtering itself …

                          • 10. Re: Hide "doubles" in portal
                            dmb_fmp

                            But I really liked the potential simplicity of your 1st method! It inspired me to create a solution that requires only 3 Fields, 1 Table, 1 Script, & 1 Filter.

                             

                            Fields:

                            FirstNames

                            sCountOf

                            z_sCountOfStorage

                             

                            When a new name is entered, the script just opens a new window, shows all, sorts by FirstNames, replaces the z_sCountOfStorage with the sCountOf value, & closes the window.

                             

                            Then, using the filter works just like it should when set like "z_sCountOfStorage = 1"

                            • 11. Re: Hide "doubles" in portal
                              erolst

                              dmb_fmp wrote:

                              It inspired me to create a solution that requires only 3 Fields, 1 Table, 1 Script, & 1 Filter.

                               

                              I wouldn't consider that progress, compared to 1 field, 1 TO, 1 relationship, and (especially) no script or triggers.

                               

                              Note the SQL-based solution I suggested in a follow-up post, and which is even more economical; attached file shows it in practice.

                              • 12. Re: Hide "doubles" in portal
                                user19752

                                This expression confuse me since first Children::ID in List function is evaluated different context from next Children::ID in FIlterValues function.

                                 

                                'The portal filter calculation will evaluate the same as a field calculated in a portal row.' is not true.

                                • 13. Re: Hide "doubles" in portal
                                  erolst

                                  user19752 wrote:

                                  This expression confuse me since first Children::ID in List function is evaluated different context from next Children::ID in FIlterValues function.

                                   

                                  Yes, and otherwise that scheme wouldn't work: the first reference in the Let() declaration is evaluated via the relationship, but the second one in the context of a portal row, because the expression it appears in is the portal filter proper. Not sure from where your confusion stems …

                                  'The portal filter calculation will evaluate the same as a field calculated in a portal row.' is not true.

                                  If you present something as a quote, please indicate its source. I cannot find the quoted statement in this thread.

                                   

                                  Also, I have no idea what you want to say by it.

                                  • 14. Re: Hide "doubles" in portal
                                    user19752

                                    Thx, and sorry i quoted from FM help

                                    http://www.filemaker.com/help/13/fmp/en/html/relational.11.22.html#1033103

                                     

                                    make a calculation field in portal table as the expression, it is evaluated in context of portal table, so List(tableItself::field) returns only one value every time.

                                    1 2 Previous Next