Portal paging

[The Learner]

Hello all,

I have a small accounting solution with two tables: Dashboard and Expenses. On a layout based on the dashboard, I have a 10-row portal that displays all expenses. I'd like to be able to move from the first 10 rows to rows 11-20, and then 21-30, etc., and back, by simply clicking two arrows, so that there is no longer a need for the scroll bar.

Can someone guide me through this?

Thanks.

  

[comment]

How is the relationship between Dashboard and Expenses currently defined?

 

[The Learner]

the relationship is: Dashboard::Zkp = Expenses:ZkfDashboard

[comment]

I am afraid that doesn't tell me much. What are those fields and how are they populated?

[The Learner]

Good Day,

Dashboard::Zkp is a number field with a serial number generated on the creation of a new year, and a new year is created by script (the dashboard table currently has five records, for years 2019 to 2023).

Expenses:ZkfDashboard is simply a number field.

"Allow creation of records in this table via this relationship" is checked on the "Expenses:ZkfDashboard" side of the relationship.

[comment]

I see.

Attached is a basic demo showing how you can build a paged portal in a parent/child relationship.

There are several ways this could be made smarter, e.g.:

• reset the page number to 1 when switching to another parent record;
• use a variable instead of the global field (this would also allow you to use a different repetition for each parent record, so that each record "remembers" its page number;
• prevent the script from exceeding the minimum and maximum page number (and grey out the buttons when they reach their limit).

I have purposefully left all of these out so that the underlying method remains clear.

 

PagedPortalDemo.fmp12

Edited October 12, 2023 by comment

[The Learner]

Thanks very much. I'm going to get to work and try to implement this in my solution.

[The Learner]

Good Day,

It works fine, except that the descending order in which the portal was sorted previoulsy is lost. Can the portal filter be tweaked so that the List of IDs is processed starting with the highest one? Is this the right approach?

Thanks.

[comment]

The easiest solution would be to sort the relationship. Then the list of IDs will be already sorted and the existing portal filter can process it with no change.

Alternatively, you could apply the SortValues() function to the received list to reverse it. But that can only work if the child IDs are serial numbers, not random UUIDs. And you would also need to sort the portal, so that not only the page order is reversed, but also the order of the records within the page.

If neither of these can work for you, then it gets a bit more challenging: instead of calculating how many records to skip from the top of the list, we need to start with the total number of related records and work backwards from there - so the filtering expression would become something like:

Let ( [
portalSize = 5 ;
setIDs = List ( Child::ChildID ) ;
startingValue = ValueCount ( setIDs ) - portalSize * Parent::gPortalPageNum + 1 ;
endingValue = startingValue + portalSize - 1 ;
pageIDs = MiddleValues ( setIDs ; startingValue ; Min ( endingValue ; portalSize ) )
] ;
not IsEmpty ( FilterValues ( Child::ChildID ; pageIDs ) )
)

The reason why this is more complicated than would seem warranted is a quirk of the MiddleValues() function, where:

MiddleValues ( text ; -1 ; 5 )

returns the first 5 values instead of 3 as one could expect.

 

[The Learner]

Sorting the relationship looks like it does the trick. So let's leave it at that as far as the sorting is concerned.

But I'm very much interested in the third point you listed this morning in your first post:

6 hours ago, comment said:

prevent the script from exceeding the minimum and maximum page number (and grey out the buttons when they reach their limit).

Would implementing this require a lot of changes?

[comment]

Roughly:

Calculate the target page number as: 
gPortalPageNum + Get (ScriptParameter)

Calculate the last page number as: 
Ceiling ( Count ( Child::ParentID ) / $portalSize )

If the target page number is between 1 and the last page number (inclusive), set the gPortalPageNum field to the target page number. Else beep or do nothing.

Apply the same rules to conditional formatting of the buttons: grey out the left arrow when gPortalPageNum is 1, and the right arrow when gPortalPageNum is equal to the last page number.

 

[The Learner]

Good Morning,

So the script now does not exceed the minimum and maximum page number. This issue is resolved.

As for the conditional formatting, it works fine on the left arrow but for some reason that I can't manage to identify, the right arrow behaves differently. The portal on which I'm testing this has 18 pages. When I reach page 18, the right arrow button does turn to grey but as soon as I move the cursor away from it, it loses its formatting and goes back to white. Any idea what can explain this?

The formatting condition on the right arrow is : DSB_Dashboard::gPortalPageNum = $lastPage. And below is the script.

 

[comment]

2 hours ago, The Learner said:

When I reach page 18, the right arrow button does turn to grey but as soon as I move the cursor away from it, it loses its formatting and goes back to white. Any idea what can explain this?

The scope of a script variable (prefixed with a single $) is limited to the duration of the script. So while the script is running, the $lastPage variable is equal to 18 and the condition is true. When you move the cursor away from the button, the button is redrawn (because it changes its state from Hover to Normal). But at that time the script has already run out and the $lastPage variable no longer exists.

You can test this by adding a pause of a second or two to your script. Then you will see that the button will remain grey when you move away from it, and return to normal only when redrawn again.

A possible solution is to make the variable global (prefixed with  $$). But then you need to consider what happens when you switch to another record, where 18 may not be the last page number. Because a global variable persists until the end of the session (or until you cancel it by setting it to empty).

 

[The Learner]

Thanks for your very clear explanations.

I have found a workaround. I've created a text field LastPageNumber in the Expenses table, added a script step that sets that field to $lastPage and changed the formatting condition to DSB_Dashboard::gPortalPageNum = Expenses::LastPageNumber. And so even when the $lastPage variable ceases to exists, the text field LastPageNumber keeps its value.

Thanks again for all your help.

[comment]

If you want to use a field for this, then it should be a calculation field defined in the Dashboard table. And the result type of the calculation should be Number.

[The Learner]

Perfect. I've made these changes. But just so I understand the rationale, why should the calculation field be in the Dashboard table?

[comment]

27 minutes ago, The Learner said:

why should the calculation field be in the Dashboard table?

Because it needs to count the number of related records in the child table, from the context of the current parent record.

 

[The Learner]

Thanks very much.