6 Replies Latest reply on Sep 19, 2012 11:20 AM by philmodjunk

    Related Drop Down List

    ToddBest

      Title

      Related Drop Down List

      Post

            

           Not new to databases, new to FileMaker..

            

           I have one database containing 2 tables, Contacts and Companies. One company can have several contacts. I have related them through CompanyID. When a new contact is entered, I would like for them to select the company from a drop down list. But I want the drop down list to show a calculated field CompNameDiv(Company Name + Division Name) that they choose from, and store the appropriate CompanyID with the Contact record.

            

           My first attempt was using a value list that used the Value from CompanyID and showed the value form the CompNameDiv. This did not work, as the record reverts back to showing the CompanyID. 

            

           I think that the issue is that there is no company yet related to a contact, and therefore, it has nothing to display?

            

           thxs

        • 1. Re: Related Drop Down List
          schamblee

               You need another relationship.  You need a self join companyId using the cartesian "X".

               Here is a sample app.

          https://www.sugarsync.com/pf/D9559058_790_711361711

          • 2. Re: Related Drop Down List
            philmodjunk

                 My first attempt was using a value list that used the Value from CompanyID and showed the value form the CompNameDiv. This did not work, as the record reverts back to showing the CompanyID.

                 This relationship:

                 Contact::CompanyID = Company::CompanyID

                 appears to be the relationship that you already have and should be all that you need for this to work.

                 It sounds like you formatted Contact::CompanyID with a drop down list where CompanyID and CompNameDiv from Company are the first and second fields of your value list and you selected the option to hide the ID field. That correct?

                 If so, when you exit the Contact::CompanyID field, you will see the ID number as that is the value that was actually entered into the field. But there are two basic ways you can show the company name and division.

                 1) Simply place the CompanyNameDiv field from companies on your layout and it will display this data once you select a company from the drop down list. You can even set field behavior on this field to prevent access when in browse mode, give it an opaque fill color and place it on top of the drip down list field. When you click on this field, the drop down list pops to the front and deploys, when you select a value, the list disappears back behind the CompanyNameDiv field--which now displays the company name and division of the company you just selected. BTW you can add any fields from Company that you want to your contact layout and they will display data from the company you select from the value list.

                 2) Use a pop up menu instead of a drop down list. Pop up's behave a bit differently, but if you select the "hide first field" option, the second field is displayed in the field after you exit the field.

            • 3. Re: Related Drop Down List
              philmodjunk

                   Oh yes, and CompNameDiv should be defined as:

                   Company Name & " " & Division Name

                   not:

                   Company Name + Division Name

              • 4. Re: Related Drop Down List
                ToddBest

                     Worked exactly like I needed it too. Have lots of reading to do on the Cartesian X. 

                     The laying over of an edit box on top of the drop down is definately added to the toolbox. Love stupidly simple solutions. 

                     Thanks!

                • 5. Re: Related Drop Down List
                  schamblee

                       Phil is right, you don't have to use the "x" .  You just need the self join.  Didn't mean to miss lead anyone, just got in a hurry making example.  

                  • 6. Re: Related Drop Down List
                    philmodjunk

                         S Chamblee,

                         Just being picky about details here, but a "self join" is a relationship that links a table to itself by using a second occurrence of the same data source table.

                         We don't have that type of "join" here and such a join is not needed in order to get this to work.