2 Replies Latest reply on Dec 10, 2012 8:58 PM by wimrippen

    Conversion/Replace Sa in SA and Gmbh in GmbH etc



      Conversion/Replace Sa in SA and Gmbh in GmbH etc


           I have world-database with 10.000  Companies with linked Products database via Company field in both. AAbbcc=AAbbcc.


           How to make script to find/convert Companies like ....Johnson Sa into Johson SA ...and ...Weber Gmbh into Weber GmbH...

           and similar business forms. I have to change both in order to maintain the effective link.


           It is too complex doing it manually, so I wonder with a basic scipt it could be converted,later I can extend it

           to similar  business forms like Spa/ CV de SA , B.V. into BV and N.V. to NV.

           Thanks for help/ideas.. !




        • 1. Re: Conversion/Replace Sa in SA and Gmbh in GmbH etc

               You would be much, much better off if you did not link your tables by name, but used a serial number or UUID (Filemaker 12 only) instead. Avoiding such issues is one of several reasons why that's the beter way to go when linking records in a relationship.

               However, when using text fields as match fields in a FileMaker relationship, the values are not Case sensitive.

               Sa will match to SA and sa.

               The only way that I can see to make this work is on a case by case basis. You'd specify Sa, for example and then the script would find all records were a single word in the file is either sa, SA or Sa and then replace field contents can be used to make them all SA.

               Here's the scripted method in more detail:

               Define a global field: Globals::gSearchText
               Define a second Global text field: Globals::gExampleList

               Enter text such as GmbH into gSearchText, gmbh, GMBH, Gmbh, etc into gExampleList, and then perform this script:

               Enter Find Mode []
               Set Field [YourTable::NameField ; "=" & Globals::gSearchText]
               Set Error Capture [on]
               Perform Find[]
                  Set Variable [$K ; value: $K + 1 ]
                  Exit Loop If [ $K > ValueCount ( Globals::gExampleList )
                  Replace Field Contents [no dialog; Substitute ( GetValue ( Globals::gExampleList; $K ) ) ; Globals::gSearchText ) ]
               End Loop

               Note, with the proper recursive custom function and FileMaker Advanced, you could eliminate the need for gExampleList and the looping portion of the script, replacing it with a single call to the recursive custom function.

          • 2. Re: Conversion/Replace Sa in SA and Gmbh in GmbH etc

                 Thanks PhilModJunk, I rebuild the whole system and indeed it works much better with CompanyID relation.

                 If I continue now and adding a new Company and/or Contact (in FM12 Pro Adv/ServerFM12) how to generate a new fresh

                 CompanyID,see my Forum posting on this a few minutes ago,

                 Thanks for the very very good advice !

                 William Netherlands