1 of 1 people found this helpful
I'll try to take a stab at this... I believe I recognize this challenge from a couple of solutions where I've had to create a sort of "questionnaire" setup. (To try and make this understandable, I'm using your table names Plan and Method, although where I've set it up I have different tables like Questionnaire and Questions.)
First, as you've noted, you'll need a separate table for Answers, which has a foreign key for Plan and a foreign key for Method.
Problem is, your Method records are independent of any particular Plan, while the Answers need to be just for one particular Plan. So a portal of Plan_Methods can't be (easily) linked to Plan_Methods_Answers, since you don't have a Method::fk.Plan to link to Answers fk_Plan.
If this doesn't make sense, I'll save you some time and suggest I might be off-base already.
One method I've used is to set a global $$Plan on recordload in Plan, to the primary key of the current Plan, and calculate a "fk.Plan_current" (unstored) in Method, thus providing the missing foreign key. Then I can see all current Methods in the Plan_Method portal, and relate-create answers in Plan_Method_Answer. This has worked, but in the end it was... fiddly. It was heavily dependent on the calculated current plan key getting set, and evaluating, and ended up involving refreshes periodically...
Another reason for switching is that when you look at an Answer, you want to know the details of the Method when the answer was entered, not what the Method says now (which could be different if you've updated the Methods).
To address the "fiddliness" and the requirement to show the Method as it was at the time, I've switched to scripting the addition of Answers when a Method is added, and/or adding answer records for any "missing" methods onRecordLoad. The Answers table does a lookup for Method details, so they can be "frozen" and won't change if a Method later gets added. The answers don't actually have an "answer" yet, but a record exists for any answer we're going to need, and those answers have a Plan key, so it's a simple relationship.
In order to script the addition of Answers to a Plan, you can either use an off-screen portal with a separate relationship that allows creation, or (this is my preferred way) you can export the Methods to a tab-separated file in the temp folder, import those into the Answers table, then replace the Plan key.
Thanks for your fast answer.. i'll try your suggestion, i'm not sure i can do that at first but i'll try.. this is the most complicated filemaker solution for me..
Another possible way to approach this (based on one big assumption that each test gives one result or answer) is to simply hold the Test and result in the same record...or in a one to one join.
Plan--< Method -- Result
If there is only one result per test, your Method table could be
Table Method contain:
This approach would still let you create a value list of Methods so it is easy to populate tests on a new plan.
And it would let you show the result on your main Plan layout in the same portal.
And even if a method gives more than one result, the grandchild table of Result can simply be a one to many join by Id_Method, and show via portal on a Method layout.