Sort the contents of a field?

[nycpost]

Is there a way to sort the contents of a field?

I have a value list of numbers (001, 002, 003, etc.), displayed as a checkbox in a field. For each record, I check off the values that apply. I then export this field (along with others) via XML and an XSLT to transform it into a tab delimited ALE file. The problem is the carriage returns in this field mess up the formatting of my tabbed file.

As a solution, I've created a calculation field which pulls the values from my checkbox field and Substitutes the carriage returns for underscores, so I end up with a text string. This field exports beaufitully as tab-delimited and works great.

The only problem now is, when I'm busily checking off values in the original field, if I UNckeck a value (it gets removed from the text string in the calculation field, which is fine), but then if I REcheck that same value later, it gets placed at the end of the list so it's not in numerical order.

I'm wondering if there's a way so the numbers always remain sorted in the calculation field's text string. I tried using List () to always put the values in order, but that didn't have any effect.

Anyone have any ideas?

[comment]

You could set the checkbox field to auto-enter a calculation (replacing existing value) =

FilterValues ( ValueListItems ( Get (FileName) ; "YourValueList" ) ; Checkboxfield )

I suppose you could also sort the values during the export - though that might be a bit tricky since these are strings, not nodes.

[nycpost]

Comment, thank you! That works brilliantly.

[dysong3]

how would you adapt this to sort the contents of a field that does not have a value list?

[Raybaudi]

Could you give us a real example of the contents of your field ?

[jbante]

how would you adapt this to sort the contents of a field that does not have a value list?

 

You can use a custom function to sort the field contents, like this one for text, or this one for numbers.

[dysong3]

Thanks for that.  But I'm not quite out of the woods yet.  Here's an example of the fields contents I would like to sort, and also possible eliminate duplicates.

 

1894

1547

1548

1917

2727

1584

11482

2254

1634

1892

 

When I try to create a custom function or define an auto-enter calculation with the formula from 

 

 

 

this one for numbers

 

the first parenthesis after ValueSort halts the calculation verification.

 

 

 

] ;

            ValueSort ( valueList )

        ) ;

 

I also have another question.  I imagine that "value list" is the field I want to sort, but what do I need to enter for "ValueSort"?  Sorry to be a dummy. Thanks for your help.

[jbante]

To also remove duplicates, you can apply a separate custom function to remove the duplicates before sorting the list. You'll be able to find several at BrianDunning.com.

NumberSort ( RemoveDuplicates ( Self ) )

You'll probably get better performance if you remove duplicates before sorting, as shown here. In an auto-enter calculation as you've described so far, you can use "Self" to reference the field the auto-enter calculation is for.

 

I'm surprised that you're having trouble copying the NumberSort function into your file. I tried it just now, and FileMaker accepted it. I notice that the link you say you copied from is for the NumberSort function, which does not reference ValueSort at all; but the calculation excerpt you quoted does reference ValueSort. Can you take a screenshot of the situation showing both the calculation dialog and the error message you're getting?

[Raybaudi]

What about take advantage of a FileMaker's built in function ?

Sort Field.zip

[jbante]

What about take advantage of a FileMaker's built in function ?

 

If you're following the thread, you know that using the value list sort behavior was already suggested, and that the custom function method was suggested in response to a request to accomplish the effect without a value list.

[Raybaudi]

@ jbante
 
I have carefully read the thread and it seems that he has not told that he doesn't want to use a value list, but just that he does not have a value list

[dysong3]

@ Raybaudi

 

Thanks a lot.  Your example file allowed me to understand how a value list works in this instance.  I was able to incorporate your method and it does exactly what I was hoping for.  Thanks a lot.

 

@ jbante

 

Thanks also.  I'm still intrigued as to why I could get the custom function calc to get passed verification.  Here's a screen dump of where the first hurdle is.  If you have any idea why it's getting stuck there, I would love to know.

 

 

 

 

[bruceR]

Dysong3: your screen shot would be more useful if you moved the error dialog before taking the screen shot.

From what we can see, you have not named the function yet. And you have defined two arguments for the function but it is not clear what you have named them.

[dysong3]

Ooops Sorry.  There you go.

 

 

 

.

[jbante]

In both your screenshots, the name of the function you're defining in the header (under "Function Name") doesn't match the name of the recursive calls to the function in the calculation. You've also defined two parameters for the function in both screenshots, but the ValueSort and NumberSort functions both take only one parameter. Both of these issues will cause the calculation to fail verification or otherwise not work.

[bruceR]

That is - it should look like my screenshot.

[dysong3]

OK. Great.  I've resolved that too.  Thanks very much.