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 would appear to be the simplest solution unless i am missing something Nigel > On 21 Dec 2016, at 00:40, Pat Bensky <pben...@gmail.com> wrote: > > 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 should actually be sorted like this: > > AuBr3 > AsGa > Al2O3 > Ag2O > > So the lowercase letters in the elements should not be considered in the > sort order. > One simple solution would be to just create a sort field in which all > lowercase letters have been deleted. That would work in most cases but not > if there was a situation something like > > AuBr3 > AgBr3 > AsBr3 > > So that won't do. > Note - we already deal with the subscripted numbers by padding them out > with zeros. > > 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 save that sort order in the > numeric sort field. > 4. Run through all the records in their sorted order. If any two or more > are found to have the same NoLowerCase entry, adjust their numeric sort > order according to their original formulas. > Then they can be sorted by the sort field and should all be in the correct > order. > > Any better ideas? > > Pat > -- > ************************************************* > CatBase - The Database Publishing Solution > tel: +44 (0) 207 118 7889 > w: http://www.catbase.com > skype: pat.bensky > ************************************************* > ********************************************************************** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: http://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com > ********************************************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************
