custom portal sort from value list of related records

[shaker]

Hello,

I'm having a problem with a custom portal sort. The sort is based on a value list that is made up of related records from another table.

I have created a table of Shakepearean productions. On the main layout is a portal that contains a list of actors and the characters that they play. For example in a production of Shakespeare's 'A Midsummer Night's Dream' I have a list of actors playing various roles including Hippolta, Oberon, Theseus, Titania etc. I want to show the actors that play the most important roles first so I have another table where each record contains a role name and a play id that is used to link it with records in the table of productions. The problem is that although the related roles apear first, they are sorted alphabetically instead of by creation order. Is there any way to change this? I have tried defining a sort order in the relationship between the production and roles tables so that roles are sorted by their serial ids, this works if I use it as an input value list but has no effect upon the sort order of the portal records.

Any help appreciated.

[Fitch]

In FileMaker 7 and 8 you can (and generally should) define the sort directly in the portal options rather than in the relationship.

[shaker]

Hello,

Thanks for your response.

Maybe I am not explaining my problem clearly. The sort option is defined in the portal. It is defined as a custom sort. Please have a look at the attached example, it contains data about productions of one play 'A Midsummer Night's Dream'.

custom_sort_example.fp7.zip

[comment]

Sorting by 'Custom order based on value list' works only with value lists using custom values.

I believe you can still achieve what you want by modifying your structure a bit, for example like this (attached).

[Fitch]

You can make this work with something similar to your existing structure, along the lines of comment's solution.

1. Make another TO on your graph of "top5roles" and connect it to performers, role to valuelist; I'll call the TO "perform roles"

2. Make a field in performers I'll call "role sort": a calculation that is simply:

perform roles::id

3. Sort the portal on this new field.

You'll notice a problem: the records in the portal with no role sort id will be at the top. But we want them at the bottom. The simple solution is:

4. Make a value list

0

1

2

3

etc

... and sort the role sort field with a custom order based on this list.

[shaker]

Many thanks to you and to Comment for your feedback.

I've now changed the database structure and implimented the steps that you describe with the exception of step 4.

There are over 1200 roles across all the Shakespeare plays so I thought that a custom value list of this size might slow things down. Instead I changed the "role sort" calculation to give any performer records that are not linked to a role an arbitrary high value above 1500.

Edited October 17, 2006 by Guest