A Sorting Conundrum

[Steve G]

I have a value list built on the contents of a calculation field ("Quarter") which contains either "First Quarter", "Second Quarter", "Third Quarter", or "Fourth Quarter", then a space, then a year (2008, 2009, etc). So a typical value list might look like this:

First Quarter 2007

First Quarter 2008

First Quarter 2009

Fourth Quarter 2007

Fourth Quarter 2008

Second Quarter 2007

Second Quarter 2008

Second Quarter 2009

Third Quarter 2006

Third Quarter 2007

Third Quarter 2008

Third Quarter 2009

What I want is for all the years to be together, with the quarters in logical order, like this:

Third Quarter 2006

First Quarter 2007

Second Quarter 2007

Third Quarter 2007

Fourth Quarter 2007

First Quarter 2008

Second Quarter 2008

Third Quarter 2008

Fourth Quarter 2008

First Quarter 2009

Second Quarter 2009

Third Quarter 2009

So what I did was create a second calculation field ("Quarter Sort") which gives me a sorting number I can work with. "Third Quarter 2006" generates "2006 3", "Second Quarter 2008" generates "2008 2", etc.

Now I'm stuck. What I need is for FileMaker to display AND use the data in "Quarter", but sort it according to the data in "Quarter Sort". This doesn't seem possible but perhaps I'm missing something. Is there a way to do this that I'm not seeing?

[jamesducker]

..."display AND use "...

Reading between the lines, it sounds like you have discovered the feature introduced in FM8 that allows you to build a value list from two fields, and display only the second one.

Unfortunately there is no setting in the value list to make it USE the value of the second one.

You could do one of two things:

1) the hard way

a) if it isn't already so, put your list of quarters in to its own table that contains the 2 fields you mentioned

build your value list on that so it uses "2006 2" but displays "Second quarter 2006", use that VL to set the contents of whatever field in your main data table

c) make a relationship from that field to the short ("2006 2") field in the 'quarters' table

d) make a calc field on your main data field, whose definition = quarters::long description

e) 'use' that calc field.

This has the disadvantage that the field you are 'using', ie the calc field, will be unstored, so finds and sorts on it will take longer.

2) the easy way

Change your value list to be built on just one field, make the values sort themselves but still be user-friendly, ie:

2006Q3 -- third quarter 2006

2006Q4 -- fourth quarter 2006

2007Q1 -- first quarter 2007

and live with it being slightly less elegant but a ******* sight easier.

Hope that helps.