7 Replies Latest reply on Nov 13, 2014 4:04 PM by philmodjunk

    Conditional value list - no values defined

    BenjaminDestrempes

      Title

      Conditional value list - no values defined

      Post

      Hi,

      I am having some issues with relationships for a database I'm working on. I know this is a simple issue and I must be tired but for the life of me, I can't get this to work.

      I have the following tables:

             
      • Opportunities
      •      
      • Accounts
      •      
      • Contacts
      •      
      • Work Orders

       

      Each opportunity has an associated fk_account_id field, each account has a fk_contact_id field, and each work order has a fk_opportunity_id field. The relationships are as follows:

             
      • Opportunities::fk_account_id = Accounts::id
      •      
      • Accounts::id = Contacts::id
      •      
      • Work_Orders::fk_opportunity_id = Opportunities::id - allow creation of entries in Work_Orders

       

      I have a portal on the Opportunities layout that contains a pop-up menu that should let me choose any contact associated with the opportunity's account. The pop-up menu, however, does not display the correct entries. The value list uses the values from the Contacts::ID and Contacts::Name_Full fields, displaying values from the second field and is selected to include only related values starting from Accounts. However, clicking on the popup menu only shows <no values defined>. Unchecking the include only related values checkbox makes it show all values from the Contacts table.

      I know I'm probably making a simple mistake in my relationships but I can't figure out what. Any help will be appreciated!

        • 1. Re: Conditional value list - no values defined
          philmodjunk

          I'm having trouble puzzling out your relationships.

          It isn't immediately clear from your field names whether one account matches to many contacts or if one contact matches to many accounts.

          I think that you have:

          Contacts-----<accounts ----<Opportunities>----Work Orders   (---< means "one to many")

          But I could easily be wrong.

          I have a portal on the Opportunities layout

          And that would be a portal to what table here?

          • 2. Re: Conditional value list - no values defined
            BenjaminDestrempes

            Hi,

            Apologies for the confusion. The relationships are exactly as you think they are. The portal on Opportunities shows entries from the Work Orders table. I would like to be able to assign a specific contact to the different work order entries, but to only show the contacts associated to the account that is associated with that opportunity in my popup menu..

            • 3. Re: Conditional value list - no values defined
              philmodjunk

              The portal on Opportunities shows entries from the Work Orders table.

              With the relationships that I have described, that portal would only be able to list a single work order as you would have many opportunities linked to one work order. I must have been tired last night also. You clearly have a one to many relationship from opportunities to work orders.

              Contacts-----<accounts ----<Opportunities----<Work Orders

              a) try using Opportunities as your "starting from" table occurrence in the value list.

              b) if that doesn't work, try putting a portal to contacts on your Opportunities layout as a test to see if you see any records appear in the portal. If it doesn't, something is wrong--either with your relationships or the values in your match fields.

              PS. I would rename Accounts::id to be Accounts;;fkContactID to avoid possible confusion. (And if Accounts::id is actually the field that uniquely identifies accounts, you have just identified the problem as this is the wrong field to use in your relationship to Contacts.)

              • 4. Re: Conditional value list - no values defined
                BenjaminDestrempes

                You were correct, using Opportunities as the "starting from" table occurance solved the issue popup menu issue.

                Is there a way now to display values in the portal related to the selected contact? Contact::Email, for example? Simply adding a Contact::Email field gives some unexpected results. It always display the email of the first contact in the value list, no matter which contact I choose.

                As for the contacts and accounts relationship, it's actually Accounts::ID > Contacts::fk_account_id, does that make more sense?

                • 5. Re: Conditional value list - no values defined
                  philmodjunk

                  As for the contacts and accounts relationship, it's actually Accounts::ID > Contacts::fk_account_id, does that make more sense?

                  It changes the relationships that you said that I had exactly right.

                  Contacts>-----accounts ----<Opportunities----<Work Orders

                  This means that you can have multiple contacts linked to the same account.

                  If you are selecting a value in the work orders table that identifies a specific contact, you need to add a new occurrence of Contacts where you value list formatted field in work orders is used as the match field to this new occurrence.

                  Work Orders>----Contacts|WorkOrders

                  Work Orders::_fkContactID = Contacts|WorkOrdrs::ContactID

                  • 6. Re: Conditional value list - no values defined
                    BenjaminDestrempes

                    That did the trick, thanks a lot!

                    • 7. Re: Conditional value list - no values defined
                      philmodjunk

                      For more on conditional value lists, see "Adventures in FileMaking #1 - conditional value lists".

                      It demonstrates 8 different forms of conditional value lists.