3 Replies Latest reply on Oct 14, 2014 2:27 PM by philmodjunk

    Question about Substitute Function



      Question about Substitute Function


      One of the fields in a table I import contains a single period to designate "Free Trial". (I have no idea why but I can't change the source.)

      When I import, I want to replace all records with a single period in this field with the text string "Free Trial" and I want to retroactively change all records that have already been imported. I know I could change the ones already imported with a Find / Replace but I'd rather set this up so it does it automatically from now on.

      I created an Auto Enter calculated value using the calculation shown below but it's not working on existing records. Note:  "Do not replace ..." is unchecked.

      Substitute ( Self; "." ; "Free Trial")

      Is there a syntax error here? It looks OK when compared to FileMaker docs but it is not working.

      Thanks for any help.

        • 1. Re: Question about Substitute Function

          It's doing exactly what you set it up to do, just not what you want it to do. Adding or changing auto-enter field options does not automatically update the data in existing records, only records added after the fact or records where a field referenced int the calculation has been changed.

          You can use replace field contents (This is NOT find/replace) from the records menu to update the field after making your change to teh auto-enter field options or you might use this trick: Updating values in auto-enter calc fields without using Replace Field Contents

          • 2. Re: Question about Substitute Function

            Clever. Thanks.

            Two Questions:

            Is there any reason not to use the Replace Field Contents after I find all records with the "offensive" period?

            Will the Substitute calc work when I do my next import? It takes me nearly an hour to do the import. Otherwise I'd just try it.


            • 3. Re: Question about Substitute Function

              I'd take a copy of your database and import source of just a few records and run a test to confirm that it's set up correctly and produces the results that you want.

              There are two basic approaches to "fixing" data that is being imported. You can set up an auto-enter calculation that corrects the data during import or you can use Replace Field Contents or a looping script to fix the imported data after import. Which is the right approach may not matter or you may find that one option works better for you than the other. Enabling auto-enter options during import, for example, enables ALL auto-enter field options and that may not always be desirable.