3 Replies Latest reply on Jun 11, 2012 3:08 PM by philmodjunk

    editing a value list with custom values



      editing a value list with custom values


      Hello everyone, long time no post, and I am making progress.  Here's my latest....and I apolize if I cant explain it correctly.

      I have 2 tables, with the relationship of "CONTACT MATCHING ID FIELD" as the key field.  Table 1 is the customers name, info, etc.  Table 2 is a list of their equipment (some customers have more then one piece of equipment).

      Its easier to enter the information on Table 2, however I want to make sure when I click "new Record", that I enter the equipment info so it connects to the right customer.

      First thing I tried was to take the "CONTACT MATCHING ID FIELD", on table 2, and attach it to a value list.  In the value list, "use value from field", and under "specify field" I have the words......           "Contact::CONTACT MATCHING ID FIELD" and "Contacts::Last"

      So what this does, when I click "new record", I get a drop down list of Last Names, from Contacts, to select.  The problem is, for multiple contacts with the same last name, i.e. "Smith", only the first occurance will show (if I'm saying that right).  So John Smith is the only Smith I can add a new piece of equipment to, and not Ruth Smith. So how can I fix this?

      I guess my question is, if I drop down to "use custom values", can I use a custom formula to add the first name too....if so..then obviously how?

      Any help would be greatly appreciated.



        • 1. Re: editing a value list with custom values

          You don't want to use custom values for this. Custom value list is a list of values you manually type in when setting up the value list.

          But you can define a calculationfield that combines first and last names in a single field. You might use something like:

          LastNameField & ", " & FirstNameFIeld

          If you define this calculation as an auto-enter calculation for a text field, you can specify a unique values validation and the user will be required to enter a unique combination of firat and last names each time they add a new customer record.

          Then use this in your value list in place of using just the last name field.

          As your customer lists get long, however, you are still faced with a great deal of scrolling just to select a new customer from the value list. In such cases, you might try using one of the techniques in this demo file: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

          • 2. Re: editing a value list with custom values

            Thanks again Phil (howUdoinnn?)  Ill give that a try tomorrow.  A little more info, just to see if it changes anything.  The list of customers is already set.  It's the adding of the different types of equipment.  Some customers have up to 8.  Are you saying, doing it your way, I would start to type a Last Name, FM would auto validate (if im saying that correctly), then tab to the next field to start typing a first name?  I dont want to create a new customer, on the equipment table, just add equipment to an existing customer.  If thats what you're saying will happen (and you always correct), then thats how ill proceed.

            Thanks again (and again, and again)


            • 3. Re: editing a value list with custom values

              It seems unlikely that you will never ever add new customers. The unique values validation rule is a way to make sure that you don't get two customers with exactly the same first and last names should you add a new customer record in the future.

              Unfortunately, value lists that enter an ID number do not auto-complete or support type ahead except in a very limited fashion in windows (not at all in mac). That's why I also encluded a demo file where you can enter text into an auto-completing value list of names and a script finds all matching records and either enters the id of the record that's found (if only one is found) or presents you with a list where you can click one on the list to select it if there is more than one found.

              This can make for much faster data entry when your value list becomes long and also does not require unique first/last name combinations. On the downside, it requires some scripting to make it happen.