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.
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.
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.
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/*FormatIn_Value_List( Value_List , Value )ParametersValue_List - A string containing the return-separated list of values to be searched.Value - The string containing the search value.Data Type Returnednumber / booleanExamplesAssume the variable $List contains High¶Medium¶LowAssume the variable $Value contains MediumSet Variable[$Index; Value:In_Value_List($List; $Value)]Then $Index would now contain 2 (which also evaluates to True)DescriptionReturns 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 Supported7, 8, 8.5, 9, 10, 11, 12CodeDetermines 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.*/Let([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 )];Case(FirstCharPos < 1 ; 0 ;PatternCount( Left( "¶" & Value_List ; FirstCharPos ) ; "¶" )))
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
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.