pre-sort export

[kimrussell]

I'm interested in sorting certain portions of my database during the export process...if possible. Example: I have a field that has 5 check boxes. As it stands now, the checked boxes will export in the order that they are checked, but I want them exported in a fixed order. So if boxes 4, 2, and 5 are checked in that order, I would like them to output as 2, 4, 5.

Also (this is a good one), I'd like to be able to print out what each number represents. So like if 2 is L, 4 is A and 5 is V, then, using the example above, I would like 4, 2, 5 to export as L, A, V. I dream bigger than I'm able to accomplish. If someone could point me in the direction of what I should be doing, your help would be greatly appredated.

[Fenton]

You can sort entries alphabetically, by using a self-relationship on a unique serial ID, and the ValueListItems (Get(FileName), "value list name") function; in an unstored calculation field.

[The below is not in the attached file. Too tedious :-]

As far as the 2nd part, it could be done with a hard-coded calculation, if the values are local. One has to wonder why you don't just pick the 2nd field instead?

I first wrote this in 7, where I could use the Let() function, or RightWords(); then rewrote it so it would work in 5 (where it's clunky). The original value list is: one, two, three, four. This is how you'd produce a 2nd sorted list from those choices, from a checkbox-formatted field name Text1. The repetition is just to remove a 1st carriage return, if necessary. It could be eliminated if you're sure your choices can't overlap in any way. PatternCount is not fussy about overlap, so the extra returns preclude that happening.

Case (

Left (

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "one" & ¶); "first thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "two" & ¶); ¶ & "second thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "three" & ¶); ¶ & "third thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "four" & ¶); ¶ & "fourth thing" );

1 ) = ¶;

Right (

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "one" & ¶); "first thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "two" & ¶); ¶ & "second thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "three" & ¶); ¶ & "third thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "four" & ¶); ¶ & "fourth thing" );

Length (

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "one" & ¶); "first thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "two" & ¶); ¶ & "second thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "three" & ¶); ¶ & "third thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "four" & ¶); ¶ & "fourth thing" )) - 1);

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "one" & ¶); "first thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "two" & ¶); ¶ & "second thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "three" & ¶); ¶ & "third thing" ) &

Case ( PatternCount (¶ & Text1 & ¶; ¶ & "four" & ¶); ¶ & "fourth thing" )

)

CheckboxOrder.zip

Edited September 27, 2005 by Guest

[-Queue-]

How about an unstored calculation of

Substitute(

Substitute(

Substitute(

Substitute(

Substitute(

"¶¶" & ValueListItems( Status(CurrentFileName), "value list name" ) & "¶¶",

"¶2¶", "¶L¶" ),

"¶4¶", "¶A¶" ),

"¶5¶", "¶V¶" ),

"¶¶", "" ),

"¶", ", " )

where 'value list name' is a value list based on the checkbox field, using only related values based on a self-relationship of the serial?

You can add more nested substitutes for each of the values which you want to convert.

It requires refreshing the screen to see the updated result, but it should work otherwise.

[kimrussell]

Thank you both for your reply. I will try them both. To answer your questoin, Pooh-Bah, of why I don't use the second field here's the real life example:

I have check boxes that describe a type of restaurant. New, BYOB, Outdoor seating, etc. When I export this information, each of these words is represented by an icon as mapped out in a wingding type font. So, for example, New = v which is the New icon, BYOB = b which is the BYOB icon and so forth. I want each of these letters to be in a specific order, but it's not alphabetical.

Again, thank you both for your answers. I knew it was going to be complicated, but I wasn't aware of just how much.