Custom ABC Sort Order

[Corys]

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

[-Queue-]

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.

[djgogi]

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(

Substitute(

Substitute(

Substitute(

Substitute(

Substitute(

yourField, "Q", ":")

, "Z", ";")

, "E", "<")

, "R", "=")

, "T", ">")

, "Y", "?")

Dj

[Ugo DI LUCA]

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

[-Queue-]

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?

[Ugo DI LUCA]

That's what I understood JayTee...

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

MyAlphabetv2.fp5.zip

[BobWeaver]

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")

[Ugo DI LUCA]

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 ?

[BobWeaver]

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.

[Ugo DI LUCA]

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.

[djgogi]

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

[Corys]

wow..thanks for all the help and examples. what Ugo came up with will work like a charm..

thanks a ton!!

[smitha]

Hi everyone

I am Smita and i have a serious issue here!!

fp5 and fp7 sort differently.

My fp5 has the "default language setting for Indexing and Sirting as English"

Fp7 also has the same seting. Due to their different sort preference the sorting that I can see in Fp7 is not the same.

Actually i have migrated my solution in FP5 to FP7....

I really need to know whether:: Without changin my FP5 , Can I do some tweaking and get the same sort order/Preference.

Thx

Smita