sorting pulled fields from external db file
I'm using the remote "categories" rows inside of the main "projects" database. I can successfully see a checkbox group made up of categories on every projects records.
I also have a "ratings" field on every category entry. I'd like to be able to sort the imported category like:
outside jobs : 1
inside jobs: 3
So far, filemaker is only sorting alphanumerically. I've set Define Database>Relationships>[clicked on the equal sign between two tables]>Edit Relationship>Sort Records: Sort on Priority field. This does not work.
I'd rather not hardcode 01,02,03 into the categories, because I'd like the calculation ability later down the pipe.
Value lists only sort alphabetically. Comment posted a trick that works for fairly small numbers of values in your value list.
Add a calculation field that returns space characters equal to the "rating" value:
Substitute ( 10^Rating - 1 ; "9" ; " " )
Make this field the second column value in your value list and specify sorting on the second column.