Jump to content

Custom ABC Sort Order


Corys
 Share

This topic is 6397 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. wink.gif

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 4 months later...
  • Newbies

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

Link to comment
Share on other sites

This topic is 6397 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.