A layout that specifies Table3 in Layout Setup | Show Records from can include fields from Tables 2 and 1. But if the current record in table 3 does not link to a record in table2 and "allow creation of records via this relationship" is not enabled for Table2, you will not be able to edit fields from Table 2 on this layout until a matching record is created in Table2.
Can you help me some more? Table 1 is Patients, Table 2 is Medications, and Table 3 is Diary Entries. A patient can have multiple medications, and patients can have multiple dairy entries, which is why I was trying to connect it like:
(see image below)
Is there a way to connect Tables 2 and 3 without disrupting the original relationship set up? Because Medications do not have Diary Entries?
Sorry, I misread your original description of the tables and relationships.
I was trying to reproduce what you described, not suggest a change in the relationships.
Back to the original question:
Is it possible to create a portal from a layout using Table 3 that accesses fields in Table 2?
Yes, you can place a portal to table 2 on your Table 3 layout. But you will need to make sure that a record in table 1 exists that is linked to the current Table 3 record shown on your layout. And to add new Table 2 records in this portal will require enabling "allow creation of records" for Table 2 in the Table 1 to Table 2 relationship.
That's the part that's weird though, right now Table 1 has the primary key, and both Tables 2 and 3 link to this primary key using a foreign key, I also double checked the Relationship between Tables 1 and 2, and the "allow creation of records" for Table 2 is checked. But I'm still getting that error which was in the screenshot (this operation cannot be performed because one or more required related fields...)?
And what operation exactly is that?
What did you try to do just before you got that error message?
Please note that this requirement is critical:
You will need to make sure that a record in table 1 exists that is linked to the current Table 3 record shown on your layout.
I suggest adding fields from Table 1 to your layout as well--including the primary key field. If that primary key field is blank, your current Table 3 record is not linked to any Table 1 record and you'll have problems.
Thank you so much, Phil! That is the problem, when I included a field from Table 1 to my layout from Table 3, nothing came up. But I don't understand why, I double checked the "Manage->Relationships" tab. Table 1 linked to Table 3 (the primary key in Table 1 is the foreign key in Table 3), I also tried both checking and un-checking the "Allow creation of records in this table via this relationship" for Table 3, but still it's just not linking...??
"allow creation..." ALLOWS creation. It doesn't cause it to take place automatically. You still have to create that record in table 1 and link it to your record in Table 3.
Normally, one creates the Table 1 record, the parent record before creating a record in either of the two child tables.
When you say you "still have to create that record in table 1 and link it to your record in Table 3", are you saying you have to physically type in the foreign key that links Table 1 to Table 3? What if the user doesn't know their foreign key?
No, but you have to take some additional action in order to get that record created.
The typical method most often used is to create the table 1 record and then fill in data into some field of a portal to Table 3. If "allow creation..." is enabled for Table 3 in the underlying relationsship. That option causes filemaker to update the match field in Table 3 to match to the current record on the Table 1 layout (and puts a blank "add row" in the portal where you can do this data entry.)
But you have this reversed where you are creating a record on a Table 3 based layout and (I am guessing here) want to create a new record in Table 1 that is linked to it. It is much less common an approach, but if you enable "allow creation..." for Table 1 in this relationship and add any editable field from Table 1 to your Table 3 layout, you can enter data into that field and Filemaker will both create the new record in Table 1 and copy it's Match field value into the corresponding match field of the current Table 3 record.
Scripts can also automate the needed process so that all you need to do is click a button.
And different methods, both scripted and/or manual would be used if you want to connect your current Table 3 record to a Table 1 record that already exists.
Thank you so much, Phil. Actually, it would be more like the first scenario, the record in Table 1 would already be created, but I would need multiple records in Table 3 that would need to be generated that link to Table 1. (don't need to create a new record in Table 1 from a layout in Table 3).
How can this be done in a script? Can you give me an example?
If you used the portal on a table 1 layout, you won't need a script.
And if you formatted the ForeignKey field in Diary with a drop down list of ID's an Patient names from Table 1, you can create a new record and select the patient from that drop down list--again with no script.
But you might also use this approach:
Set up the OnRecordLoad trigger on your Table 1 layout to perform this script:
Set variable [$$PatientID ; value: Patients::PatientID ]
Then add this auto-enter calculation on the PatientID match field in Table 3:
As long as you start from your Table 1 layout, where the script will then initialize this global variable with the needed ID value, you can create a new record on your Table 3 layout and the auto-enter calculation will copy this value from the variable into the match field that is needed here in order to link it to the current record on your Patients layout.
Thank you so much again, Phil, you are so helpful, I'm really grateful!
The thing is, we have two different groups of people, one group is only accessing Layout 1 (the layout based on Table 1), the other is accessing Layout 3 (but not allowed to access Layout 1), which is why putting a portal from Layout 1 to fields from Table 3 wouldn't work because the groups of people typing in info from layout 1 don't know any information about the fields in table 3.
The only thing that worries me about the script approach is you mentioned you have to start from the Table 1 layout, but the problem is, the folks that access Layout 3 do not have access to Layout 1. Is that a problem?
I'm afraid it still didn't work!? I was able to add the global variable primary key as the foreign key to both Tables 2 and 3. I then created the layout based on Table 3 which had a portal linking to Table 2. However, whenever I tried to add a new row line to this portal, I would still get the error message "This operation cannot be performed because one or more required related records are not available and cannot be created."
I did double check the relationships, and "allow creation of records" for Table 2 in the Table 1 to Table 2 relationship is indeed checked.
Any other possibilities?
You aren't limited to a single layout for any one table. You can create as many layouts as you need all based on the same table.
You can create a layout for Table 1 where there are no fields from table 1 visible, just the portal to Table 3.
One way or another you have to be able to select a Patient record for a given diary record. Otherwise, there is no way to get this to work.