A reader asks:
Is it possible to create a portal paging system with filtered portals in FileMaker 11? I am interested in showing a portal without the scroll bar. Buttons will take the user to the first, previous, next, and last set of records in the portal. I know this is possible when the filtering occurs on the Relationship Graph but I can’t figure out how to do it when the filtering occurs on the presentation layer in FileMaker 11.
Absolutely. With a script trigger and a few global variables, you can implement this feature in any database where parent records display child records. Arrow icons or buttons provide the user with the ability to view sets of records without having to scroll.
Why would you choose this scripted approach over simply providing a scroll bar? As a rule of thumb, I generally employ native FileMaker functions and features over something I have to script and manage myself. Nine times out of ten, users have no objection to navigating with scroll bars, which FileMaker handles just fine. If users have a strong preference for navigating with previous and next buttons, however, this function provides that feature without requiring significant development time. Using functions in the presentation layer obviates the need for additional relationships and table occurrences. The user gets the user interface they want, and you get to keep the relationship graph under control. You may also have some geeky fun in the process.
In the following example, we will be looking at departments and their related employees. For each parent record - each department - you’ll need to first identify the IDs of the related employees. Using the OnRecordLoadscript trigger, we’ll set a variable $$IDs equal to:
List ( employees::employeeID )
We’ll also record the total number of related employees, setting $$maxEmployees to:
ValueCount ( $$IDs )
We’ll also assume that, each time you navigate to a department record, we will start afresh with our portal, setting a variable $$portalStart to 1. Finally, we should also then refresh the window - a necessary step with filtered portals.
Let’s assume our portal shows 10 rows at a time. If we set the variable $$portalStart to 11, we’re trying to see related records 11-20. Our calculation to filter our portal needs to pick out the IDs of those records, and then see if each employee’s ID is in that subset.
Let (
subset = MiddleValues ( $$IDs ; $$portalStart ; 10 ) ;
FilterValues ( subset ; employees::employeeID ) ≠ ""
)
If we translate this expression to English, it might read something like this: Beginning at the starting record, take 10 values, and only include in the portal records whose primary keys (employeeID) match one of those 10 values.
Now we need a way to change that $$portalStart variable. When we “scroll up” we can subtract 10 each time, to a minimum of 1. If we’re trying to “scroll down”, we should increase it by 10. However, we don’t want to go too far; if there are only 38 related employees, we shouldn’t be able to set $$portalStart greater than 29 (to see the ten rows between 29-38). Lastly, we might want to be able to quickly jump to the beginning or end of the related records. Scrolling to the top is easy: we just set $$portalStart to 1. Scrolling to the end uses the same maximum value we use when scrolling down: $$maxEmployees - 9.
Set Variable [$scroll; Value: Get ( ScripParameter )]
Set Variable [$maxStart; Value: $$maxEmployees - 9]
If [$scroll = "up"]
Set Variable [$$portalStart; Value: Max ( 1 ; $$portalStart-10 )]
Else If [$scroll = "down"]
Set Variable [$$portalStart; Value: Min ( $maxStart ; $$portalStart+10 )]
Else If [$scroll = "home"]
Set Variable [$$portalStart; Value: 1]
Else If [$scroll = "end"]
Set Variable [$$portalStart; Value: $maxStart]
End If
Finally, we want to report on our layout the set of related records being viewed in the portal. We’ll set one more variable, $$portalRecordSet, and use it in merge text on the layout.
"Records " & $$portalStart & "-" & $$portalStart + 9 & " of " & $$maxEmployees
And remember to refresh the window!
In this way, the user may navigate through sets of 10 records without the need of a scroll bar. TheOnRecordLoad script trigger sets the stage by grabbing the list of related records, and our buttons allow us to set $$portalStart and filter the list.
| Download Sample File |