Can someone point me to a tutorial in how to use self-joined tables?
I want to be able to display field values from a separate record in a layout portal.
This is it in a nutshell: FileMaker Pro 16 Help
However, I think you probably already saw this and it hasn't fully sunk in.
There are many useful ways to use self-joins, so you might want to provide more detail after checking the resource above if you are still not able to accomplish your particular goal. For instance, in an invoice table you may have a self-join that works on the invoice date so you can summarize revenue on a daily basis. In a staff table, you may use a self-join to form groups based on department. In an inventory table you may have a self-join based on Vendor_ID so you can form a group of all the stuff you get from your individual vendors.
You're right about it not sinking in. Let me explain in more detail.
I have created a solution for a plant genetics company to track their genotypes and cross varieties through field trials, lab, and kitchen trials. For their field trials, they have asked for changes to allow them to enter individual samples from up to 20 samples of each variety grown that year. The layout is shown below and is based on a table named "Genotype".
The grid for "Set" and "Yield" you see is for the sampling data, and the fields there belong to a table named "Gen_Tuber_characteristics".
At top right, there is a check box field titled "Check?" This is to indicate that the particular variety is flagged for use as comparative data.
The popup at lower left (inside the portal) uses a calculated value list to allow selection of varieties that have the "Check?" field value as "Yes".
What I want to do is display the field contents for that variety in the fields coloured green you see in the portal. Thos values will also be in the "Gen_Tuber_characteristics" table.
I can move all those fields into the "Genotype" table if that makes my job simpler. None of these fields in "Gen_Tuber_characteristics" contain live data.
Hopefully, that explains my goal fairly clearly.
Thanks for taking the time to respond!
My Interpretation: "Genotype" and "Gen_Tuber_characteristics" are two different tables. The relationship between the Genotype records and the Gen_Tuber_characteristics (labeled as Set / Yield in your diagram) records is from the Genotype primary key to the Set/Yield foreign key [simplest case, see below].
If the above is true, then you don't actually want a self join, you just want a different relationship between the Genotype and Gen_Tuber_characteristics tables to use as the bottom portal.
Assumption: The Gen_Tuber_characteristics table has a field that stores one of the values in the calculated list attached to Check_Value_Name. If Gen_Tuber_characteristics doesn't have such a field, add it - it needs to be a stored field (check back if you don't know how to add it). This will be the Gen_Tuber_characteristics match field as discussed below.
Implementation: In the Genotype table, place the "Check_Value_Name" field as a global field. In the relationship graph, form a relationship between the Genotype Table Occurrence (TO - the same TO as the layout above is based on) and a TO instance of the Gen_Tuber_characteristics table. To form the relationship, first make the relationship identical to the Set/Yield portal relationship, then add to it the global Check_Value_Name field in the Genotype table to match the name field as discussed above in the Gen_Tuber_characteristics table. Modify your layout to use the new Gen_Tuber_characteristics relationship in your bottom portal. [Now, the global field essentially becomes a "pointer" that selects for the Gen_Tuber_characteristics record based on the current chosen list item in the global field.]
That is very helpful! I will work through it and report back.
I'm missing something... What did you mean by "Modify your layout to use the new Gen_Tuber_characteristics relationship in your bottom portal"?
After adding the new TO I described for the Gen_Tuber_characterisrics table, you will have named it uniquely. In layout mode, double click the bottom portal and assign its TO source to be that of your new TO. Double click the fields in the portal and re-assign them to source from the same TO. Your modified layout portal object should now behave according to the choice in the selector field to display the filtered record you want.
One more thing... You mention "stored field"... but that selection doesn't exist in 16 like it used to, as far as I can tell. Is simply turning on indexing enough or is there another setting I am missing somewhere?
In addition, here are a couple of screen shots showing the relationship as it's set between the two TOs and the calculation used to generate the value list.
What I basically meant was a field that would work on the "foreign" side of a relationship (usually the right TO if your graph is structured left-to-right (a good convention).
A text field that auto-calcs (or is set by a script) to one of the values in the list that is attached to your "Check_Value_Name" selector field would work nicely. It is not necessary to modify a text field's default Storage settings for it to work in a relationship.
I'm going to try and work through this again. What I have now is taking the values from the record the grid is displaying rather than from the desired record, so I'm missing a connection somehow.
I suspect I'm dealing with a PEBKAC (Problem Exists Between Keyboard and Chair).
Retrieving data ...