7 Replies Latest reply on Sep 30, 2013 4:33 AM by JeroenAarts

    Portal filter fails



      Portal filter fails


      FileMaker Pro



      Operating system version

      Mac OS X 10.7.5

      Description of the issue

      When a portal filter is used and the calculation involves columns, that are themselves calculations using a global variable, the filter shows records only up to the first record failing the condition and doesn't look further.

      Steps to reproduce the problem

      - Create a table "Master" with 1 column named defined as:
        __id | Number | Auto-enter Serial

      - Create a table "Slave" with 1 columns defined as:
        __id | Calculation | Unstored, = GetValue ( $$SlaveIDs; Get ( RecordNumber ))

      - Create a relation between the 2 tables:
        Master::__id X Slave::__id

      - Create a layout based on the table Master

      - Place a portal on the layout Master, based on table Slave, place the field Slave::__id to the portal and specify the following filter condition:
        Slave::__id > 3

      - Create a record in the layout Master

      - Create a layout based on table Slave and create 6 records in this table

      - Create script "SetSlaveIDs" with just one step:
        Set Variable [$$SlaveIDs; Value:"1¶2¶3¶4¶5¶6"]

      - Execute script "SetSlaveIDs"

      Expected result

      The portal in the Master layout should show 3 lines with values:

      Actual result

      Portal is empty.

        • 1. Re: Portal filter fails

               I don't think Get ( RecordNumber ) -- which returns either the location of a record in a found set or the location of a record in a portal will work in this context.

               Can you get a single record to appear if you redefine that calculation to just enter an integer from 1 to 6 instead of get ( RecordNumber )?

               BTW, care to explain WHY you would set up such an unusual system for controlling what records appear in the portal?

          • 2. Re: Portal filter fails

                 Hello there,

                 I agree with Phil interrogations. Moreover, i saw one detail that immediately surprised me : 


                      - Create a table "Slave" with 1 columns defined as:
                      __id | Calculation | Unstored, = GetValue ( $$SlaveIDs; Get ( RecordNumber ))
                      - Create a relation between the 2 tables:
                      Master::__id X Slave::__id
                 Is that meaning you are attempting to establish a relationship using an unstored calc ?
                 If true, that cannot working. And the conclusions are the same as Phil's, because this calculation cannot be stored so it is just impossible to do that way... Sorry. qui pleure
                 Bye , Fred
            • 3. Re: Portal filter fails

                   Fred, since this is an expression in a portal filter, not a case of matching fields in a relationship, the fact that the field is unstored should not keep it from working though it might slow down the rate at which a layout with this portal updated.

              • 4. Re: Portal filter fails


                     Thank you for the post and discussion.


                     In my testing, I can get a portal to filter based on an unstored calculation; however, if the relationship is based on the unstored calculation, then I cannot even get the portal to populate anything to filter.


                     Schema follows:


                     "Primary" table

                     1. Pk (auto-enter serial)

                     2. info (text field)

                     3. unstored calc = Pk + 1


                     "Secondary" table

                     1. Fk (text field)


                     The relationship is drawn from Primary::Pk ---- Secondary::Fk and the portal on the layout associated to the Secondary table displays records from the Primary table filtered using the following calculation:


                     Primary::unstored calc > 5


                     The portal displays all records added where the unstored calculation field contains a value greater than 5.



                     FileMaker, Inc.

                • 5. Re: Portal filter fails

                       Hi all and thanks for all the input!

                       Actually, Phil's first reply was right on the spot. If I add a column of type number, populate the rows with the sequence 1 - 6 and use this column as the second parameter of the GetValue function (instead of the Get ( RecordNumber )), everything works as expected.

                       As to why we need this and why we overlooked the Get ( RecordNumber ) misuse:

                       The project is a database for keeping historical records of individuals in certain historical period. For each individual the user must be able to add properties like "Father Of" with value being a link to another individual, refrerences to historical documents, additional names used for this person in different documents - they have lot of freedom in creation of these properties.

                       To achieve this we needed to build quite a complex relationship structure and for individuals with around 25 properties the portal refresh time became unacceptable for the user (several second). Lucky for us the properties can be grouped.

                       So we switched to this virtual table design. Our table contains a fixed number of records with the calculations retrieving values from the global variable. We populate the global variable with the groups and only after a user clicks on a group we add the properties to the list. See the attached picture.

                       All worked well until recently the client told us they sometimes open a second window to compare properties of different individuals and this of course didn't work, because the script populated the global variable only for the individual that triggered the script last. So I built multi-window logic in there, filling the global variable with data of all currently displayed individuals along with the name of the window they are displayed in. Then I would use a filter to display in each window only records belonging there. And since before everything worked fine with Get ( RecordNumber ) it slipped my mind that it will fail in the second window. And when I went to display the data in table view, it showed the correct values as well.

                       While I haven't confirmed this working in the actual project yet, it works in the test case I presented here so if you are interested in the real results, I'll post later...

                  • 6. Re: Portal filter fails

                         Are you familiar with using a join table to link records in a many to many relationship? I'm not 100% sure from what you have described here, but I suspect that would allow you to set up as many links as you need for any one individual.

                    • 7. Re: Portal filter fails

                           Yes here at ClickWorks we are familiar with join tables. We needed a virtual list to display categories and category details in the same portal like in a treview. The issue we were trying to solve was to get the same virtual list working accross multiple windows with different data sets. We overlooked Get ( RecordNumber ) as we've always been using it in our virts. Thanks again for your suggestion!