3 Replies Latest reply on Oct 14, 2013 1:18 PM by philmodjunk

    Dynamic Value List

    fayman

      Title

      Dynamic Value List

      Post

           I have a Project database containing a project layout, and customer layout.  When creating a new project within the project layout, I'd like to allow employees to create new customer records from the project record, including adding mulitple contact names and emails.

           Once entered, the customer information within the customer layout would be accessible as employees created new projects, in that they would select the existing customer from a pop-up menu, and then select appropriate contact and email (filtered) for that customer.

           This is a simple dynamic value list function, but I'm overthinking it and it's driving me mad...

        • 1. Re: Dynamic Value List
          philmodjunk
               

                    I'd like to allow employees to create new customer records from the project record, including adding multiple contact names and emails.

               And what tables and relationships exist in your database to facilitate this. To me, this sounds like a many to many relationship where on project can link to many contacts and one contact might be linked to many projects.

               Is that the case?

               Do you have the needed tables and relationships in place for such a relationship?

          • 2. Re: Dynamic Value List
            fayman

                 That's what I'm struggling to grasp.

                 I have a "Project" table containing Customer/Contact/Email fields.  I also have a "Customer" table containing the same fields.  I am struggling to understand how to relate, and whether I should be creating additional tables for the customer info.

                 I'd need the fields in the project layout to be pop-up menus with related customers from the "Customer" table.  I would also need to be able to add new customers through this field.  Choosing an existing customer from that pop-up menu would filter the Contact/Email fields accordingly so that the user is given the correct list of customer contacts.

                 Actual situational usage:  Sales rep creates a new project, and can create a customer name, multiple contact names, and email addresses.  Additional future projects will be created, and will now allow the sales rep to choose the existing customer, and then choose a specific contact name and email address based on the list of multiple names.

                 I apologize for the length....I am a true novice.

            • 3. Re: Dynamic Value List
              philmodjunk

                   It sounds like you need these tables/relationships:

                   Start with these relationships:

                   Contacts-----<Contact_Project>-----Projects

                   Contacts::__pkContactID = Contact_Project::_fkContactID
                   Projects::__pkProjectID = Contact_Project::_fkProjectID

                   You can place a portal to Contact_Project on the Contacts layout to list and select a Projects record for each given Contacts record. Fields from Projects can be included in the Portal to show additional info about each selected Projects record and the _fkProjectID field can be set up with a value list for selecting Projects records by their ID field.

                   Keep in mind that this is just a "beginner level" approach. There are a number of enhancements that are possible that can make the resulting layouts much more user friendly.

                   You also appear to have multiple "contacts" for a given "customer". That suggests this relationship:

                   Customer----<Contacts

                   Customer::__pkCustomerID = Contacts::_fkCustomerID

                   Though in some cases, this can also require a many to many relationship if there is any chance that a given contact might work for/represent more than one customer.

                   And there is more than one way that you can set up what happens when someone selects a Customer for a given project. Selecting a customer could automatically link all contacts for that customer to the project or it could serve to limit the list of possible contacts from which the user would then make specific selections for the current contact. Which is the better approach, (and they aren't mutually exclusive) will determine the precise relationships needed to support it.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained