4 Replies Latest reply on Oct 22, 2009 6:10 PM by whittex

    fMultiple find and replace



      fMultiple find and replace


      How does one automatically insert a single character into a blank field for >500 records. The character will be the same for each record.


        • 1. Re: fMultiple find and replace



          Replace Field Contents
          Compiled by Stuart Gripman
          Here's how to change the values of one field in a bunch of records simultaneously.

          If you use a database long enough, you're bound to encounter a situation where you need to change the values of one field in a bunch of records at the same time. If you've ever gone from record to record making the same change over and over again, surely you've thought there must be a better way. There is.

          The Replace command is a powerful tool for updating many records at once. Effectively, Replace deletes the contents of the currently active field and inserts the data of your choosing for every record in the found set. It's important to exercise caution when performing a Replace. It always operates on the entire found set of records -- and you can't reverse it by using "Undo." I routinely save a back-up copy of my database before a Replace. It's the only way to go back if you make a mistake.

          Replace with current contents
          Replace has three methods of operation. The first Replaces the field data in all records to match the current record's value. Click into a field and choose "Replace Field Contents…" from the Records menu. If you click on "Replace," all the records in the found set will now contain the same value as the active record.

          Replace with serial numbers
          This option lets you apply serial numbers to the Replace field. The initial and increment values must be integers. If the field you're replacing in is defined as a serial number field, the "Update serial number in entry options" checkbox will be available. Checking it will update the field's serial number settings to ensure that records you create after the Replace action will have appropriate serial numbers. One word of caution: No matter which record you invoke the Replace from, the initial value will be applied to the first record in the found set.

          Replace with calculated result
          Here lies the true power of Replace. Choosing this option opens a standard calculation dialog box. If you can calculate it, you can Replace with it. One very common application is to append information to an existing field. Say you have a notes field and you want to add "Sent Summer 2006 Card" to the notes of each record without overwriting the existing notes. The calculation below would do the job:

          notesField & "¶Sent Summer 2006 Card"

          This calculation simply retains the existing contents of the notes field and adds the text on a new line at the bottom.

          For more on calculations, see Susan Prosser's articles in Issue #2 2006. Subscribers can read them online at http://My.Advisor.com/doc/17636 and http://My.Advisor.com/doc/17640.

          Replace gotchas
          It's not all roses and sunshine with the Replace command. It bears repeating that you can't undo a Replace, so be sure to save a backup before every significant Replace.

          You can perform a Replace in a script, but be aware that the field being replaced must be available on the current layout at the time the script is run. If the target field isn't available, the Replace won't take place and FileMaker won't generate an error message. Write your scripts to go to an appropriate layout before invoking a replace.

          Finally, you can perform a Replace on a networked database, but it can be slow. FileMaker Pro has to move a lot of data across the network for each record involved in the Replace, so even a few large records can take a long time. More importantly, when multiple users are connected to a database, Replace can't update records that are being edited by other users. Under these circumstances, you will get an incomplete Replace that skips those records locked up by other users. Although FileMaker Pro will alert you to such an error, it will not tell you exactly which records could not be updated. Generally, if you need the Replace effect in a multi-user database, a looping script is a better choice.


          • 2. Re: fMultiple find and replace
               Thanks David. I failed to mention that I do not want to replace the field data in every record. Just in the records where the field is now blank. Any suggestions?
            • 3. Re: fMultiple find and replace

              Perform a FInd to find just those records where the field is blank, then follow David's instructions.


              To find records with a blank field:


              Enter find mode

              Enter just an equals sign in the blank field

              Perform the find

              • 4. Re: fMultiple find and replace