sort values in List using a calc

[ESpringer]

Dear friends, nice to see so many familiar faces here (LaRetta, Lee, Vaughan, Ocean West...) after being away from my Developer Hobby for years...

 

I've been compiling the index for my book, using three main related tables for

 

* flagged locations in text

* terms to be indexed

* subheads under main terms

 

I've successfully used the List function to get each term-entry record to include a list of all related subheading strings (where each subheading string includes a comma-separated set of associated page runs). So I can now output something that looks quite close to the formatted index I need...

 

HOWEVER, the List function is returning a poorly-ordered list (neither alphabetical by subhead nor numerically ordered by page -- I'm not sure what's doing the sorting, perhaps record creation order, but even that doesn't quite make fit what I see). 

 

I've attempted to solve the problem by making a Value List (which I at least know how to sort), and then using a calc to show ValueListItems... but such a value list "won't work" in this case (says the alert dialog) because the calculated subhead text strings (the things I need to alphabetize) refuse to be indexed. (Also, I've had a rough time getting the ValueListItems function to behave as I wish it would, in general... even when a value list appears correctly in a drop-down menu, displaying the whole list on a layout, using that function, is giving me spotty output.)

 

Isn't there a simple function with which FileMaker will take a list of ¶-delimited values and spit out a sorted version? (Am I not seeing something obvious?  )

[LaRetta]

Hi!  It is nice to see you back.

 

I am too visual and I'd need to see graph or something specific but one thing to consider is that the List() function returns values in the order defined in the relationship (graph's sort by).  Also what version are you using?  Well, even if version 11, you can use a dynamic value list whereas the child side can be unstored.  Both Comment and Mr_Vodka have demos here for 11 and 12.  Also consider that a temp table (search for virtual list) might come in handy.  Sorry I can't search right now.

 

ADDED:  Oh and of course check out http://www.filemakerhacks.com

 

Kevin goes into some depth on the use of these techniques and others relating to value lists.

Edited February 27, 2013 by LaRetta

[Rick Whitelaw]

Hi!  It is nice to see you back.

 

I am too visual and I'd need to see graph or something specific but one thing to consider is that the List() function returns values in the order defined in the relationship (graph's sort by).  Also what version are you using?  Well, even if version 11, you can use a dynamic value list whereas the child side can be unstored.  Both Comment and Mr_Vodka have demos here for 11 and 12.  Also consider that a temp table (search for virtual list) might come in handy.  Sorry I can't search right now.

 

ADDED:  Oh and of course check out http://www.filemakerhacks.com

 

Kevin goes into some depth on the use of these techniques and others relating to value lists.

Thus far I have never been able to get the List() function to sort by the relationship.

Rick.

[LaRetta]

Hi Rick,

 

I didn't explain it very well. Here is a sample file (created in 12 and I should have created it in 11 since the functionality is the same), but I initially wanted to eliminate the possibility that it was a version difference.  We still may have an OS difference (in behavior) so it is worth checking out.  Probably however, I didn't explain it very well.

ListSort.zip

[jbante]

If all else fails, here's the custom function I use to sort return-delimited lists alphabetically:

 

https://github.com/jbante/FileMaker-Techniques/blob/master/CustomFunctions/Value/ValueSort-TimSort.fmfn

 

And this one sorts numbers:

 

https://github.com/jbante/FileMaker-Techniques/blob/master/CustomFunctions/Value/NumberSort-TimSort.fmfn

[doughemi]

Thanks for those, Jeremy.

[ESpringer]

Thanks all (and sorry I wasn't around to acknowledge quickly).

 

Jeremy, that did the trick and FAST -- and it's why I thank my former self for springing for FMP Advanced! (I'm just making the decision whether to go 12 Pro or 12 Advanced after trial expires and this helps nudge me over the top )

 

For LaRetta: Here's an image of: at left, the effect I need (as produced by Jeremy's Custom Function). At right, the tables for the index...

 

[ESpringer]

OK, now I'm really pushing my luck... but there's one niggling detail I had resigned to handling in a word-processor after exporting the data -- but perhaps Jeremy's function could be tweaked (for which I'd gladly pay an hour's wage!):

 

The detail is that the list should be sorted by publisher's standards. There's a set of words (conjunctions and prepositions including and, as, in, of) that should *not* interfere with the alphabetical order of whatever comes afterwards. 

 

So, the image I posted isn't quite right. It needs to read more like this:

 

blame

  accounts of the aim of...

  disadvantages of focus on...

  embraced by utilitarians...

  and expressive function...

  manner of enacting...

  and moral judgments...

  presumed practical efficacy of...

  as second-order responsiveness...

  unexpected outcomes of...

 

If by chance Jeremy or anyone else sees how to tweak the custom function, great. Otherwise, it's a job to ask someone to do manually, painful as that is. Sigh. :|

[jbante]

I've considered the possibility of writing a third version of the function that could accomodate the behavior you're describing. Suppose you codified all of the publisher's sorting standards into a custom function of your own. For example:

 

// PublisherOrder ( value1 ; value2 )
// Returns True (1) when value1 should sort before value2 according to the
// publisher's standards; False (0) otherwise

Let ( [
	~derivedValue1 =
		Substitute ( value1 ; [ " of " ; " " ] ; [ " an " ; " " ] ; ... ) ;
	~derivedValue2 =
		Substitute ( value2 ; [ " of " ; " " ] ; [ " an " ; " " ] ; ... ) ;
] ;
	~derivedValue1 < ~derivedValue2
)

 

This function would be enough to define what "sorted" means for an entire list. It would be easy to modify my sort function (or any other, for that matter), to accept this function or some other calculation as a parameter, and use it with the Evaluate() function to replace the behavior of FileMaker's native "<" operator:

ValueSortByCalculation ( List ( "accounts of the aim of..." ; ... ) ; "PublisherOrder ( [value1] ; [value2] )" )

 

It will be slower, and it may not pass as tail recursive by FileMaker's standards anymore, but it should still work. I don't have time to implement this right now, but I'll take a look at it tonight.

[ESpringer]

Wow, Jeremy!

 

Yes, something like sorting based on a derivedValue variable is what would be needed...

 

Two thoughts: (1) A separate table of "fluff words" (prepositions and conjunctions) would allow prepositions to be added on the fly as they're encountered... there probably are less than a dozen, but it would make the solution more transparent if the list of words were accessible...

 

(2) a calc field could display the variant of the modifier string with fluff words removed. So, the idea would be "return all values from field 1, but ordered according to an alphabetical sort on field2"... Would that be any simpler to implement?

 

Actually, if I could figure out how to get LaRetta's angle to work... that is, getting List to sort based on Relationship order... then perhaps I could see this alternate sorting through without more custom function magic... But I suspect FMP will throw up objections to having t his relationship sorted on a calc field that it refuses to index...

 

Alas, I don't have 12-advanced (just 12 pro trial), so working on the relationship-sort option means playing on a sandboxed transformation of my file that can't also benefit from Jeremy's custom function...

[ESpringer]

Holy smokes, LaRetta, I think it WILL work -- I'm now getting the List to sort according to the relationship's sort order, having made that sort order dependent on a calc field in which all the fluff prefixes get Substiuted by "".

 

So far, looking good!!