Repeating Field conversions - stumped

[aliquis]

Following the recurrent sound advice on this forum I’m trying to convert all my repeating fields into properly related tables, but I’ve run into a problem.

I have two main tables: Courses and Staff, which I want to join in a many-to-many relation so that eventually I can see which staff will be teaching which courses (from the Staff layout) and which courses will be taught by which staff (from the Courses layout.) Record creation needs to work in both directions. I know the basic principle of how to do that by creating two one-to-many relations through multiple table occurrences.

But the intervening variable is Timeslots. Each Course may run in a number of different timeslots, and I need to map the Staff to the Courses via the particular Timeslot, rather than to the Course as such.

So to create the Timeslots, I’ve created a separate table (Courses Timeslot) to store the multiple Course/Timeslot records. This is accessible through a portal on the Course layout. But I don’t know how to connect up the Staff table to achieve my object in para 2.

Example:

Course 1 In Timeslot 1 is taught by Staff 1 and 2;

Course 1 in Timeslot 2 is taught by Staff 1 and 3;

Course 2 in Timeslot 2 is taught by Staff 4 and 5 and so on.

I’ve attached the basic outline (half constructed) of what I’m trying to get at. Could any of you heavy-duty portal people point me in the right direction please? I’m sure it must be possible to do this but there’s a basic concept of constructing the relationships that I’m not seeing.

Example.fp7.zip

[comment]

I believe you need four tables: Courses, Classes (what you call Timeslots), Staff and Assignments. Assignments is a join table between Classes and Staff, so that:

• a Course has many Classes;

• a Class has many assigned Staff members;

• a Staff member has many assigned Classes.

[aliquis]

Thanks veteran - I thought I'd suddenly seen the light when I worked through your reply. But it's now gone all dark again. I've attached the first stages of what you suggested, but what I'm really struggling with is the multiplicity at each level. The course record will have multiple timeslots and then each timeslot can have multiple staff. The extra table you suggested has the 'unique' combinations for each permutation, but I can't work out how to to do this without having a portal within a portal, which I know is not allowed.

Example.fp7.zip

[comment]

What you have is essentially correct. However, you cannot just add staff to a course - because such assignment requires you to select a specific timeslot first. The way you have it now, it will automatically get assigned to the first related timeslot of the current course. You could of course change it afterwards, but it's not a good arrangement to have.

You could either go to a selected timeslot record and assign the staff from there, or select one of the current course's timeslots (by clicking on it and placing its ID in a global field in Courses). Define another relationship between Courses and a new occurrence of Assignments, based on the global. You can then add staff to the selected timeslot in a portal based on on the new relationship.

[aliquis]

Yes I see what you mean about not just adding staff to a course - seems obvious when you say it. But I don't follow the last bit of your suggestion.

1 I should create an ID field for Timeslots - presumably I can just do a calculation field that concatenates Timeslot & ID

2 I then create a global field in Courses (but how do I get the Timeslot ID there automatically by clicking? You mean a script?)

3 Then create another TO for Assignments (do you mean Timeslots?)...

...But sorry - I'm struggling

Example.fp7.zip

[comment]

See if this makes it any clearer.

Note that this is NOT meant to be a demo of user interface - whichever method you choose, this aspect requires a lot more work.

Courses.fp7.zip

[aliquis]

Only just had a chance to look at this, but wow! Thanks so much - it looks to be just what I was after. It's going to take me a little while to work out how it fits together, as some of the principles seem new to me. But in the meantime thank you.

[aliquis]

I think I've got this sorted now and have started to implement in my full database using the 2nd of your editing options because I would like to do all the editing done from within the same layout rather than using GRR. But I have one question.

If each course has a maximum of 4 classes I'd like to represent and edit the staffing for all of these simultaneously from within the courses layout, rather than having a single 'edit portal' which is switched from one timeslot to another by setting the global field manually as in your solution.

I realise that to do this I need to have 4 sets of table occurrences, each of which is linked to the course table via the class ID. The class IDs will then be stored in each course record, since if I do it this way, they won't be global fields - I can just have 4 ordinary text fields, as long as they contain the relevant class ID.

But what I would ideally like to do is to set them as calculation fields so that the class ID is entered automatically. But my sense is that this is not possible because it’s not possible to set up a calculation to read data from a specified portal row. If so then I’m just going to have to do it manually via a script as in your solution. Am I right here? Am I stuck with a button if I want to edit the staffing from within the courses layout?

[comment]

It's quite possible to view and edit all assignments for a Course from a single portal (see attached). You cannot, however, use this portal to create new asignments by entering data into the last portal row. The reason for this is that the portal shows all assignments for all the classes of a course - while an assignment needs one specific class.

To add a new assignment, you can either pop a window into a selected class (same as before), or enter the data into global fields first, then use a script to create a new assignment record and populate it with data entered into the globals.

Courses2.fp7.zip

[aliquis]

I think I see what you're saying, but it's precisely that kind of record creation that I want to be able to do from the course layout. You did it in your previous example by including an editable portal from the Assignments table on the layout. In that example you could add Faculty (without opening a separate window). Which class this portal displays is switched by the button that sets the global field. But by having 4 portals on the layout, one for each possible class I don't need to keep switching - I can just go to the portal I want to edit. But to make this work I have to set up 4 match fields containing the 4 class IDs. That's easy enough to script, but I was wondering whether it was possible to get the foreign-key match fields to 'read' the 4 class IDs from a portal to the Classes table automatically without using any buttons. Am I making sense?

Edited February 21, 2008 by Guest

[comment]

It's not very difficult. But it's not a solution I particularly like. It's not (only) that you need to replicate the same structure 4 times. There's a conceptual "wrongness" in it being hard-coded for a particular number of instances. Why 4 and not 3 or 5, or any number? In a relational database, the only numbers that make sense are zero, one and infinity.

Courses3.fp7.zip

[aliquis]

Thank you - yes, that's exactly what I was looking for. I take your point too though about it not being very satisfactory. But with the different examples you've given me I think I'll be able to work something out. I might even go back to one of your original solutions.

All this might not be difficult for you, but it sure stretches me. Thanks for your help here. Much appreciated.