3 Replies Latest reply on Aug 31, 2011 11:21 AM by philmodjunk

    relationships and dropdown

    patfee

      Title

      relationships and dropdown

      Post

      Hi,

      i'm trying to create the following:

      Client Table, with a PK & Name
      Client Contact Table, with a PK, a FK Client and a Name
      Project Table, with a PK, a FK Client, a FK Client Contact

      so far so good

      in the Project Form i want to select a client using a dropdown box

      In the Project Form i want to select a client contact using a dropdown box that is only showing the contacts related to that client

      i cant seem to manage this

      could someone maybe issue an example file? or direct me to a tutorial?

      many thanks

      PAtrick 

        • 1. Re: relationships and dropdown
          philmodjunk

          Your client and client contact tables seem very similar--possibly identical in terms of the fields defined. Are they the same in structure or nearly so?

          Could the same client contact be a contact for more than one client?

          Those are issues that could lead you to modifying your database structure.

          What you describe with your drop down is called a conditional value list.

          Your first drop down should be a drop down that uses a value list of Client PK's in field 1 and either client names or a calculation field that combines client names with additional data such as an address (this can make it easier to distinguish between clients with the same or very similar names.)

          Then your second drop down will use a conditional value list. Please note that this requires a relationship between Project and Client Contact based on the FK Client fields in both tables and will require and extra occurrence of the client contact table.

          For more on conditional value lists:

          Forum Tutorial: Custom Value List?

          Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

          Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

          • 2. Re: relationships and dropdown
            patfee

            Thanks for your reply

            with regards to the tables and fields, i just kept it simple to serve as an example.

            In reality it will be a client with many contacts (i.e. a project manager, a contracts manager, etc.)

            i have managed to build the knowledgebase file, and also understand what i'm doing Laughing

             

            But what i dont understand is why the relationship isnt made to a Primary key and/or foreign keys?

            At least that is what i have been doing using ms access

             

            i can do so as well in FM using the same technique, but that in the drop down, i see the primary key and not the text. 

            I know i can select the option to show a second field, but once selected, the PK is shown again.

            Is there a way around this? or should i work with Primary keys?

             

            Thanks again

            Patrick

            • 3. Re: relationships and dropdown
              philmodjunk

              Primary keys are definitely the first option to use if available, but conditional value lists (this is also true in Access) are often based on values entered in fields that cannot be defined as primary keys as they may not be unique values.

              When you use a value list to select an ID number, you can hide the first field (2nd field values must be unique), and use the pop up menu option instead of a drop down list. You can also place the "2nd field" from the related table next to or even on top of your drop down list field. If you use the inspector's behavior settings to deny access to this related field, the drop down field can be hidden behind it. In browse, clicking on the related field will cause the hidden drop down field to pop to the front and deploy. For Access folks, that will seem especially odd, but it does work.