4 Replies Latest reply on Jan 11, 2011 11:25 AM by DiotimaBooraem

    Finding hundreds of email addresses

    DiotimaBooraem

      Title

      Finding hundreds of email addresses

      Post

      I have a list in an Excel spreadsheet of about 300 email addresses, each of which are also in some record in my 2,000 record FM database. These are the addresses of people to whom we cannot e-mail. There is a field in the db called "OK to Email" and I need to find the records for all those addresses and change each one to "No" in the OK to email field.

      I can't figure out how to do this besides doing a separate find on each and every one of those email addresses, and then changing the other field manually. I figured if I could manage to pull out all 300 records as a found set, then I could change the OK to Email field globally. But I can't find any way to do that. Any advice would be appreciated!

      Thanks,
      Diotima

        • 1. Re: Finding hundreds of email addresses
          philmodjunk
          1. Import your spread sheet into a separate table.
          2. Relate your two tables like this:
            OriginalTable::Emailfield = ImportTable::Emailfield
          3. Perform a find on a layout based on your original table by putting a lone * into Import::EmailField. (If you do this manually, you'll need to add the field to your layout.)
          4. Make a back up copy of your file first. Then use Replace field contents to change the contents of "OK to Email" to "No" in one step. (Change the value of one field to "No" then select Replace Field Contents from the Records Menu to change the rest of the records in your found set to the same value.)


          While I've described this process the way you would do this manually, it is possible to do the entire operation in a script so that all you have to do is click a button.

          • 2. Re: Finding hundreds of email addresses
            FentonJones

            Import the Excel 300 email addresses into a FileMaker table (with just an email address field). This is just a table for this purpose.

            Create a relationship from this table to the main table, based on email address.

            Put a button in the Header of the imported emails table's layout. Attach a script step to the button:

            Go To Related Record [ Main; Show only related records; Match all records in current found set (radio button); Main table's layout ]

            This will go to the 300 (or however many are there to match).

            Put your cursor in the "OK to Email" field (or click "No" if it's a radio button). 

            Records menu; Replace Field Contents [ OK to Email; "No" ]

            I should say FIRST BACKUP YOUR FILE. There is no "undo" for Replace. It can quickly wipe out your data.

            BTW, if this is a one-shot deal, you could just Open the Excel file, create the relationship and run the Go To Related Record from that new FileMaker file; more or less the same thing.

            • 3. Re: Finding hundreds of email addresses
              philmodjunk

              It also occurred to me that if this is something where you will be making this update on a regular basis, you might just redefine your Ok To Email field as a calculation field:

              If ( Isempty ( ImportTable::EmailField ) ; "Yes" ; "No" )

              Then you just add/remove records from the related table to change the status of this field.

              • 4. Re: Finding hundreds of email addresses
                DiotimaBooraem

                Thank you both very much. I knew there had to be a better way!