3 Replies Latest reply on Aug 4, 2014 11:40 AM by philmodjunk

    Auto-Populate 4 Fields Based on A Single Field w/ Value List



      Auto-Populate 4 Fields Based on A Single Field w/ Value List


           I've done some searching on the forums a bit and although I've seen variations of this topic posted, none have provided the solution I need.


           I'm working with a large database but for this problem I'm working with 2 tables. Table 1=Contacts, Table 2=ServiceCall

           The Layout is based on the ServiceCall table. I have 5 Fields involved in this problem. The field I need the value list associated with is LastName. So when a Contact calls with a service call I select their LastName from the drop down menu that has values from the contacts table's records (right now the field is part of the ServiceCall table with the field name being LastName(ServiceCall::LastName), and the drop down value list has the values of Contacts::LastName)

           The other 4 fields need to be populated with other information from Contacts based on what is selected via the dropdown on  the LastName field. So for that I have: ServiceCall::Address, ServiceCall::City, ServiceCall::State, ServiceCall::Zip

           I've tried setting these fields up on the ServiceCall Table as being look up fields but it hasn't worked for me thus far. I know something is wrong with what I'm doing.

           The Contacts table needed a second TO to be linked with ServiceCalls.

           I just need the data for those 4 fields to populate once that last name field is selected from the drop down.


           Thank you in advance for any help!

        • 1. Re: Auto-Populate 4 Fields Based on A Single Field w/ Value List

               And what will you do when you get service calls for two different people named "Smith"?

               A last name or even a full name is not guaranteed to be unique. Even worse, people (and companies) change their names.

               So you will likely need to use something more sophisticated than just a last name in order to find the correct contact record for each new service call record.

               Basic data model should be:


               Contacts::__pkContactID = ServiceCalls::_fkContactID

               From there, you have a number of options for how to create new records in ServiceCalls and link them to records in Contacts.

               One method is to use a portal to ServiceCalls located on your Contacts layout. You can perform a find for a customer, use address and phone number type info to confirm that you have the correct customer if your find pulls up more than one contact record and then simply fill in the ServiceCalls fields in the portal to create a new ServiceCalls record that is automatically linked by ID to the current contact record.

               Another method is to format the _fkContactID field field on the ServiceCalls layout as either a drop down list or popup menu of contact IDs and names. You set up the value list with the "use values from a field" option and specify __pkContactID as the source of values for the "first field" and select a Name field (One that combines at least first and last names) as "Field two". This allows you to select a contact by name, but enter an ID number.

               You can then add fields from Contacts to your ServiceCalls layout and they will display data from the selected contact when you select a contact from the value list on the _fkContactID field.

               And these are just the "beginner level" methods for linking a service call record to a particular contact record.

          • 2. Re: Auto-Populate 4 Fields Based on A Single Field w/ Value List

                 Alright I worked on your second suggestion, which sounds more like what I need, and the drop down menu isn't working. I changed the fields so that the pk contact fields for the ones I need to be auto populated are on the fk layout. the dropdown list field is a fk field (servicecall) the others are pk fields (from Contacts but on the ServiceCalls layout)? the drop down menu gets its info from the pk field for contact::FirstLast but is displayed as the ServiceCalls::FirstLast field on the layout. 


                 Wondering what I'm doing wrong.

            • 3. Re: Auto-Populate 4 Fields Based on A Single Field w/ Value List

                   The __pkContactID field should be defined to auto-enter a serial number or be a text field with Get ( UUID ) specified to enter a "Universal Unique ID" text string. If you have existing records in your table, they won't automatically update to get such a value, this change will only affect new records. If you have existing records, go to a layout based on Contacts, Show All Records and then use Replace Field contents to assign either a serial number or UUID (depending one which option you decided to use) to update them with ID's.

                   Then your value list should work if set up correctly. In Manage | Value Lists, it should resemble this screen shot taken from the Known Bugs List Database: