Re: Sorting chemical formulas - SOLVED

Here is the code, in case anybody wants it: fr_t:=PtrFormulaField-> fr_replace:="<->"// <-> is the subscript tag While (Position(fr_replace;fr_t)>0) fr_loc:=Position(fr_replace;fr_t) If (fr_loc>0) fr_loc2:=Position(fr_replace;fr_t;fr_loc+1) If (fr_loc2>fr_loc) fr_numString:=Substring(fr_t;(fr_loc+

Re: Sorting chemical formulas - SOLVED

Hi Robert, That's because initially I didn't have a complete understanding of the requirement :) Consider this list: Ag2CrO4 Ag002Cr000O 004 Ag2O Ag002O 000 Ag2O4S Ag002O 004S 000 Ag2O4W Ag002O 004W 000 Ag2Te Ag002T 000 Ag3O4P Ag003O 004P 000 AgAsF6 Ag000As000F 006 AgBF4 Ag000B 000F 004 AgBr Ag000

Re: Sorting chemical formulas - SOLVED

Original Post Ag2O Al2O3 AsGa AuBr3 This is how 4D sorts these 4 formulae, which at first glance appears to be correct. Unless you're a chemist :) They should actually be sorted like this: AuBr3 AsGa Al2O3 Ag2O ___ Solution?? Ag002O000 Al002O003 As000Ga000 Au00

Re: Sorting chemical formulas - SOLVED

Thanks for all the suggestions guys. After further consultation with the chemists and experimentation we came up with the following solution, which works as required: - Each element must be two characters. The first character is uppercase. If there's no second character, pad it with a space. - Ea

Re: Sorting chemical formulas

Refinement: I suppose that you would have to pad with spaces or underscore or whatever to cover the total number of possible elements in the molecule if there could be more than 2 We will assume that there are no materials with large (> 5 for example) number of elements... So to deal with hyp

Re: Sorting chemical formulas

How about create a string field that concatenates the form of the chemical with the lowercase removed & then the original formula Index this field for sorting purposes So: Ag2O -> AOAg2O Al2O3 -> AOAl2O3 AsGa -> AGAsGa AuBr3 -> ABAuBr3 AuBr3 -> ABAuBr3 AgBr3 -> ABAgBr3 AsBr3 -> ABAsBr3

Re: Sorting chemical formulas

On Tue, Dec 20, 2016 at 7:40 PM, Pat Bensky wrote: > > The only solution I've come up with so far is: > 1. Create a numeric sort field. > 2. Create another field with the formulas stripped of all lowercase letters > - let's call this field NoLowerCase. > 3. Sort them on the NoLowerCase field and

Re: Sorting chemical formulas

> Le 21 déc. 2016 à 01:40, Pat Bensky a écrit : > > [...] Unless you're a chemist :) I was a biochemist, a long time ago now… As a biochemist, I'd use a table of orde

Re: Sorting chemical formulas

Separate the positive vs negative parts of compound and the create a composite index is all I can suggest. Regards, Wayne Sent from my iPhone > On 21 Dec 2016, at 16:55, Peter Bozek wrote: > >> On Wed, Dec 21, 2016 at 1:40 AM, Pat Bensky wrote: >> >> Any better ideas? >> > > Sorting with

Re: Sorting chemical formulas

On Wed, Dec 21, 2016 at 1:40 AM, Pat Bensky wrote: > Any better ideas? > Sorting with ORDER BY( [Formulas]; [Formulas]NoLowerCase; [Formulas]FullFormula ) is OK? From your description it seems so. If yes, maybe it is sufficient to create composite index and use above instead of additional sort

Re: Sorting chemical formulas

could you elaborate more on the expected sorting order? you say "lowercase letters in the elements should not be considered" but then go on to say that it "won't do". so what actually are the rules? > 2016/12/21 9:40、Pat Bensky のメール： > > They should actually be sorted like this: > > AuBr3 > AsG

Re: Sorting chemical formulas

Pat would it not work if your ‘sort field contained the chars in a different order. The lower case are of secondary importance so if they came after the upper case they would only affect the sort in the case you mention so in your example data AuBr3 becomes ABur003 or in the sort field? That

Re: Sorting chemical formulas

I'm not clear on the rules but it seems like you could build one sort field in the proper order, so: AbCdEf AbCgEf AhCgEf use sort field entries: ACEbdf ACEbgf ACEhgf Does this make sense? > On Dec 20, 2016, at 4:40 PM, Pat Bensky wrote: > > Here's an interesting challenge for those off yo

Sorting chemical formulas

Here's an interesting challenge for those off you who like a good puzzle ... I need to figure out how to sort chemical formulas correctly. For example: Ag2O Al2O3 AsGa AuBr3 This is how 4D sorts these 4 formulae, which at first glance appears to be correct. Unless you're a chemist :) They s