6 Replies Latest reply on May 4, 2009 8:55 AM by gsainc

    value list duplicate entries issue

    gsainc

      Title

      value list duplicate entries issue

      Post

      I have a DB with a value list that list contact names. This list is populated by a field 'contact:name' and shows only related entries according to 'contacts:comapny' so that the list only shows relevant contact names for the previously choosen company. The problem I am encountering is that there are multiple companies with a contact listed for 'John Doe'. When this occurs the list shows John Doe for each of the companies as it should... but other fields such as email and phone number which populate according to the contact name selected always populate with the first instance of the name. This says to me that the first instance is always showing up in the value list due to the value list not displaying duplicate entries. Is there a way I can get the value list to display duplicate entries?

        • 1. Re: value list duplicate entries issue
          philmodjunk
            

          Describe your value list in more detail. Are you using one column or a two column list with the first column hidden? How are your tables related? by a serial ID number?

           

          Let's pretend we've fixed this problem for a moment:  When you pull down a menu and see John Doe listed several times, how is the user to know which John Doe is the correct choice?

          • 2. Re: value list duplicate entries issue
            obeechi
              

            Could you clarify your database structure, like list the tables and their fields, and what the relationships are?

            • 3. Re: value list duplicate entries issue
              gsainc
                

              The value list is one column and shows results from: table= 'contacts' field= 'name'

              there is another filed in the table 'contacts' called 'company'. This is used as a relation point to table= 'company' field= 'name'

              with this my value list shows only related entries (when a company is choosen the contact name value list populates to show the contacts only for that company)

               

              It works right except when two companies have a contact with the same name. 

              • 4. Re: value list duplicate entries issue
                philmodjunk
                  

                "with this my value list shows only related entries."

                Then you shouldn't see names from other companies in your value list. The relationship should eliminate them from the list.

                 

                It looks like the problem lies with your relationship between the two tables. I think you have a relationship based on the person's name and you are using this relationship to refer to data in your company table. In that case, when you select a name, the system will refer to the oldest such matching record in your contacts table unless you specify a sort order in your relationship.

                 

                You need a serial ID field in contacts that uniquely identifies each person. You should then use this relationship to lookup or refer to data when selecting a person from your value list.

                 

                Your field should be an ID field formatted with a two column value list. Column 1 is your ID number and column 2 is the person's name. With this set up, selecting a contact ID number will match to only one contact record and you'll be able to refer to the correct data.

                • 5. Re: value list duplicate entries issue
                  gsainc
                    

                  I was a little mistaken in the error in my database. Here is an update to what I am seeing.

                   

                  a little background:

                  I have three tables relevant to this issue.

                   • jobs

                   • customers

                   • contacts

                  table relationships are as follows:

                   • jobs:customer -> customers:name

                   • customers:id -> contacts:id

                   • contacts:name -> jobs 2:contact

                  customer:id is an auto enter serial and contacts:id is a number field set to match customer:id when a new record is created in the contacts table.

                   

                  In the table 'jobs' there is a calculation field jobs:contactphone

                  this pulls in the phone number from contacts:phone

                   

                  Here is my issue. When two customers have contacts with the same name the correct customer/contact is being displayed in the value list (I checked this by displaying the id field for both tables and they are matching correctly) but when you choose the contact and the phone field fills in, the phone number which comes first sorted by record number is loaded.

                   

                  so:

                  1st case example  - contact:name=john contact:id=12   jobs:contactphone calculates to contact:phone for contact:id=12

                  2nd case example - contact:name=john contact:id=97   jobs:contactphone calculates to contact:phone for contact:id=12

                   

                   

                  ??? 

                   

                   

                  PS - What's witht the smilies? 

                  • 6. Re: value list duplicate entries issue
                    gsainc
                      

                    Solved!

                     

                    I created a 2 fields jobs:cid and contacts:cid

                    both are calculations contact name & contact id

                    then I made this the related field between jobs and contact 2