Corys Posted March 11, 2004 Share Posted March 11, 2004 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 More sharing options...
-Queue- Posted March 11, 2004 Share Posted March 11, 2004 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 More sharing options...
djgogi Posted March 11, 2004 Share Posted March 11, 2004 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 More sharing options...
Ugo DI LUCA Posted March 12, 2004 Share Posted March 12, 2004 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 Link to comment Share on other sites More sharing options...
-Queue- Posted March 12, 2004 Share Posted March 12, 2004 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? Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted March 12, 2004 Share Posted March 12, 2004 That's what I understood JayTee... Here's one solution for a complete switch from standard alphabet to a customized one. MyAlphabetv2.fp5.zip Link to comment Share on other sites More sharing options...
BobWeaver Posted March 12, 2004 Share Posted March 12, 2004 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 More sharing options...
Ugo DI LUCA Posted March 12, 2004 Share Posted March 12, 2004 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 More sharing options...
BobWeaver Posted March 12, 2004 Share Posted March 12, 2004 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 More sharing options...
Ugo DI LUCA Posted March 12, 2004 Share Posted March 12, 2004 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. Link to comment Share on other sites More sharing options...
djgogi Posted March 12, 2004 Share Posted March 12, 2004 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 More sharing options...
Corys Posted March 13, 2004 Author Share Posted March 13, 2004 wow..thanks for all the help and examples. what Ugo came up with will work like a charm.. thanks a ton!! Link to comment Share on other sites More sharing options...
Newbies smitha Posted July 22, 2004 Newbies Share Posted July 22, 2004 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 More sharing options...
Recommended Posts
This topic is 7217 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 accountSign in
Already have an account? Sign in here.
Sign In Now