1 2 Previous Next 18 Replies Latest reply on May 20, 2011 11:46 AM by philmodjunk

    sorting alphanumeric fields

    MarlinPenner

      Title

      sorting alphanumeric fields

      Post

      Hello,  I'm a complete rookie so please provide a detailed response with few assumptions.  My research group uses Filemaker Pro 9 to manage our chemical inventory.  The chemicals are physically stored alphabetically but the names of chemicals are alphanumeric and when sorting chemicals by name the numbers are automatically considered first and then the letters.  For example the following two chemicals are sorted as:

      1,4-benzoquinone
      Acetic hydrazide

      when they should be sorted as:

      Acetic hydrazide

      1,4-benzoquinone

      I was told that there is a way of sorting these as desired but have no idea how to do it.  Also is there a way of doing this type of sorting also ignoring particular character combinations.  For example (R) or (S).

      Thank you

        • 1. Re: sorting alphanumeric fields
          philmodjunk

          You'd need to create a text field where the numbers and any other letter combinations are not present in that field and then you sort on that field.

          Example:

          Filter ( Substitute ( ChemicalName ; ["(R)" ; "" ] ; ["(C)" ; "" ] ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" )

          The Substitute function strips out the character combinations you want to ignore and then Filter removes everything but the upper and lower case letters A - Z.

          If it's a fairly short list of chemicals, you could also define a custom values value list, type them all into the value list in the order in which they should sort and then there's a sort option that allows you to sort records by the order of values in the value list, but this can be a lot less flexible than the calculation field as you would need to update this custom value list by hand everytime you add a new chemical to your database.

          • 2. Re: sorting alphanumeric fields
            MarlinPenner

            Thanks for the reply.  There are thousands of chemicals so the custom values option is not viable. How do I create the text field and then implement it?  Is it something that can be added to the sort options box so that anyone can use it easily?  Thanks.

            • 3. Re: sorting alphanumeric fields
              philmodjunk

              Define a field of type calculations in Manage | Database | Fields.

              Put the expression shown above in the specify calculation dialog, but modify it to use your field name and also if you need to include more characters (such as a space) and/or to exclude more character combinations.

              Select Text as the calculation field's return type.

              When you sort your records, sort by this field instead of the original chemical name field. You do not have to put this field on a layout in order to sort by it.

              • 4. Re: sorting alphanumeric fields
                MarlinPenner

                Thank you, the sorting alphabetically part works perfect!  Getting a bit more technical.  I noticed some names have Greek letters.  Can these be sorted the same way as the ABC....abc... string.  Also, can the excluded characters be defined with a wildcard  for example I want to exclude (1R) or (2R) or (3R) ... etc without specifying the number.  IE is there a string something like: Substitute ( ChemicalName ; ["("#"R)" ; "" ] ) ; possible?  Thanks.

                • 5. Re: sorting alphanumeric fields
                  philmodjunk

                  Sorry, can't use wildcards.

                  Greek letters can be included in the sorting if you include them in the "ABCDE..." text in the expression. You may find it easiest to open MS Word, and use Insert | Symbol to insert your greek characters into the word document. You can then copy and paste these into the calculation.

                  • 6. Re: sorting alphanumeric fields
                    raybaudi

                    Try this one too:

                    Trim ( Substitute ( Filter ( ChemicalName ; KanjiNumeral ( ChemicalName ) )
                    ; [ "." ; "" ]
                    ; [ "," ; "" ]
                    ; [ "(R)" ; "" ]
                    ; [ "(S)" ; "" ]
                    ; [ "-" ; "" ]
                    ) )

                    • 7. Re: sorting alphanumeric fields
                      philmodjunk

                      Now that's clever!

                      • 8. Re: sorting alphanumeric fields
                        MarlinPenner

                        Is there a way of undoing that last one, it seems to have modified the data base?

                        • 9. Re: sorting alphanumeric fields
                          raybaudi

                          Creating a new calculation field couldn't modify existing data.

                          • 10. Re: sorting alphanumeric fields
                            MarlinPenner

                            Well, it has modified one of my display layouts.  Where previously the name of the chemical appeared now the name of the calculation appears along with the calculation underneath each entry.

                            • 11. Re: sorting alphanumeric fields
                              philmodjunk

                              If this is not a table view layout, you can enter layout mode and replace this field with your original data field. If this is table view, you can use the Modify button to control which columns are visible with that layout.

                              • 12. Re: sorting alphanumeric fields
                                raybaudi

                                Go in Layout Mode and place a new occurrence of the original field into the form.

                                • 13. Re: sorting alphanumeric fields
                                  philmodjunk

                                  This probably happened due to a preferences setting that automatically adds a new field to your current layout. If this is how it happened, you may need to just remove that new field from the layout. You can also enter preferences, and change this setting to keep this from happening in the future.

                                  • 14. Re: sorting alphanumeric fields
                                    MarlinPenner

                                    Yes that's what happened thanks.  I was freaked out for a moment thinking I should have backed up before playing around.  Layout mode modifications were all that was needed.  Can the two different approaches be combined so I can trim unwanteds as well as sorting strictly by alphabet? Not sure what the combining syntax would be eg:

                                    Filter ((ChemicalName); "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" );

                                    Trim ( Substitute ( Filter ( ChemicalName ; KanjiNumeral ( ChemicalName ) )
                                    ; [ "." ; "" ]
                                    ; [ "," ; "" ]
                                    ; [ "(R)" ; "" ]
                                    ; [ "(S)" ; "" ]
                                    ; [ "-" ; "" ]
                                    ) )

                                    1 2 Previous Next