Bringing VirtualSort (by J. Bante) to work?

[gczychi]

Hi,

 

I've tried for some time now to install the VirtualSort module from Jeremy Bante and I am asking if someone familiar with this module can help me.

 

I have followed the steps to the letter but when I click in the header buttons the portal won't get sorted; the only thing that changes is the function value that changes (VirtualSortFieldSorted ( Beleg::SortID ; "" )) => returns True for the correct column names.

 

Here is my setup:

Enclosing table/layout:  Jahr

Portal table: Beleg

Unique id fields (both UUID):

Jahr::id

Beleg::id

Default sort field: Beleg::SortID

Optional sort fields:  Beleg::Text, Beleg::Betrag

 

My installation:

1.1  I imported the "HyperList" and "Virtual Sort" script folders, into the "Modules" folder.

1.2  Imported the custom functions (using FMPA 13)

1.3  Created an unstored calculation field:  Beleg::virtualSort01 with the following calculation:

       VirtualSortPosition ( id ; SortID )

1.4  Defined buttons on layout Jahr, above portal Beleg:

       For the SortID column:  "Virtual Sort by SQL with New Field", with the following script parameter:

"$primaryKeyName = " & Quote ( GetFieldName ( Beleg::id ) ) & " ;¶"
& "$sortFieldName = " & Quote ( GetFieldName ( Beleg::SortID ) ) & " ;¶"

       For the Text column:  "Virtual Sort by SQL with New Field", with the following script parameter:

"$primaryKeyName = " & Quote ( GetFieldName ( Beleg::id ) ) & " ;¶"
& "¶$sortFieldName = " & Quote ( GetFieldName ( Beleg::Text ) ) & " ;¶"

1.5. Used the VirtualSortFieldSorted ( field ; sortID ) function in the Data Viewer window to maybe find a clue on what goes wrong:

When I click on the SortID column on the layout, VirtualSortFieldSorted ( Beleg::SortID ; "" ) returns 1,

When I click on the Text column on the layout, VirtualSortFieldSorted ( Beleg::Text ; "" ) returns 1, and vice versa.

 

2.1. In the portal definition, I have checked the "Sort portal records" option and sorted the portal by the unstored calculation field:  Beleg::virtualSort01

 

2.2. For the layout containing the sorted portal (Jahr), I have added a script step to my OnRecordLoad script trigger that calls the "Refresh Virtual Sort by SQL:  with the following parameters: 

"$primaryKeyName = " & Quote ( GetFieldName ( Beleg::id ) ) & " ;¶"
& "$sortFieldName = " & Quote ( GetFieldName ( Beleg::SortID ) ) & " ;¶"

3.1. I have not yet created an onModeEnter script step yet. I first wanted to see how the module works.

 

 

Anyone any clues why this doesn't work?

 

Thanks a lot for any help!

 

Gary

 

 

[bruceR]

It would help if you added your example file as an attachment.

[jbante]

As Bruce said, seeing the file your working with would help diagnose the issue. Without that, I do see a couple points worth looking at.

 

 

1.3  Created an unstored calculation field:  Beleg::virtualSort01 with the following calculation:

       VirtualSortPosition ( id ; SortID )

 

The second parameter of the VirtualSortPosition function should not be the field being sorted. To Virtual Sort, the "sortID" is a number to distinguish between multiple things being sorted at the same time, like two sorted portals on the same layout, or in different windows. You should pass the same value you use for the VirtualSortFieldSorted function and for the script parameters. It defaults to 1 if you leave it empty. This might be your main problem.

 

2.2. For the layout containing the sorted portal (Jahr), I have added a script step to my OnRecordLoad script trigger that calls the "Refresh Virtual Sort by SQL:  with the following parameters: 

"$primaryKeyName = " & Quote ( GetFieldName ( Beleg::id ) ) & " ;¶"
& "$sortFieldName = " & Quote ( GetFieldName ( Beleg::SortID ) ) & " ;¶"

 

For that trigger, you don't necessarily need to pass the sortFieldName parameter. I normally leave it blank for that trigger so that when users switch between records, the portals stay sorted by the same criteria. By specifying the sortFieldName for the OnRecordLoad trigger, Virtual Sort will reset the sort to your default every time the user loads a different record.

 

3.1. I have not yet created an onModeEnter script step yet. I first wanted to see how the module works.

 

The OnModeEnter trigger was a convention I came up with for sorting found sets of records, not portals. If you're only sorting portals, there's no reason to add an OnModeEnter trigger for Virtual Sort.

[gczychi]

Jeremy,

 

omitting the second parameter in step 1.1 did the job!  Well done.  The calculation field now reads like this:

VirtualSortPosition ( id ; "" )

It was not clear to me what the second parameter did and when to use it.

 

 

Thank you very much for this solution, and for all your contributions; your work for the FM Standards group, to name my favorite.

 

Thank you,

 

Gary

[gczychi]

There is one more thing  ;-)

 

How can I retrieve the current sort order and how can I tell virtual sort to sort by two columns  —  from within a script?

 

 

What I need to do: depending on what is currently displayed in the portal, I need to change the sort order. Something like this:

 

1)

If Jahr::Ursprungskonto_fk = 1000
// the foreign key field that decides which subset is displayed in the Portal

and not GetCurrentSortOrder ( ) =  Beleg::Valuta ; "asc" ; Beleg::Betrag ; "desc"
//  need to determine the current sort order

2)

SortPortal ( 1 ; Beleg::Valuta ; "asc" ; Beleg::Betrag ; "desc" )
//  need to set a (new) sort order of two columns

It only worked when I manually fired three scripts, one after another, the first one sorting by Valuta (asc), the second one sorting by Betrag (asc), and the third one by Betrag again (desc). It didn't work when I called these three in one script.

 

 

Thank you very much for any help!

 

Gary

[jbante]

The module includes 3 custom functions you can use to determine if any one field is part of the sort order:

 

VirtualSortFieldSorted ( field ; sortID )

VirtualSortFieldSortedAscending ( field ; sortID )

VirtualSortFieldSortedDescending ( field ; sortID )

 

If you need more detail, reverse-engineer the contents of the $$VirtualSort.ORDER_BY[sortID] variable, which indicates the field names, order, and hierarchy for a sort.

 

To script a particular sort, call the "Refresh Virtual Sort by SQL ( primaryKeyName { ; orderBy ; sortID } )" script, and specify the $orderBy parameter when you do. Format the order the same as it appears in the $$VirtualSort.ORDER_BY[sortID] variable.

[gczychi]

It took me some time to understand what you are doing with the Parameters, but then I got it. This is the way to specifically call VirtualSort in order to sort by two fields :

Refresh Virtual Sort by SQL ( )

"$primaryKeyName = " & Quote ( GetFieldName ( Beleg::id ) ) & " ;¶" &

"¶$orderBy = " & Quote ( GetFieldName ( Beleg::Valuta ) & "" & ¶ & 
GetFieldName ( Beleg::Betrag ) & "::DESC" ) & " ;¶"

After each field, you can optionally include the sort order "::DESC".  I assume that "Ascending" is default for each field and should not explicitly be specified in the parameter string (hence the empty quotation marks).

 

 

Thanks a lot,

 

Gary

[jbante]

After each field, you can optionally include the sort order "::DESC".  I assume that "Ascending" is default for each field and should not explicitly be specified in the parameter string (hence the empty quotation marks).

 

Correct.