sorting value list i

[Vincent Koh]

Hi everyone, I believe mine is an old and easy problem and should have been covered in some of the earlier post. But I have search thru this for weeks and don't seems to find anything near the solution I am looking for. So I really hope to get some help here. 

 

I have a dropdown box which I want to populate with fields from the same table.

 

Table : Items

 

First Field

 

     ItemID : 1,2,3,4,5,10

 

Second Field  :

     ItemName : BreadFast, Lunch, Dinner,Soccer, Bank Interest, Others

 

Create New Value List - Under File/Manage/ValueList

 

Now what I want is for the dropdown box to display the values in the order as I have type above - (ref : second field). 

 

If I choose "show only value from the second field",  the order that appears in the dropdown list becomes Breadfast, BankInterest, Dinner, Lunch ,Others  and soccer. Because by choosing the option to display value in the 2nd field I am also choosing to sort the second field -which is not what I want at all.

 

The only way to display the data in the sequence that I want it is to choose "Include All Values" and choose sort by the first field. But this will also display the ItemID  which is not want I want either. 

 

So my question is :

 

How can I choose the option - "show only value from the second field" without FM sorting it alphabetically?

 

Really hope someone can help me figure this out. 

 

Thanks.

 

[dansmith65]

Actually, I'm not so sure this is possible.

[Brooks]

This is absolutely possible although not with out of the box options. There is always multiple ways to accomplish anything in FM however context is key, and without knowing your larger picture its hard to tell you what is best, so ill point you in 2 directions.

Option 1 is to use a global field to capture a selection from a static value list. Then use a script to look up a matching record ( ExecuteSQL ) and store the Id.

Option 2. Use a sort field and a calculated field to append the name field with a Byte order mark. It's an invisible character that will be sorted. This method is about more in depth and you can learn more here. http://www.soliantconsulting.com/blog/2012/09/extending-filemaker-pro’s-value-list-sort-capabilities-using-char-function

[Mr. Scott]

Actually, Matt Petrowsky covered this in a tutorial video on his site, titled "User-Based Value Lists", which uses a custom function, a "values" table, and a "Sequence" field used to sort the value list.

 

Let us know what you discover and use, please.

 

- - Scott