1 Reply Latest reply on Jan 24, 2013 10:01 AM by philmodjunk

    Finding a potential record in unrelated table

    miw

      Title

      Finding a potential record in unrelated table

      Post

           I have two unrelated tables:  "Contact" and "Prospect" - they are used for two distinct and separate purposes so there is no real need to link them.  In my FM 12 pro database, when I type info for a contact in the Contact table, which includes a "Contact::Company" field, I would like to have the option to click a button and add that "Company" name to the Prospect table in the Prospect::Company field if that Company name does not already exist in the Prospect table.  What would a script look like to have the Contact::Company first see if such a name already exists in the Prospect:Company field (presumably a find?) and then if it doesn't already exist, add it to the Prospects table?

        • 1. Re: Finding a potential record in unrelated table
          philmodjunk
               

                    they are used for two distinct and separate purposes so there is no real need to link them.

               Umm well actually you would need to link them in order to quickly determine that a given contact does not already exist in prospects.

               One possible option I would strongly encourage you to consider is to keep all your contacts and prospects in the same table with a field that stores a value to identify which contacts are prospects. This suggestion assumes that the data fields in the two tables are very similar--an assumption that may not be correct.

               If you establish a relationship between the two tables using the company name fields as the match fields, you can use this relationship to check to see if a prospect record already exists with the same name, but a note of caution: Company names are not guaranteed to be unique and company names change from time to time--both issues you'll need to keep in mind when working with your contacts and prospects.