Jump to content

  •  

Photo

Value List Sort Problem


  • Please log in to reply
13 replies to this topic

#1 spongebob  Developer

spongebob
  • Members
  • 156 posts
  • LocationBikinibottom, Ocean
  • FM Application:11 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 22h 55m 14s

Posted 26 January 2007 - 08:15 AM

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
  • 0

#2 mr_vodka  Candy is Dandy... But Liquor is Quicker

mr_vodka
  • Moderators
  • 5,917 posts
  • LocationOld Bridge, NJ
  • FM Application:12 Advance
  • FMGo:iPad
  • Platform:Windows XP
  • Skill Level:Expert
  • Certification:8, 9, 10, 11
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 9d 7h 4m 12s

Posted 26 January 2007 - 08:38 AM

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
  • 0
- John Ahn
Life3D Studios
FMfunctions.com, Co-founder

#3 spongebob  Developer

spongebob
  • Members
  • 156 posts
  • LocationBikinibottom, Ocean
  • FM Application:11 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 22h 55m 14s

Posted 26 January 2007 - 09:39 AM

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
  • 0

#4 Jeff Hannes  novice

Jeff Hannes
  • Members
  • 8 posts
  • FM Application:9 Advance
  • :

Posted 26 January 2007 - 10:52 AM

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

Attached Files


  • 0

#5 comment  consultant

comment
  • Members
  • 23,955 posts
  • Time Online: 318d 21h 25m 49s

Posted 26 January 2007 - 11:02 AM

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

2. Instead of typing 100 spaces, consider:

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

#6 spongebob  Developer

spongebob
  • Members
  • 156 posts
  • LocationBikinibottom, Ocean
  • FM Application:11 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 22h 55m 14s

Posted 29 January 2007 - 12:28 AM

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
  • 0

#7 comment  consultant

comment
  • Members
  • 23,955 posts
  • Time Online: 318d 21h 25m 49s

Posted 29 January 2007 - 07:37 AM

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.
  • 0

#8 spongebob  Developer

spongebob
  • Members
  • 156 posts
  • LocationBikinibottom, Ocean
  • FM Application:11 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 22h 55m 14s

Posted 08 February 2007 - 06:51 AM

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!
  • 0

#9 BruceR  consultant

BruceR
  • Members
  • 3,281 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12
  • Membership:TechNet
  • Time Online: 28d 6h 37s

Posted 23 October 2008 - 03:53 PM

Incorporated your calc in this adaptation.

Attached Files


  • 0

#10 bobearl  enthusiast

bobearl
  • Members
  • 30 posts
  • FM Application:10 Advance
  • :

Posted 16 October 2009 - 02:03 PM

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?
  • 0

#11 bobearl  enthusiast

bobearl
  • Members
  • 30 posts
  • FM Application:10 Advance
  • :

Posted 16 October 2009 - 03:12 PM

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!
  • 0

#12 bobearl  enthusiast

bobearl
  • Members
  • 30 posts
  • FM Application:10 Advance
  • :

Posted 16 October 2009 - 03:14 PM

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

Attached Files


  • 0

#13 bobearl  enthusiast

bobearl
  • Members
  • 30 posts
  • FM Application:10 Advance
  • :

Posted 19 October 2009 - 08:41 PM

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.

Attached Files


  • 0

#14 bobearl  enthusiast

bobearl
  • Members
  • 30 posts
  • FM Application:10 Advance
  • :

Posted 21 October 2009 - 08:22 AM

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




FMForum Advertisers