3 Replies Latest reply on Jul 21, 2010 9:06 AM by philmodjunk

    Auto-Populate Other Fields from a Value List

    retailmonica

      Title

      Auto-Populate Other Fields from a Value List

      Post

      I would like to auto-populate other fields from a value list in a drop-down.  Not too complicated, right?

      My issue is that I have three different sets of similar data that may or may not be necessary on each record.

      PI - PI Phone - PI Dept - PI School

      Co-PI 1 - Co-PI 1 Phone - Co-PI 1 Dept - Co-PI 1 School

      Co-PI 2 - Co-PI 2 Phone - Co-PI 2 Dept - Co-PI 2 School

      When I select the PI or Co-PI's name from a value list, I want the information that I have already set up in another table to populate, so we don't have to mess with phone numbers, etc.  The problem is that I can't get the exact same data to be available for each of the PI fields.  It is very possible that on one record I want Joe in the PI field, and on another one he should be Co-PI 1.

      The closest I got to getting this was when I select PI, every other field fills in.... even the Co-PI fields, minus the names, which are not set to lookup.

      I have tried making more than one relationship between the tables, but no such luck.

      Any help?

        • 1. Re: Auto-Populate Other Fields from a Value List
          philmodjunk

          Strikes me that a redesign of your tables and layouts might be a good idea, but can't tell for sure from your description, so I'm going to stick to suggestions on how to make the above set up work for you.

          I'm assuming that if a person's phone number or other contact data is updated, you'll want to see that updated info appear on all the records where you are selecting people from value lists. This isn't always the case--sometimes you need to keep data in past records unchanged. If you don't want the changes to appear automatically, let me know and I'll post a slightly different approach that uses the same relationships I am about to describe.

          Let's assume you have two tables for this suggestion, your names will be different so substitute your own table and field names for mine:  MainTable, ContactInfo

          For purposes of this discussion, MainTable has just Two fields: ContactID1 and ContactID2

          ContactInfo would have the following fields:

          ContactID (auto-entered serial number)
          Name
          Phone
          Dept
          School

          You'd define the following relationships (One for each of the two individuals shown above):

          MainTable::ContactID1 = ContactInfo::ContactID
          MainTable::ContactID2 = ContactInfo 2::ContactID

          ContactInfo 2 is a second table occurence of ContactInfo. It points to the same data source table, but has a different name so you can link it to a different field in MainTable. To create it, select the table occurrence box for ContactInfo in Manage | Database | Relationships and then click the button with two green plus signs.

          On your MainTable layout, place Both ContactID1 and ContactID2 fields, formatted with the same 2 column value list of values drawn from ContactInfo. Column 1 should show the ContactID and Column 2 should show the name. Don't hide the first column unless you also use a validation rule to enforce unique names in ContactInfo or duplicate names won't show in the list.

          Next to ContactID1 place name, phone, dept and school fields from ContactInfo. Next to ContactID2, place the same fields, but select them from ContactInfo 2 instead of contact Info.

          Now when you select a person in ContactID1, you'll see their contact info appear automatically and the same will occur for a different person selected in ContactID2.

          • 2. Re: Auto-Populate Other Fields from a Value List
            retailmonica

            Thanks for the reply, Phil...

            I think you're right, I wouldn't want it to update old information.  I follow your response above, but I would appreciate a modified answer.

            Thanks so much!

            • 3. Re: Auto-Populate Other Fields from a Value List
              philmodjunk

              Option 2 uses the same relationships, but you need separate data fields in main table that auto-enter values from ContactInfo and contactInfo 2.

              I'll describe the process for two fields and you can apply the same methods to the others.

              Define Phone1 and Phone2 fields in MainTable. In field options for Phone1, click the auto-enter tab and select the looked up value option. Select the Phone field from ContactInfo as the value to copy into this field. Do the same for Phone2, but select the Phone field from ContactInfo 2 instead of ContactInfo.