Cannot create problem

[David Jondreau]

I'm trying to create related records in a filtered portal but having some trouble.

I get the error: "This operation cannot be performed because one or more required related records are not available and cannot be created."

I have a table of Tutors (1:1 to a People table). I want to show all those Tutors on a layout for a Tutoring table and allow the user to select a Tutor to see and add Contact Info. I want to use a filtered portal but am having trouble.

A picture is worth a thousand words...a file is worth more. Please see attached.

(The actual file is much more complicated, so there are apparently extraneous fields in this file, but they do have a purpose).

PortalProblem.fp7.zip

[comment]

I don't think this is related to portal filtering. You are on a layout of Tutoring, and you are trying to create a related contact through a relationship using the x relational operator.

[David Jondreau]

I think you're on the right track.

If I switch the cartesian to a constant to a constant and I get the same error. If I allow creation then it creates a new Tutor record.

So...I don't I understand the implications of "Allow creation..." on a table a couple hops away. I do want to create a related record in a table two TOs from the TO the layout is based on. Why wouldn't that work (cartesian or no)? The "intermediate" parent exists.

[comment]

The "intermediate" parent exists.

Does it? Which one exactly is it?

[David Jondreau]

The script in the portal Contact Info is setting the Contact Info::People ID to one of the existing Tutor::People IDs. Even if I put an existing Tutor::People ID in manually to Contact Info::People ID it gets rejected.

Now if I type in the Number field, it creates a record, but sets Contact Info::People ID to the first related from Tutor::People ID.

[comment]

You are right about that part. It has something to do with your trying to set the match field. This works from the parent table (and then the relationship overrides your value), but not from the grandparent.

[stefwef]

What is the "tutoring" table for?

It seems to me that your Tutors are people too, no?

There are a bunch of ways to do this. You have a tutor table with tutor IDs and a student table with student IDs. You can keep this schema but I would change it a bit so that both of these tables are linked to your contact table.

It looks to me like you are trying to assign students to tutors?

If so, you have a few options. Here is one that does the "vice versa" scenario (tutors to students):

Create another table that is a "join" table that has 2 fields: tutor ID and student ID.

Now in the relationship graph, your relationships would look like this

TUTORS=JOIN

tutor ID=tutor ID

JOIN=STUDENTS

student ID=student ID

Now when you are in tutors table you can place a portal to "join" in which you can display students assigned to tutors.

In tutors you can place portal to join in which you can display students.

Now if you want to display contact information for either students or tutors, you need to link the contact table to them.

STUDENT=CONTACT

Student ID=Contact ID / where the student::ID is the auto enter "text" field like S1...

allow entry.

TUTOR=CONTACT2

Tutor ID= contactID / where the tutor::ID is the autoenter "text" field like T1...

allow entry.

The contact tables are not on a layout, but you create portal from either student or tutor tables.

This contact table would also hold your availability days/times for both students and tutors. And in turn you should be able to show this through the join portal.

Then to assign a student to a tutor you need a script. This will be something like:

set var $tutorID

open new window

show student list

pause

(make sure there is a select button on your list layout that resumes script)

set var $student

Go to join table

new record

set field:student ID

set field:tutrID

close window

refresh/join cache

Sorry - my husband has turned the movie on / not time to finish. but hopefully this helps youout!

- S

[David Jondreau]

I appreciate your effort to help, but I am looking for answer to a specific question.

Why can't I create contact info record in this situation?

[comment]

I am not sure I have an answer to your question. It might be a quirk or a residual result of the auto-creation mechanism. Keep in mind that in a simple arrangement:

Grandparent -< Parent -< Child

you CAN create a child from the Grandparent this way - if BOTH relationships are set to allow creation. In such case, a new parent is always created - even if you set the Child::ParentID to the ID of an existing parent.

OTOH, setting a field other than the matchfield creates a child of the first related parent - and the Grandparent -< Parent relationship doesn't have to allow creation.

[stefwef]

Sorry I did not check on this again. The question in your file does not match the question on the forum - and setting $$Select to tutor::peopleID and student::peopleID to $$Select are working (when a student portal row exists to click on).

Is it that you are surprised that you cannot click to assign $$var to empty student::portal row? This is normal, as the student::peopleID is the related field, it "autoenters" into the related tutors::peopleID (which since you are in "turoring" will be from the first tutor record related to tutoring). You cannot have data entry into this field from two different "angles" at once. If you place student::peopleID onto your student portal and manually try to enter into it you will get the same error.

As the record cannot create that way, there is no peopleID field to change in that row so $$var does not get assigned.

With the existing schema, you would need to create student record in "Assign $$var" script before assigning $$var. You could do that with a set(field) - any other field than people::ID. Just add "set(student::someotherfield):value script step before your set(student:peopleID) script step.

[addendum: wherever I wrote "student" please read "contact"]