This topic is created so you can post and discuss your solutions to the 'great' challenge posted here : http://www.1-more-thing.com/Dynamic-Sort-Challenge.html

Because 1-more-thing is a very rich company that offer an incredible range of services to FileMaker developers worldwide, we decided that the first to solve this challenge should receive something BIG. This is why we put great value on the table... a box of delicious Belgian chocolate!

I hope you're going to try hard, and hopefully come up with better/crazier ideas than my own solution. It's only for fun!

Well I never had a need to sort like this and I didnt have much time but wanted to try anyway. however , this seems to work, may need slight tweek but I would appreciate any input. and if their is a better techniques i would like to see it, I can certainly use this to enhance the flavour of my own databases...

DynamicSortChallenge.zip

Well; your solution completely misses the point of the challenge. it provides one, hard-wired sort.

The point of the challenge is to provide dynamic sorting. Where the user picks a sort order and the sort is accomplished according to their selection.

E.g.

Product

Store

or

Store

Product

etc.

Given the custom functions built into the example file I imagine the solution is to use a snapshot link.

Since a snapshot link is an XML text file; and the text of the file can be controlled or substituted; then you can write any sort order you want.

Hello Fabrice,

I'am french but I do appreciate Belgian chocolate.

So , I've tried to find a rapid solution for this dynamic sorting.

I have to solve the remaining problem with my empty color lines, but now it 's to late in the evening.

Otherwise, I wanted to post my file.

This is my first approach.

It's so fun to participate to this sweet challenge.

Best regards

DynamicSortChallenge FB.fp7.zip

hi there,

@FMP12. As Bruce said, I don't see how your file solves the challenge. It's a simple hard coded sort order. Keep trying

@Gaston. Interesting unstored calc technique. I think it's working but you should probably add a function to handle various data types. What if you select a date field as a criterion ? Maybe this one : http://www.briandunning.com/cf/593. Also, your modifications on the report layout make it difficult to understand. "Color" is not what you want to see on a report. Rather "Red, Green" are the interesting summaries.

@Bruce. Hope to see your file soon ;-)

Hello Fabrice,

I'm back after some corrections.

Color line trouble is solved.

Date Range Filter is activated.

I think that it might ok...

Just waiting for few suggestions if needed ?

Best regards.

DynamicSortChallenge v2 FB.fp7.zip

Hello Fabrice,

DynamicSortChallenge_PR.fp7.zip

Hey, new ideas!

@Gaston, thanks for the clarification. It surely is interesting, definitely.

@Pihlippe, although your solution is working, I don't really like the fact that it replaces field contents. Because you're modifying the logs, and the solution is not multi-user anymore. And it's time consuming on a large foundset. Also, according to FileMaker Bible (Ray Cologon), sorts are not faster on an indexed field than on a unstored one. So all together, I think your solution is not far from Gaston's, but you're "copying" the calc result on the record, which I don't think is necessary.

So far, we've already had several solutions. On the French forum, Steve Nadeau proposed to export some xslt based on the sort criteria, then export records in xml using this xslt, and import them back in a dedicated table. I like the idea.

Here's another try (I had some times yesterday, and then some more this morning)

IThis time, it requires ScriptMaster, and it's probably only compatible on Macs (haven't have THAT much time!) (because of the file path...)

There isn't much validations and user-proofing, but hey... it's only for a box of chocolate

You have a nice day !

DynamicSortChallenge.fp7.zip

Thanks to all participants.

My solution is posted here.

Very nice!

I won't be attending Devcon this year, maybe you can send my box of chocolates back with one of the other Seattle people.

Thanks Fabrice !

Great technic without calculation in the sales table. I'm happy to discover this snapshot link manipulation.

It will make fast sort on big foundset.

Have a nice day !

I won't be attending Devcon this year, maybe you can send my box of chocolates back with one of the other Seattle people.

Sorry to hear that you won't be there.

I would certainly give it to some Seattle guy, but it's at your own risks, the one I know would more than certainly do some Q/A testing on the chocolates ;)

Thanks. Quite a risk yes!

I do have one question about your substitution method - seems to me it could be simpler.

Set variable [ $xml; // remove the sortlist

Substitute ( $xml ;

BetweenNext ( $xml ; "<SortList" ; 1 ; 1 ; "</SortList>" ; 1 ; 1 ) ; ""

)

Later, you build the new sort list and plug it back in, with some difficulty.

Why not something like this?

Set variable [ $xml; // remove the sortlist

Substitute ( $xml ;

BetweenNext ( $xml ; "<SortList" ; 1 ; 1 ; "</SortList>" ; 1 ; 1 ) ; "SORTPLACEHOLDER"

)

And then later:

Set variable [ $xml; substitute( $xml; ["UTF-8" ; "UTF-16" ]; ["SORTPLACEHOLDER"; $newSortList] ) ]

Well, why not? This is actually not the difficult part. I don't think it took me longer than typing, but surely we could do as you say.

Fabrice,

Interesting that the xml manipulation should come up now as I have just done something similar with excel xml produced from an xml export with an xlst tranform. (I know I should have changed the xslt but that was beyond me and the time I had available)

Instead of using the web viewer I created a folder in the temporary path into which I did the export. I named the file with a .txt extension so that I could use 'import folder of text files' to get the xml into a global text field before performing the substitutions and exporting the field contents.

Thanks for getting us all thinking...

Paul Jansen