6 Replies Latest reply on Mar 29, 2013 8:22 AM by philmodjunk

    Filtering records in a portal



      Filtering records in a portal


           I have an application whereby I would like to filter the records displayed in a portal to a finite number. This is so I can minimise web traffic as I would like to use this via IWP.

           Right now the main database has many records. Some of which are allocated to a user. The user can only see their records - this is limited by priviledge set. I can display a nice scrolling portal that will show say 300 records that have been allocated. However,when using IWP it means that the data for all 300 records has to be loaded each time the page is refreshed.

           I would like to limit the portal to say 20 records using a filter but I am struggeling to find out how I can do this. Effectively I would like to filter it so that I can choose which set of 20 records are displayed.

           I have tried using the get(recordnumber) but that seems to show the record number of the record in the main database and not the record number of the related record - by that I mean that if there were 66 related records I would like to find out how I reference numbers 1 to 66 - then I could filter it so that I only show records 1-20, 21-40, 41-60, 61-80 etc. I would use some form of navigation that set a global field or variable to be referenced in the filter calculation.

           Am I approaching this the right way - and if so where am I failing?

           Or, is there a better, more eloquent method that I should be pursuing?


        • 1. Re: Filtering records in a portal

               It's possible to use two relationships to the portal's table and then use getNthRecord to reference a value via relationship 1 to filter the records shown in a portal based on relationship 2. I don't know whether this will make for much, if any of a performance improvement as portal filtering entails its own performance hit on your interface. You'll need to test and see.

               Let's say that you are displaying records in unsorted (creation) order and each record has an auto-entered serial number.

               Then GetNthRecord ( RelatedTable::SerialNumberField ; 21 )

               will return the serial number field value of the 21st related record in Related Table. You could even use a script with this function to capture the value in a global field in the parent table so that you can "filter" your records at the relationship level--which may make for faster performance.

          • 2. Re: Filtering records in a portal

                 Hello and thank you for your tip. I have not completely grasped it. The records that I am dealing with are a subset based on a priviledge set - I have now worked out that I can have a calc field in that table that uses (getrecordnumber) WITHOUT storing the results that will produce sequential numbers in my portal view - that's great but if I start to specify a filter on the portal view then things go bad again.

                 My calculation field 'grn' contains values 1...n in order. If I specify the filter as follows:  grn<20 then it displays only records 1-20 - nice.

                 If I specify the filter as grn>5 then it will list all records 6...n, OK, now I try to combine the two, I set the filter to be 'grn>5 and grn<20' and the result is that no records display. I cannot see what is happening here and would appreciate any tips.

                 An alternative approach was to have a tabbed layout which had say 20 tabs, each with the same portal view but specifiying the number of records displayed as 20 and then the start record as being 1, 21, 41, 61 etc.

                 This certainly works well in the IWP environment (no significant delays in returning to the list due to loading ALL records for the portal) but it does not look quite so neat and has maintenance issues.

                 If i could make the portal filter work the way I want it to then I can see that I can have some calculation fields that work out the lower and upper limits of the records that I would like to display. I could then re-work the limits depending on which 'range' of fields that I wanted to see - all lovely - I just cant make it work - not so lovely.

            • 3. Re: Filtering records in a portal

                   Actually, it seems not quite as simple as I seem unable to reference any field (global) and use it as part of any filter calculation regardless of which table it is in.

                   eg, I try to set a filter 'grn>limit' where limit is a global which contains the number 20 and no records are displayed. Further testing also shows that despite my previous claim, whilst I can filter on 'grn<20', I seem unable to filter on 'grn>20', if I try to use the '>' operator then it returns no results in the same way that it did when I tried to combin ''grn>5 and grn<10'. Perhaps I am on to something here.

                   Certainly, a filter set as 'grn<20 and grn<15' works by displaying records 1-14 (I appreciate it is an almost pointless test but it shows me that the 'and' appears to be working.



              • 4. Re: Filtering records in a portal


                     HOW are records originally created, and are they deleted/changed_in_order often? Do they need to be sorted BEFORE breaking into groups of 20?

                     One straightforwad method is to Run Looping Script when User Logs-In, or when Child Records are added/deleted, that does the following...

                     1. Isolate Records for current User...sort if neded.

                     2. Loop thru all records, setting a new FencePost field called 'Set' with a single # PER each group of 20 records...

                     - So, use a counter variable in Script that starts at 0 and increments by 1 for each Record encountered in Loop.., then when it gets to any Record that represents a MULTIPLE of 20 + 1 (like RecordNumber = 21) it will need to RESET to 1 and start counting up again.

                     - Use 2nd variable called $_set that starts at Value = 1 and updates by 1 each time counter variable RESETS...so first group of 20 get Set field value = 1, 2nd group of 20 getvalue = 2, etc...

                     3. Now, back in Portal Filter....add a Global Field in Parent Table called 'Set__gn' - this will store a number that the User can add/subract to/from (via script) - don't allow 0, or a number higher than ( count ( ChildTable::_ID_pk ) / 20 ). You 'could' add this global field ANYWHERE in solution - if you have a table for globals for instance.

                     4. Add the following predicate to the Filter in Portal:

                     'Set__gn' (the new global field)  = 'ChildTable::Set' (the new fencepost field).


                     IF this does not fit the workflow of DB...you may also consider the following...

                     1. Set up the SAME global field per above strategy, call it 'global_1'. Can be in ANY table.

                     3. Use SQL (select WHERE 'Current_User_ID' = 'User_ID' in Child Table) to set a global VAR that Lists ALL 300 of the User's Child Record's primary ID's (not the Parent ID) - this will happen super fast. If you're not comfy w/SQL, you could use FM's internal Fuunction List() to accomplish same thing, and prob. at same speed (but requires relationship).

                     - This MUST be done via BUTTON (or on file Open) for IWP = no triggers ;-(. But only need be done ONCE per session, unless Child records are added/deleted in that session, then updated with same step. The button may needto 'refresh' the window as well.

                     4. Now, in Portal Filter...set the following 'type' of code...so that ONLY Child records that match the follwing are displayed. This would act by itself, filtering reocrds WITHIN the 300 by groups of 20.

                Let ( [ $_n = global_1 ; $_ids =

                GetValue ( $$_global.variable ; $_n ) & "P" & // paragraph return

                GetValue ( $$_global.variable ; $_n + 1 ) & "P" &

                GetValue ( $$_global.variable ; $_n + 2 ) & "P" &

                GetValue ( $$_global.variable ; $_n + x...up to 20 )  // - you get the idea. // This could also be accomplished with a recursive custom function for faster performance!

                ) ] ;

                Patterncount ( $_ids ; ChildTable::__ID_pk ) // assuming your using ID = UID...otherwise use Custom Funtion called 'In_Value_list'* by Sandor Selovar or equivalent.

                ) // end Let

                     5. Add a button to Parent Table UI (where portal is displayed) for User to 'click' through groups of Records - simply Setting the field 'global_1' to + 1 or -1 than existing value - don't allow 0, or higher than ( count ( ChildTable::_ID_pk ) / 20 ).




                     * Custom Function Below


                     In_Value_List( Value_List , Value )
                     Value_List - A string containing the return-separated list of values to be searched.
                     Value - The string containing the search value.
                     Data Type Returned
                     number / boolean
                     Assume the variable $List contains High¶Medium¶Low
                     Assume the variable $Value contains Medium
                     Set Variable[$Index; Value:In_Value_List($List; $Value)]
                     Then $Index would now contain 2 (which also evaluates to True)
                     Returns 0 (False) if the either the value list or the value is empty.
                     Return 0 (False) if the specified value does not exactly match any item in the value list. (Not case sensitive.)
                     Returns the index (a positive number indicating the sequence number of the value in the list ... that also evaluates to True) if the value exactly matches an item in the value list.
                     FileMaker Versions Supported
                     7, 8, 8.5, 9, 10, 11, 12
                     Determines if a specified value is present in a return-separated list of values:
                     Returns 0 if the either the value list or the value is empty.
                     Return 0 (False) if the specified value does not exactly match any item in the value list.
                     Returns the index (a positive number indicating the position of the value in the list ... that also evaluates to True) if the value exactly matches an item in the value list.
                     Value = If( Right( Value ; 1 ) = "¶" ; Left( Value ; Length( Value ) - 1 ) ; Value ) ;
                     FirstCharPos = If( not IsEmpty( Value_List and not IsEmpty( Value ) ) ; Position( "¶" & Value_List & "¶" ; "¶" & Value & "¶" ; 1 ; 1 ) ; 0 )
                     FirstCharPos < 1 ; 0 ; 
                     PatternCount( Left( "¶" & Value_List ; FirstCharPos ) ; "¶" )


                • 5. Re: Filtering records in a portal

                       Follow-up...for better performance...

                       - Use the Let Formula Above (change the result to simply = $_ids ), so you have 20 Child_ID's in a variable.

                       - Then set a Parent Global Field (in PArent Table) w/ the $_ids variable data.

                       - Use that Parent global field as the Parent KEY in the relationship (linking to the Child_ID's in the Child table).

                       - Maybe add refresh, or use a constant_link in your relationship to force the refresh



                  • 6. Re: Filtering records in a portal

                         I use the following method:

                         Assign an auto-entered serial number field that automatically numbers your records in the order that they are created. The fact that your records in a given portal may be numbered 1, 3, 5 23, 5000, does not matter so long as they need to appear in the portal in creation order which would show the values in this field in ascending order.

                         Then define TWO relationships between your layout's table and the portal's:

                         I will call the layout's table "layout", the portal's "Portal" here. Substitute your own names for mine:


                         Layout::__pkLayoutID = Portal::_fkLayoutID
                         Layout::__pkLayoutID = PortalFiltered::_fkLayoutID

                         For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                         Place a portal to PortalFiltered on your layout. We'll refer to Portal in our filter expression for PortalFiltered.

                         Define a pair of number fields with global storage. These fields must be of type number. I'll call them gStart and gEnd. You wil asign numbers to these two fields to control what records appear in the portal. If you want the first 5 records to appear, assign 1 to gStart and 5 to gEnd

                         Your portal filter expression will be:

                         PortalFiltered::SerialNumberField > GetNthRecord ( Portal::SerialNumberFIeld ; YourTable::gStart ) AND
                         PortalFiltered::SerialNumberField < GetNthRecord ( Portal::SerialNumberFIeld ; YourTable::gEnd )

                         But your portal will not update smoothly when you edit either of these two global fields. You could use the script step: Refresh Window [Flusch Cached Join Results], but there's a better way. Update your relationship to be:

                         Layout::__pkLayoutID = PortalFiltered::_fkLayoutID AND
                         Layout::gStart X PortalFiltered::AnyField AND
                         Layout::gEnd X PortalFiltered::AnyField

                         It doesn't matter what fields you select for match fields for the last two pairs of fields, but by including the global fields in the relationship with the X operator, the filtered portal will automatically update when you change a value in one or both of the global fields.