Value List Sort Problem

[spongebob]

Hi forum!

This must be so easy it must be a no brainer...but Ive now spent 2 hours trying to do it to no avail.

I have one Table like this with two fields

sortorder and menutext like this

1 -nothing selected-

2 zzz Entry

3 aaa Entry

4 ddd Entry

I want to make a value list from the text items, but one that shows in exactly the order above.

I need the table so I can add in new menu items easily so I cannot use a straight "fixed value list".

I want my Value List to display like this when I click on my field:

-nothing selected-

zzz Entry

aaa Entry

ddd Entry

it however displays:

-nothing selected-

aaa Entry

ddd Entry

zzz Entry

all the time no matter what relation I create or if I show all values. How can I "order" my value list by SORTORDER WITHOUT displaying the field SORTORDER in the valuelist as well?

Surely this can be done no?

Spongebob

[mr_vodka]

When you reference a field for a value list, it will sort it alphabetically, as opposed to a custom value list where it lists it as entered.

Take a look at this thread. You may be able to implement the technique for your solution.

See here

* Edit Sorry misread post

[spongebob]

Hi thanks for your response;

I checked the link; theres no discussion of sorting value lists if I see that right...

did you point to the right link? How may that one help?

Thanx

[Jeff Hannes]

That's a nice little puzzle...as with many things like this in FileMaker, you need to get creative and think a little outside the box to get the program to do what you want. There's probably more than one way to do this, but here's what I came up with...

I used the fact that later versions of FileMaker now index spaces, so "_" sorts before "__" which sorts before "___", etc. (Assuming _ is a regular space.)

In your Values table set up a third, calculated fields that is simply a number of spaces equal to the sort number. The code for that looks like this:

Left("                                     "; SORT_NUMBER)

Just make sure you put enough spaces in to cover as many entries as you might add over the course of time.

Then for your value list, make it two columns...Use your VALUES first, and the SPACES second, and sort by the second column. The user won't be able to see the spaces when they select a value from the drop=down, but the spaces will control the sort order so it comes out the way you want.

I've included a quick sample solution, hope it solves your problem.

- Jeff

VALUE_SORT.zip

VALUE_SORT.zip

Edited January 26, 2007 by Guest

[comment]

1. Keep in mind that this is limited to approximately 100 values;

2. Instead of typing 100 spaces, consider:

Substitute ( 10^SortOrder - 1 ; "9" ; " " )

[spongebob]

Hi Forum,

thanks for all these marvellous suggestions however; I had also actually already come up with exactly that solution...problem is though that I have about 250 values in the value list. Also, I think that the menu will get wider with many many spaces. In fact it will get too wide.

I find it hard to believe that with all the options that exist to sort value lists up and down and across the board, filemaker didnt think of this, that one really wants a custom sorted value list.

Filemaker: Why can we display one field, sort by the second, but only SHOW the first field please?

Anyone who has any other suggestions Id be very happy to hear them...

Yours Truly

spongebob

[comment]

I haven't noticed a problem with the width of the field. But 250 values is too many - not only for this workaround, but for a menu in general.

I suggest you study the examples posted by Fenton here.

[spongebob]

Okay after alot of messing around I finally opted for the "display the options in a new window using a seperate layout and related table" approach. Works well, also in find mode.

Thanks to all for your help!

[bruceR]

Incorporated your calc in this adaptation.

valuesort.zip

[bobearl]

This is an incredibly powerful and useful tool that Michael and Bruce came up with. It is a solution to a similar problem I had. I have one additional question as a neophyte. How do you all recommend annotating this fix in a solution? I can fix my problem here, but what is the standard way to mark it up so that the next person who takes a look at this (even me!) doesn't wonder what the heck the calculation field is for?

[bobearl]

I hit one additional snag - the SORTtable that Bruce and Michael made with the calculation works for the dropdown selection lists. However, when I try to use the same Value List to sort a portal, it doesn't seem to work. I'm attaching a file, in this case trying to sort fruit in a bowl by size. When I use the calculation for the value list for size when choosing a size in the Fruit layout, the dropdown works flawlessly. When I try to sort the portal, however, it only sees the size value from the fruit table and reverts to alphabetical order. Is there some clever way to make the fruit in the portal appear sorted by size. Pardon my being so thick!

[bobearl]

Apologies, first attempt to upload a file didn't go well. Par for the course for me.

BowlFruit2.fp7.zip

[bobearl]

I am still looking for any advice on portal sorting in a value list. I have made slight progress since my prior posts, but am stumped and would welcome advice.

As it stands, my example file does allow me to select a size from a correctly ordered value list, and then displays correct options from the conditional value list in the name field within the portal. What I cannot figure out is how to get the portal to sort in the same order as the value list, and to aggregate multiple name values for each size.

This model, which is quirky, is to allow users to describe something with checkboxes; this model would be useful if the user were asked to catalog types of fruit in a fruit bowl by size. I've attached the fp7 file as well as a mockup the desired output. Any assistance would be greatly appreciated.

BowlFruit5.fp7.zip

desired.pdf

[bobearl]

Any help at all with the above would be incredibly appreciated!