Many to Many relationships require a Join Table (Google "join table site:forums.filemaker.com")
Many Students can have Many Classes - a join table "StudentClasses" is needed.
The first five links in this list may be useful.
Thank you very much for your answer!
May I ask you to draw the ERD for this solution?
To expand on DavidAnders suggestion:
You need to add a table of Products that is separate and distinct from Lines. Selecting a product for a given offer should create a new record in lines that links it to both Products and Offers.
It is sure that I am not really experienced in database design but I have to admit honestly that I still don't get it...
Here is simplified database to demostrate the problem: http://cl.ly/2o0W1l0q0z2d/download/Products.fmp12
I appreciate any suggestion!
There are a few minor things, which were stopping yours from working, and a kind of overall method which would make it work, but requires some scripting, and the using of a specific type of layout.
The main problem you were having is that Get(ScriptParameter) does not "keep" its value after a script is ended; it goes back to nothing. One way of getting past that is to "send" it to the next "sub script". But that is not what you are doing. You are going to a new window, then starting another script, from there.
My method is to create your new window. But it is a specific layout, only visible during this scripted process. It is the ONLY layout which has the button script to add a new product to a Offer (which you've just come from).
That layout has a script (button) which will add the product for a Offer. It will also test that you do NOT go to any other layout. It has an OnLayoutExit Script Trigger, which will Close the window and HALT all scripts if you try to leave the layout manually. That script requires that you tell it the name of the window. There is also a button to "Close Window", if a person just want to click it to leave.
I know, sounds a little complex, and it kind of is. But each piece is not big. Without them you will have trouble making something that both works with 2 windows, passing data from one to the other, and is safe. I not real found of the HALT step generally, as it will stop all running scripts. But in this case it seems OK. If anyone knows a better method, please do tell :-]
I put the file here, for a while.
Thank you Fenton!
It seems to work as I expected. What I should now figure out is how to display only the products that are alreday filtered by the request's criteria.
And yes it looks now more complex than I originally thought it would be. I will now "take it apart" and analyse it to understand it fully!
I think I have managed what I wanted in a very simple way. Could please someone experienced have a look at it if there are any "traps" behind this idea?
Some things are still not working, which I have commented on the layout and adding a new line item or an offer has no duplicate check currently. Suggestions to these are also welcome! :-)
It is 4 am local time, so I better go to sleep!
I took a look. The problem in the Offers layout is that the field (_kp_products_id) in the Lines portal is NOT a Lines field (it's still a Product's field). Just change the field to Lines and it will show correctly. When you see that all fields are repeating on each row of a portal, it usually means that you're using the wrong relationship for the lines.
The one thing I would change is the Products portal on the Requests layout. I would put it on the Offers layout. It will work the same, as each Offer can see its Request, hence can use that same portal, and in this case the same Products rows. The problem with it in Requests is that it cannot be sure what Offer you're on; you are creating a new Offer for any added Product. If you run from the Offer instead, you just use the Offer you're on; though obviously you'd need to create any needed Offer from the Request layout. Doesn't that make sense?
You are still using a "new window", without locking it; which works fine, if people behave themselves. But it would make me a little nervous, as someone not paying much attention could change windows, change Offer records, then click the button to add a new record to Lines, but using a previous Office ID (from the one they're now own). Yes, not likely, but possible. Hence all the extra steps in the scripts to lock it. But you can see how it goes.
In my version of the file I thought I'd try to do this without a new window, to avoid all that. I put the fields required to see a Product into the portal, including a smaller (1/2) size of the picture. You can just click a button there to add one. However it depends on not needed to show much more.