2 Replies Latest reply on Dec 4, 2009 5:56 AM by Vic

    Multiple searches from a spreadsheet?



      Multiple searches from a spreadsheet?




      I have the task of cleaning a database in FMP 10. The data to be cleaned is invalid email addresses which are in a spreadsheet. (bounces from a mailing)


      Is there a way of finding over 900 different contacts using first name, last name or email address without having to search for each one individually.


      Currently I am searching for each one and putting a common identifier in the notes field so they can all be found later in a single search.


      Thanks, in anticipation.



        • 1. Re: Multiple searches from a spreadsheet?

          You can import the spread sheet in to a utility table and create a script that uses the imported data to find and mark matching records.


          After import, a script like this might do the job:


          Go To Layout [//Layout for utility table ]

          Go to Record [First]


            Set Variable [$Name; Value: UtilityTable::Name]

            Go To Layout [//Your Main table]

            Enter Find Mode[]

            Set Field [YourTable::Name; $Name]

            Set Error Capture[on]

            Perform Find[]

            If [ get ( FoundCount ) > 0 /* records were found */]

               Replace Field Contents [no dialog; YourTable::MarkField; "x"]

            End If

            Go To Layout [//Layout for utility table ]

            Go To Record [next; Exit after last]

          End loop



          I've referred to just one "name" field, If you need to refer to several fields from the spread sheet, you'll need additional set variable and set field steps.

          I've specified Replace Field Contents so that if multiple records are found, all are marked with an 'x'. If you're sure you'll be finding exactly one record each time, you can use set field instead.

          • 2. Re: Multiple searches from a spreadsheet?

            Thanks Phil,


            I have had slight success. (I am totally new to scripting)


            I will try modifying further to get a better result next week and hopefully understand a bit more.