Title

Setting up Relationships for Dynamic value lists

Post

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!!!

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:

Facilitator>-----Facilitator_PERIOD

_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.

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.

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.

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.

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.