FileMaker Weetbicks

Producing Pretty Portal Pagination

July 10, 2011 By Daniel

Navigation of portals in FileMaker is a pretty simple affair, you either have a scrollbar, or you don't. In this article we demonstrate another way to navigate portals using a pagination style. We also demonstrate how it can be implemented at the relationship level, or the portal level using filtering.

Pagination?

While our portal pagination is not quite as graphically fancy as googles, the basic idea is the same. We will build a back/forward button to page through portal records. We will also include first/last buttons to jump to the first or last page of records. Finally, we will give a page number box where the user can directly type in the page number of records they want to jump to.

Part One - Building the Widget

It comprises of 4 buttons, a global field for page number, and a text label that contains the total # of pages.

Now, in order to determine the total number of pages that the portal will contain, we need to know two things:

• The number of records
• The number of portal rows

The number of records can easily be obtained through a relationship to the child records being displayed. See this article for how we are obtaining the number of records via relationship.

The number of records displayed per page in the portal will have to be recorded somewhere, be it in a global variable or global field. Here we have opted to store it in a global field. Note that for this implementation we have no scrollbar and we have anchored a field in the portal to both the top and bottom of screen - therefore anchoring will expand the height of each portal row, rather than add portal rows, removing any issue of changing portal row numbers being displayed.

Using this information we can produce a calculation to determine the total number of pages:

Ceiling ( Home to Child for All Records::zRecordCount / Records To Show per Page )

Here we are dividing the total # of records, by our global field that contains the # of records to show per page. The total number of pages is now shown in the text label for reference. It also helps when navigating direct to the last page, more on this later.

Relationship-Level pagination

Filtering the relationship requires an additional relationship and field, but does have its benefits over portal-level filtering. These benefits are pretty much the same as those in any relationship vs portal level filtering debate - it easier to produce aggregates of values.

Here we have two relationships. The first is identifying the overall set of records to use in the portal pagination. The second relationship is for the portal itself which is a sub-set of the overall records at any given time - a single page of the records in other words.

Here is the first relationship in more detail. This relationship is the one you would use to locate the total records you want to use in your pagination portal. In this case we've chosen to just use every record in the child table.

Here is the second portal relationship. This one you'll notice is relating from a field called "Child IDs" to the primary key of the child table. Child IDs is a special field which contains the primary keys of a single page of records. The key here is in how this set of IDs is populated - it all ties in with the page number being viewed, total # of pages, and # of records per page, with the help of some calculations & custom functions.

Obtaining the Child IDs for any given page

The key to all of this is the GetNthRecord function. This can give us a specific field value for a specific record in a relationship. In this situation we actually wish to be given more than one record value, we want all of the values for whatever page being viewed. This is where a cool custom function comes in which uses recursion to give us all of the primary key values for all the pages records - GetNthRecordSet

Let (
[
// ------ BEGIN VARIABLE DECLARATIONS BELOW -----
startParam = recordNumStart ; 
start = Case ( startParam < 1 ; 1 ; startparam ) ; 
end = recordNumEnd ; 
thisVal = Case ( // this limits "?" result on empty related records
                         IsValid ( GetNthRecord ( fieldName ; start ) ) ; 
                         GetNthRecord ( fieldName ; start )
                        ) ; // END CASE
nextStart = start + 1
] ;
// ----------- FUNCTION RESULTS BELOW -----------
thisVal & 
Case ( 
                    // TEST 1: the next value exists to use
          IsValid ( GetNthRecord ( fieldName ; nextStart ) ) and 
                    // TEST 2: Either no end Value given or next value is within the end value
          ( ( end = "" or end = 0 ) or nextStart <= end ) ; 
                   // RESULT: Return Character & Recurse again
          "¶" & GetNthRecordSet ( fieldName ; nextStart ; end ) ;        
        ) // END CASE
) // END LET

This custom function takes as its parameters the field name to obtain values from, in addition to the start record number and end record number. For all records in between those two records (inclusive) it obtains the field values. This custom function comes courtesy of Jonathan Mickelson, of Thought Development Corp, and can be found on the Brian Dunning site here.

Knowing which record IDs from which records to get

At any given time we are viewing a particular page number, the number of which is contained in a global field. We need to take that page number, and be able to calculate a start record number, and end record number that reflects the records belonging to that page.

Let ( [
	StartRecord =  ( Page Number - 1 ) * Records To Show per Page + 1 ;
	EndRecord = StartRecord + Records To Show per Page - 1
] ;
	GetNthRecordSet (
		Home to Child for All Records::Child ID ;
		StartRecord ;
		EndRecord
	)
)

Above is the calculation we came up with, it is put in a let statement for easier reading. We determine the Start record number and end record number first. Then we simply run them through the GetNthRecordSet custom function along with the field we wish to obtain

Note above that "Page Number" is a global field that contains the specific page number the portal is showing at any given time, and "Records to Show per Page" is a global preference containing the number of total records displayable per page.

The custom function also gracefully handles the situation where endRecord has exceeded the total number of records

The end result of the above calculation is a list of child IDs which we can use to drive the portal, and display a particular page.

Controlling the Page Number

• Next/Back buttons
• First/Last buttons
• Directly entering a page number

In all of the above situations, we need to ensure that the user does not enter a page number less than 1, or a number that exceeds that of the total page count.

The easiest way to achieve this is to turn the Page Number field into an auto-enter calculation field. The purpose behind this is that whenever the page number is changed, its auto-enter calculation runs, and if the page number has been set to an unacceptable value, it can be remedied.

Case (
	// Cannot go lower than page 1
	Self < 1 ; 1 ; 
	// Makes sure page # doesn't go to a number where there would be no results
	Self > Total Pages ; Total Pages ;
	// Otherwise keep it as is
	Self
)

Scripts for Page button navigation

The script takes a parameter. This parameter is either:

• "Next" to indicate next page
• "Previous" to indicate previous page
• "1" to indicate first page
• Total Pages to indicate last page

If user wants to go to previous/next page, then the keyword is checked, and the current Page Number is either incremented or decrement - remember that the auto-enter calculation on this field will take care of any situations where page number may wish to slip beneath 1 or above the total number of pages.

In the other situations, the user has used the first/last buttons, so we just jump direct to whatever the parameter was, which in this case is the page number.

Whenever the page number global field changes, the Child IDs field also automatically changes, and thus the portal automatically updates. This is because the auto-enter calculation on the Child IDs field references the Page Number field, so any change to Page number means the Child IDs calculation is re-evaluated, quite nice :)

Using Portal Filtering

• A return-delimited list of values
• A search value

If the search value is contained in the return delimited list, then the positional index of that value is returned, if not, zero is returned.

The portal filtering calculation is thus:

Index ( Home::Child IDs ; Home to Child for All Records::Child ID )

Each portal record is checking to see whether its ID is in the list of IDs that constitute the currently viewed page. Given the index is returned, any value returned that is non-zero means the ID is in the page, and the boolean result is true. If it is not on the page, zero/false is returned and so the record does not display.

Why would you want portal pagination?

Basically, if you can think of a use for it, or it suits a situation in which you may find it useful, then go for it !

We originally used it here at Digital Fusion it on a dashboard screen for a client. The dashboard had a search box where the user could quickly lookup a clients details. Using some script triggers to produce a live search, we could display the most relevant found contacts details first on screen, and give a next button for them to flick through other possible found results.

Even if it has no real use for you, we think it is still a cool exercise in what you can do with portals :)

Taking it Further…

• A single custom function to obtain Child IDs from page number, total pages etc
• Portal-level filtering that requires no secondary relationship or fields
• Different designs of navigation widgets, i.e. using a repeating field to show a series of clickable page numbers...

Example File

Download Example File.