Sorting Value Lists Formed from Related Records

[GibsonCRG]

First, I have to say this board is a terrific resource; I got a long way on my problem just reading through prior posts. Many thanks to all. But, I got to one issue I couldn't seem to resolve.

I'm creating a Value List from related records, using the formula I cobbled together from prior posts here to form a text result that concatenates the related values from the "History Items" database:

Substitute( ValueListItems( Status( CurrentFileName), "History Items Value List" ) , "

[Vaughan]

The value list will be sorted according to the order specified in the original fields's storage option (define fields). However, it won;t be as dramatic adn ascending or descending, it'll only be localisation-level issues we're talking about.

It sounds like something else is happening... or is there as custom alue list somewhere?

[GibsonCRG]

> It sounds like something else is happening...

> or is there as custom alue list somewhere?

I don't think anything else is happening, and it's a very bare bones file. I think I found the root of the problem. In the FileMaker help file, it states:

FileMaker Pro sorts value list items created from values in a field

alphabetically, and duplicate values are not displayed.

This seemed to crush my hopes at first blush, but then I came up with a solution that wound up working! While no one is going to accuse me of finding the simple way to do it, here's my solution; hopefully it may do someone some good down the road (plus, I'd love to see if there's a simpler way to do it).

First, a little more background on the problem: each of my text entries begins with a TEXT representation of the date the entry was made, then the entry text itself. Dates, of course, do NOT sort well as text - December appears before February, years don't come into play, and so on. Since I need to sort with the most recent dates first, I had to generate a sort key that would work for me. In the History file, I created a SortKey field, calculated as follows:

NumToText(

Mod(

1000000000,

Year(Date)*10000+Month(Date)*100+Day(Date)+

(Hour(Time)+Minute(Time)/60+Seconds(Time)/3600)/100

)

)

This generates a number that, up through the year 9999 or so, will give a LOWER value number for the more RECENT dates. In other words, if sorted using alphabetic rules, the most recent dates will be first in the list, and the older dates will be last in the list.

Then, in my MainFile, I told it to build the ValueList with the following attributes:

- use only related items from the relationship to the HistoryItems file

- use the HistoryTextItem as the first item in the value list

- use the SortKey as the second item in the value list

- sort the values by the Second Field

There appears to be a feature (or bug, I'm not sure) in FileMaker 5.5 such that, when building a dynamic value list from related items, if you have the value list set to "also display values from", the second item is NOT included in the dynamic value list!

That's my FileMaker thought for the day.

Cheers!

..Chris..

[CobaltSky]

Interesting post, Chris! Good to hear you've found a solution - and if I may say so, a nicely lateral use of the Mod function to generate a reverse sort key...

Interesting also, how the 'bugs' sometimes save the day

[GibsonCRG]

Thanks for the good words; my early days of programming taught me that modulo arithmetic comes in handy in all sorts of odd situations.

However, I still feel a little bit bad that I've created code with a known Y10K bug!

..Chris..

[CobaltSky]

Well actually, every operating system and application I know of at this point in time is a Y10k disaster area

However, I still feel a little bit bad that I've created code with a known Y10K bug!

If you feel bad enough, you might like to make amends by campaigning for five digit dates.

You can't start too early with these things.

[BobWeaver]

Well I did create a solution once that had a 5 digit year. You can't be too careful.

Seriously, though, this looks like a good solution for sorting a value list in a custom order. I looked into doing a sorting a valuelistItems list some time ago but didn't think it was possible. Good work!