Sort items in a field

[ShawnR]

I have a field with years (it is a text field) in it, ie: 1970, 1971, 1972 1973 and so on. I have it set up as a check box field so a user can select muliple years in the field. I then have a calc field that replaces the returns between the years selected with a slash / so it is displayed better on other layouts. The problem is, if the years are selected out of order, say 1972 then 1973 and then 1970, the calculation displays them in the order they were selected. Can I change the display order of the calculation to be in an assending order no matter what order they were selected in?

Thanks for any help.

Shawn

[comment]

Try:

Substitute (

FilterValues ( ValueListItems ( Get (FileName) ; "yourValueList" ) & ¶ ; YourCheckboxfield ) ;

[ "¶¶" ; "" ] ;

[ ¶ ; "/" ] )

Edited February 13, 2007 by Guest
Removed the trailing slash

[ShawnR]

Thank you for the input. I can not seem to get this to work. Even if I just try

ValueListItems ( Get (FileName) ; "yourValueList" )

to see if it is getting the list of values, all I get is a question mark for the value. Could this be due to my value list being values from a field in another table?

Thanks

Shawn

[ShawnR]

Okay, I got it... It can not be stored due to the relationship... Have to try a different method.

Shawn

[Raybaudi]

Could this be due to my value list being values from a field in another table?

Hi

...another table of the same file ?

What's the name of your Value List ?

[ShawnR]

Yes, another table of the same file. The name of the value list is "Product Years".

The other table only has one field, which is the year. I was doing it from a field to make it more easy for a user to add other years when needed. However it seems that I will not be able to do it that way.

Shawn

[comment]

The calculation CAN be stored - although I don't see why a field used for display NEEDS to be stored.

[ShawnR]

If I check do not store, it works. I will be later searching by this field and need the search to be as fast as possable.

Shawn

[ShawnR]

I am sorry, I am not thinking right this morning. I can search on the original field, not the display field.

Cool... This should work. Thank you for all the help..

Shawn

[Raybaudi]

This calc.

Let(

string = Substitute (FilterValues ( ValueListItems ( Get (FileName) ; "Product Years" ) ; YourCheckboxfield ) ; ¶ ; "/" );

Left ( string ; Length ( string ) - 1)

)

must works, stored or not stored

[ShawnR]

I tried that calc as well with the same results. Does not work as a stored calc.

I will just search on the original field..

Thanks again...

Shawn

[comment]

Something doesn't up here. The calculation field should be in the same table as the checkbox field - so no related fields are used in the formula, and it CAN be stored. Sourcing the values from a field in another table is irrelevant.

Of course, any searches, etc. should be performed on the "real" data, i.e. the checkbox.

[ShawnR]

But the value list IS from a related field.

Shawn

[comment]

How can that be, when:

The other table only has one field, which is the year.

What then is the relationship?

In any case, even if the value list is defined to show only related values, the calculation is still NOT forced to unstored.

SortCheckBox.fp7.zip