5 Replies Latest reply on Jun 24, 2013 10:59 AM by philmodjunk

    Setting up Relationships for Dynamic value lists



      Setting up Relationships for Dynamic value lists


           I have been through the education materials but still can't seem to apply this to the database I am building.

           I am building a database to track rotations medical students do. The students do two periods and do two separate services on those two periods. The service name is housed in the 'Facilitator' table. I am trying to set up an easy entry form to select the period that shows the period and the dates (i.e. "Period 1 (5/13/13-7/15/13)" ) from the period table. The student's name ( "FirstName LastName" ) and then the service they will be rotating on. 

           I can't seem to figure out the relationships to get them to populate. Any help would be greatly appreciated!!!

           Thank you, Ali


        • 1. Re: Setting up Relationships for Dynamic value lists

               I don't see what a relationship has to do with your value list. Normally, a relationship is used to create a conditional value list and that relationship then "filters" the list of values to only display a subset of the total list of values. But you haven't described any such "filtering" in your original post.

               This relationship looks incorrect:


               _kf_PeriodID_1 = __kp_PeriodID AND
               _kf_PeriodID_2 = __kp_PeriodID

               that relationship will only match records in the two tables if _kf_PeriodID_1, __kp_PeriodID AND _kf_PeriodID_2 all have exactly the same value.

          • 2. Re: Setting up Relationships for Dynamic value lists

                 I'm sure I am making this harder than it really is. 

                 With the1st and 2nd  Period's I was attempting to figure out how to fix the many-to-many relationship. I moved it around and i'm not sure yet if it makes sence. Like the example that is given where you add the 'Line Item' table to fix the many-to-many relationship between the product and the invoice. A student will have more than one period (usually 2) and a period will have a number students (usually around 20-30). I attached my new attempt at the relationship graph. The 'Period_1st' and 'Period_2nd' are copies of the 'Period' table. A student can only be on 1 service per period but they do more than one period in a year, therefore they do more than one service (in Facilitator table)...

                 I setup a form associated with the 'Student' table. I have 3 fields in the form that I want to be a dropdown list from other tables.

                 1. Student's Name (got this)

                 2. Period the student is currently on which will be populated from the 'Period' table. On the form I want it to read something like, "Period 1 (6/13/13-8/1/13)

                 3. would be the service the student will be on during the period selected. This would be taken from the 'Facilitator' table which has a service field.


                 This is what I used, though it doesn't work, to set up the 'Period' drop down list.

                 I selected 'student_PERIOD::__kp_PeriodID' to where the data should come from. Then in the value list I selected 'Period' and the field 'PeriodYear' which is a calculation to combine two fields together (i.e. Period & "(" & PeriodStartingDate & "-" & PeriodEndingDate & ")" )

                 On the Relationship graph I have the names set-up to where the all caps is the orginal table and the lowercase is the table that I am connecting it to. They are also color coded. the student tables are in green, period tables is in purple, etc.

                 Hopefully this makes sence. Thank you so much for your help! 

            • 3. Re: Setting up Relationships for Dynamic value lists

                   A many to many relationship usually requires a join table between the two tables linked in the many to many relationship.


                   Period::__pkPeriodID = Service::_fkPeriodID
                   Student::__pkStudentID = Service::_fkStudentID

                   Thus, each record in Service, the join table, links a specific record in Period to a specific record in Student. Since you have a different Facilitator for each service, you'd link them by FacilitatorID to each record in Service as well.

                   A portal to service on a Period based layout would list all students and their specified service for that period. A portal to Service on a Student layout would list all service assignments for that student.

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

              • 4. Re: Setting up Relationships for Dynamic value lists

                     How can i connect them so I can use this form for data entry? The Students, services, and periods will already be loaded into the system. I want to use this form assign the students to the periods and services. 

                • 5. Re: Setting up Relationships for Dynamic value lists

                       A portal to the Join table is most frequently used for this purpose--though other methods can also be used. Please check out the demo file as it shows the "basic setup" for such a portal as well as a layout with a different interface option that simulates a check box list for selecting items.