Custom ABC Sort Order

March 11, 200421 yr

Hello,

I'm hoping I can do this, maybe someone can tell me..

I want to sort a bunch of names in they way ABC would be, but have the alphabet mixed around..

I've created a Value list that has my random alphabet, R,E,Z,M etc etc etc...but when I try to sort by custom order, it still does it by ABC order. It looks like it's expecting an exact match when comparing my value list to my last names.

is there anyway to do an ABC sort, but specify the ABC order?

thanks

March 11, 200421 yr

If I understand your question correctly, you want to sort last names by a 'random' alphabet. Create a calculation field that is Left(LastName, 1) and try sorting this field by your value list.

March 11, 200421 yr

Well it's little bit more complex than that.

Supposing you have defined your alphabet as set of distinct chars, you'll need1:

1) to exclude those chars from ASCII set

2) create an substitutive set from the above ASCII subset

3) Using in chain Substitute function, create your hidden sort field (ASCII for language sort)

For ex.

Substitute(

yourField, "Q", ":")

, "Z", ";")

, "E", "<")

, "R", "=")

, "T", ">")

, "Y", "?")

Dj

March 12, 200421 yr

Once the ASCII characters are off, why not use a custom value list (indexed) and a calc field ?

This one (that doesn't extract ASCII) only catch the first letter but could be expanded to fit your needs.

No ?

MyAlphabet.fp5.zip

March 12, 200421 yr

Okay, so after testing, my idea works, but only for the first letter. Do you mean you want to sort the entire field based on your random alphabet?

March 12, 200421 yr

That's what I understood JayTee...

Here's one solution for a complete switch from standard alphabet to a customized one.

MyAlphabetv2.fp5.zip

March 12, 200421 yr

If you need to sort on every character --not just the first -- you can create a calculated sort field with the substituted alphabet. So that you don't re-replace characters that have been already converted use the trick of changing the input text to upper case first, and use uppercase for the search character and lowercase for the replace character. Substitute() is case sensitive.

Substitute(Substitute(Substitute(Substitute...<<26 Times>>...(Upper(InputText)

,"R","a")

,"E","b")

,"Z","c")

,"M","d")

....etc...

,"Q","z")

March 12, 200421 yr

Yes.

I just played around with the value list trick. Basically same concept except that it

Substitute A with the Position of A in the Custom Alphabet

Then substitute this Position# with the value of the Standard Alphabet in this position.

26 substitutions + 26 others.

True that I could have made a 26 substitutions with each being

Subsitute(Upper(Text), "A",

MiddleWords(ValueListItems(Status(CurrentFileName),"CustomAlphabet"),WordCount(Left(ValueListItems(Status(CurrentFileName),"StandardAlphabet"), Position(Status(CurrentFileName),"StandardAlphabet"), "A", 0, 1))),1),

Subsitute(Upper(Text), "B",

MiddleWords(ValueListItems(Status(CurrentFileName),"CustomAlphabet"),WordCount(Left(ValueListItems(Status(CurrentFileName),"StandardAlphabet"), Position(Status(CurrentFileName),"StandardAlphabet"), "B", 0, 1))),1),

....

Is this what you mean Bob ?

March 12, 200421 yr

I just meant that you could create a calculated field that has the alternate alphabet substituted for each character, and then sort this field using a standard sort rather than try to sort the original field with a custom sort. The records should end up in the desired order.

If the order of the substitute alphabet is going to change on a regular basis, then you might want to replace the individual characters with a reference to a single global alphabet field like this:

Substitute(Substitute(Substitute(Substitute...<<26 Times>>...(Upper(InputText)

,Middle(gAlphabet,1,1),"a")

,Middle(gAlphabet,2,1),"b")

,Middle(gAlphabet,3,1),"c")

,Middle(gAlphabet,4,1),"d")

....etc...

,Middle(gAlphabet,26,1),"z")

Make sure that the letters in gAlphabet are all uppercase.

March 12, 200421 yr

Yes, actually it is late here...

Having a value list doesn't offer much more than a harcoded calculation..., and surely worsen the calc structure.

The global route on the other side, even if smooth, stays unstored and could get longer to process with the sort feature.

Thanks Bob.

March 12, 200421 yr

I didn't have time to reply yesterday,

so I was saying the above (the ASCII stuff) because, coincidentally, I have just finished the work on commissioned Sanskrit dictionary ( 57 symbols including diphthongs and triphthongs ) where I was affronting the same question.

Globals or related field are out of question because of indexing issue as you surely know.

In this moment I can not send you the complete solution (think that my client wouldn't be very happy), but I can assure you that it works like a charm.

The substitutive calculation must be indexed or as I said the sort and search could become extremely slow.

Also be aware on 63 items in chain limit of Substitute function, so it might be necessary to split the calculation in two fields. (I had to do so because my client asked also to make (ab)use of equivalent characters like e,

CustomAlphabet.zip