6 Replies Latest reply on Jun 23, 2011 12:02 PM by philmodjunk

    Autopopulating records from external table using multiple trigger fields..



      Autopopulating records from external table using multiple trigger fields..


      So let me start off by apologizing for anything that I say that sounds "newbie". Im not very experienced with FM, but for what Im trying to do I don't think I need to be. Ive spent the past handful of hours reading through this forum and watching youtube videos on FM relationships and I still can't get this right.

      Okay, so some background, I am taking an existing file built by someone else and augmenting it for a completely different simplified purpose. (with permission of course).

      There is a Main File and a Data file. All data is entered via the Data file. On the layout in question, the records in the Main File have fields for Name, Company Name, Email, Notes, etc.

      What it is able to do now: I type in the name of the person (it begins to autofill just like I want) then once selected the other fields information autoloads in from the same record on the Data file.

      What I cant get it to do: If I type in the company name or email instead instead... it autofills properly pulling from the Data file BUT once it is selected NONE of the other fields get filled out...

      I know this must be a very very simple fix...

      My brain is on the verge of exploding from all the research so hopefully someone on here can help shed some light on this problem.

      Thanks for your time.

        • 1. Re: Autopopulating records from external table using multiple trigger fields..

          There is more than one way to set this up. Do you know what method was used? The data may be physically copied into the layout's table or there may just be a link to a related table. Either will look the same, but the implementation and results can be quite different.

          You've identified two files, but what we really need to know here is what tables exist and how they are related to each other and which table is specified for the layout where you are doing your data entry.

          PS. your auto-complete name drop down may look nice and be easy to use, but it could indicate that you have a relationship based on a person's name and this is not the best way to set this up as name changes can reallly mess up your relationships.

          • 3. Re: Autopopulating records from external table using multiple trigger fields..

            Any given layout is based on a single table occurrence (a "box" in manage | database | relationships ).

            In your case, the layout is based on New Touring Soft..., the top left "box". Adding additional relationships to completely separate occurrences of this table will have no effect on the function of your layout.

            If I were to design this database, the relationship would not be based on Pro_SignatoryComb... nor on company name nor on email addresses.

            Names are not unique. People and companies change their names. Either situation creates problems for your database. I'd base the relationship on Pro_Id instead, but use other methods on the Pro_SignatoryComb..., company name, and email fields to look up data from the vp2010pro table.

            There's still a key detail missing. Go to one of these fields where the data "auto fills" and look at it while in layout mode. Double click it to bring up specify fields to be sure. Is this field that "auto fills" when a name is entered in Pro_SignatoryComb... defined in vp2010pro or in New Touring Softw...? (When you double click the field, the field's table occurrence name will be shown in the drop down at the top of the specify fields dialog.)

            • 5. Re: Autopopulating records from external table using multiple trigger fields..

              Deleted the massive amounts of screenshots i had in the last two posts. I finally figured this out tonight.
              Well not all the way but enough to get by. 

              It is now using company name as the look up trigger.

              All I had to do was make the company field the only one matching between the two tables on the relationship tab

              It would still be cool to be able to have any of the three trigger it cause sometimes i remember their name more than i remember their company and vice versa, but for now this should work. Thanks for your help philmodjunk!

              • 6. Re: Autopopulating records from external table using multiple trigger fields..

                Script triggers can be set up on the other fields that perform a script that searches the table for records matching what was entered and then copies the Key value into the key field. In your case it would copy the company name of the first found record into the company name field.

                Something to keep in mind: If you are ever notified that "Acme construction now has a new owner and we are changing our name to Pinnacle Builders" or you enter data for John Smith Consulting, then find out that it's really John Schmidt Consulting or Jon Smith Consulting or.... Your existing system will be difficult to correctly update to handle the name change without loosing the link between your two tables. Using a Company ID serial number would avoid those potential problems.