9 Replies Latest reply on Jun 26, 2009 6:39 AM by jeffd

    Value List Question

    jeffd

      Title

      Value List Question

      Post

      My "Contacts" value list draws form a field in a related file. I am using the values form the first field which is companynames and I am displaying a second field which is a division of the company names. The problem is this. I have multiple company names that are identical with different division names. When using the value list, it is only displaying one of the company names. e.g. I have many "State of Alabama" company name records with its many state agencies listed in a second field which I call 'divisons'. Why isn't the value list displaying all names? 

        • 1. Re: Value List Question
          philmodjunk
            

          Are you hiding column 1, the company name field, and only showing column 2?

           

          That would have the effect you are describing.

          • 2. Re: Value List Question
            comment_1
              

            A value list does not contain duplicates. Since in your example the user will be entering "State of Alabama" into the field, no matter which division they pick, it doesn't really make any difference.

             

            The correct way to do this would be to base the value list upon a unique CompanyID, and also show a second field concatenating the company name and division.

            • 3. Re: Value List Question
              philmodjunk
                

              I agree with comment.

               

              Another variation on this comes to mind:

               

              Make two value lists. The first can be your company list which is best based on company ID not company name as company names change. The second can be your division, but set it up so that it is limited to only those departments associated with the selected company.

              • 4. Re: Value List Question
                jeffd
                   Would you give me a little more insight on how I go about setting this value list based on the company ID. How do I link all this together, with the division. Keep in mind that I am using 2 files, one is the customer file and the other is a quoting program that uses the customer file. 
                • 5. Re: Value List Question
                  comment_1
                    

                  Does your customer file already have a unique ID field - such as an auto-entered serial number?

                   

                   

                  BTW, why don't you consolidate the two files into one?

                   

                   

                  • 6. Re: Value List Question
                    jeffd
                      

                    I will use the record ID number for each customer. I hope this will be ok, since each is unique.

                    I will have 4 total files using this customer file.

                    • 7. Re: Value List Question
                      comment_1
                        

                      Jeff_D wrote:
                      I will have 4 total files using this customer file.

                      I know this is off-topic - but why don't you consolidate ALL your tables into one file? Not that it's necessary for this, but it's convenient for a lot of reasons (unless you have specific reasons not to).


                      • 8. Re: Value List Question
                        philmodjunk
                          

                        You can define a value list where column 1 is the ID field and column 2 is the name field (or comment's name and division combination field). You can hide the first column or not as you choose. If you hide the first column and make the list format a 'pop up menu', then the user will see the visible column 2 even though they are really entering the column 1 ID number.

                         

                        The main gripe I have with this approach is that you can't enable auto-complete when the first column is a number field. This is not a limitation in other Database systems and the loss of this feature when pulling up a list with a lot of values is a major pain. I put up with it because the danger to your table's data integrity if you use a name field as your Primary Key is more critical than the user convenience of auto-complete.

                        • 9. Re: Value List Question
                          jeffd
                            

                          I've been thinking of doing what you suggested. I've been usng some of these other files for at least 10 years. I'm just a rookie at databases, hardly any thought or planning went into building & relating these files. Now with FMP10, I want to make life a whole lot simpler.

                          Thanks for your suggestion.