Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

Sort a portal based on on variable data


Recommended Posts

Posted (edited)

Hi All,

I have a Portal that tracks players qualification status for an end of year game. To qualify a Player must have played 8 or more games, but also finish in the top 100.

The portal contains the following fields:

Name

Games

Points.

Currently the portal is sorted by Points descending and then games descending.

What I would like to happen is that the portal is sorted in the following way:

Sort by Games (but only if Games are equal or greater than "8" 😎😎

Then Sort by Points

Then Sort by Games (for any Games less than "8" 😎😎

Then sort by points.

 

Any help would be appreciated.

 

Milky.

Edited by milky
Posted (edited)

Records can only be sorted by actual fields, not by calculation expressions.

Add a calculation field to the portal table (result is Number) =

Games ≥ 8

Then sort the portal by this calculation field first, then by Games and then by Points (all descending).


Alternatively you could use 2 filtered portals, one for the players that played 8 or more games and one for the others. Then you wouldn't need the extra field.

 

 

Edited by comment
Posted (edited)

On second thought, if you are sorting by Games before sorting by Points then all the players with 8 or more games will be sorted before players with 7 or less games, even without the added field. So I must have missed something in your description of the requirements.

 

Edited by comment
Posted

Thanks Comment. 

 

Your first solution was the correct one, as I needed Players to be sorted by:

1. Qualifiers "(greater than or equal to 8 )"

2. Then by Points.

3. Then by Games if a Player had the same amount of points, so that the larger number of games appeared earlier.

I would also like to be able to continue the sort order by:

4. Qualifiers "(less than  8 )" - This already happens thanks to your original solution.

5. Then by Games (currently it is sorting by Points at this stage.

6. Then by Points if games are the same.

I have included a screenshot.

sort demo.jpg

Posted

I am afraid the second part makes no sense to me, for the reason I already stated.

The same field cannot appear more than once in the sort order - and there is no good reason why it should. You can either sort the players by points first, then by games or vice-versa. In the first variant, players with the same amount of points are grouped together and listed in the order of the number of games they played. In the second variant, players with the same number of games are grouped together and listed in the order of their points. Any additional sorting by these fields is meaningless. If two (or more) players have the same amount of points and the same number of games, they will be listed in record creation order - unless you bring in a third field to break the tie.

 

Posted

Thanks Comment,

The reasoning behind this as you can see from the screenshot, is Players who are close to qualifying are not in Game order they are in Points order (so all Players who have played 7 games are not in the correct order), Can I create additional an field in that table based on Games, so that I may sort with this field last? 

Posted

I am still struggling to understand what is it exactly that you want. Are the players that do not qualify supposed to be sorted differently from those that do?

 

Posted

Thanks Comment,

Exactly.

Players that have played 8 or more games are sorted,

First by your suggestion - Add a calculation field to the portal table (result is Number) = greater than or equal 8, 

Secondly by Points and

Thirdly by games,

whereas Players who have not played 8 games are sorted by Games and then Points.

Posted (edited)

I see.

So the simplest solution would be to do what I already suggested earlier: split the records into two portals, one filtered by the expression Games ≥ 8 and the other by Games < 8. Sort the first portal by Points, then Games. Sort the other portal by Games, then Points.
 

If you want to keep them all in the same portal, then you could define 3 calculation fields (all with a Number result):

Sort1 (the field you already have) =

Games ≥ 8

Sort2 =

If ( Sort1 ; Points ; Games )

Sort3 =

If ( Sort1 ; Games ; Points )

and sort the portal by these 3 fields.


It is also possible to roll these 3 fields into one - but only if you set a limit to the amount of points/games a player can have. For example, you could do (result is Text) =

Let ( [
string = " 000000" ;
sort1 = Games ≥ 8 ;
sort2 = If ( sort1 ; Points ; Games ) ;
sort3 = If ( sort1 ; Games ; Points ) 
] ;
sort1 & SerialIncrement ( string ; sort2 ) & SerialIncrement ( string ; sort3 )
)

This should work for as long as no player has more than 999,999 points or games. (Caveat: not tested.)

 

Edited by comment
Posted

Thanks Comment,

Excellent solution, I will also try rolling them into one field at a later date.

Screenshot attached.

Regards

 

Milky.

sort demo2.jpg

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.