Conditional Value List

[bevo1801]

I have one table (Instructors), related to 3 separate tables (Days_Available, Certifications, and Addresses) by a primary key.

In a fifth table (Events), I need to be able to select and schedule Instructors based on information in the 3 related tables (Days_Available, Certifications, and Addresses). Ideally, I would like to see a filtered value list of Instructors that meet the pre-defined criteria of the event. In other words, choose from a list of Instructors that have specified they are available the day of the event, but also meet the event certification requirements, and live in the home state of the event.

Step Two of this would be to make the Instructor "Unavailable" for that day (removed from the list), once they are scheduled.

This is probably not as complicated as I think, but I am having a mental block. I have created filter keys before relating to one table, but not to 3. Is this possible? Suggestions would be greatly appreciated. Thanks.

Table 1: Instructors

Primary_Key

Table 2: Days Available

Primary_Key

Date

Table 3: Certifications

Primary_Key

Certification_Type

Table 4: Addresses

Primary_Key

Home_State

Table 5: Events

Event_Date

Certification_Required

Event_State

Primary_Key

[bcooney]

I put together a demo. Hope it helps. Although this works, it uses multikeys instead of join tables. However, I couldn't see any other way to have the stored values in the Instructors table that you need for the relationship.

All comments welcome.

PS Didn't see the unavailable date req until after I posted this. Will try for that now.

Topic_192790.zip

Edited January 27, 2008 by Guest

[comment]

I think we need to clarify which tables are real entities, and which are merely value lists. Certainly, the structure as described doesn't make much sense: for example, the Days Available table needs an InstructorID foreign key.

Assuming the Instructors table includes an Address field (why would we even need a separate table for this?) and a Certifications field (with multiple values allowed), this would only leave AvailableDates as a separate table - a child table of Instructors.

Matching Instructors with Events on Address AND Certification could produce a list of qualified Instructors who live there. Matching AvailableDates with Events on Date could yield a similar list of Instructors who are free on that date. Filtering the two lists by each other, using FilterValues(), would get us the final list of eligible candidates.

It's possible to extend this process to additional criteria derived from more child tables - but at some point it may be more efficient just to use find.

[bevo1801]

Thank you very much. It did open my eyes to some other ideas. I am still having issues with storing the values.

To clarify:

The Instructors table consists of Instructor_ID, Home_ST, _mk_Avail, _mk_Cert.

The Instructor_Days_Available table consists of only 2 fields. The Instructor_ID, and a Date field.

The Certifications table consists of only 2 fields. Certification_ID, and Certification_Name. It's used primarily as a value list, but the certification names are long, so I decided to put them in their own table with a unique ID.

The Instructor_Certifications table is basically a join file, consisting of Certification_ID and Instructor_ID. Some Instructors have only one certification, some have up to 10.

I decided to do away with the Address table for this purpose. Originally, this table was created to allow multiple addresses for each Instructor (Home, Work, Vacation home, etc). For scheduling purposes, I added a Home_ST field to the Instructors table to eliminate the extra confusion.

Finally, the Events table consists of an Event ID, Location_State, Certification Requirement (from Certification_ID), and a Event_Date field. Once the location, certification and date are specified, then I was hoping to see a filtered list of Instructor_IDs that meet that criteria. This still created an additional problem because I can see some cases where no single Instructor will meet all 3 requirements, and therefore, there are no values in the list. So, I have also created a value list to filter by only Certification and Date (and not Location), and a 3rd value list to filter only by Certification.

Using your demo, I was able to get the related portal records into a single field in the Instructor table. One for Days_Available, and one for Instructor_Certifications. That is where I reached the problem of storing the values. My immediate solution was to create an update script that copies the unstored related values, to a stored values field using a Set field script step. I don't like it, but that is where I am at. I am still open to further suggestions.

Once that is working, I will need to remove the Date_Available record for an Instructor, once they are scheduled for an Event.

Again, many thanks to both of you for your help and suggestions.

[comment]

The Instructors table consists of Instructor_ID, Home_ST, _mk_Avail, _mk_Cert.

What are _mk_Avail and _mk_Cert?

[bevo1801]

_mk_Cert is an unstored calc (text result)

ValueListItems( Get(FileName); "Instructor Certifications") & Left(Instructor_Certifications::Certification_ID;0)

_mk_Avail is an unstored calc (text result):

ValueListItems( Get(FileName); "Instructor Days Available") & Left(Instructor_Days_Available::Date;0)

These are generating a return delimited list in a single field for each. Because the values are unstored, my only solution so far was to copy the values via script to a stored field. The "stored" field is used in a match in the relationship with the Events table. The filter appears to be working correctly with this method, but I still do not like having the script and that manual step. There has to be a better way. :)

[comment]

As I said, you need to get the list of eligible InstructorID's from each relationship, filter the lists by each other, and base a new relationship on the result.

Or just do a find.

Edited January 29, 2008 by Guest

[bcooney]

The issue that I tried to address in the demo is the need for stored values in the child table. In this case, I was hoping for one relationship btw Event and Instructors using a multi-criteria relationship (State, Certification, Date of Event). So, to have stored certification ids in the Instructor table, I used a multikey of Certifications rather than a join table btw Instructors and Certifications. Likewise, I used a multikey for Dates Available, rather than a join between a Calendar table and Instructors.

But, as I said above, after I finished I saw the requirement for further filtering instructors from the list of available if they are already booked for that date. I'm thinking that join tables and find mode might be a less convoluted approach.

Edited January 29, 2008 by Guest