Johannes, if I understand your issue correctly, you need a Join Table between the Production table and the Product table (as it is a many to many relationship between Products and Production ...ie. a production record can have many products and a product can be on many production records).
Create new ProductionProduct table that has foreign key from both tables it will join. the Amount field is in this table.
modified your Production sample file as an example.
I am thinking that I need three tables, one for the production plans, one for the products and one for the amounts.
That is correct.
You need a join table that combines a product and a plan, and lets you set an amount for that combination. Usually, this structure is illustrated like so:
Plan --< ProductInPlan >-- Plan
Every record in that join table states: this product (as referenced by a foreign key) has this amount (a field to fill in) in this plan (as referenced by another foreign key).
Compare the constellation above with your file: you've had the right idea (add a dedicated table), but the implementation is wrong.
In case you're interested, here's a modified version of another sample file. It has not only the correct structure, but also a user interface to conveniently add products to plans (or remove them again).
Thanks so much. You guys are really awesome. I am aware of the join table approach, but what I don't like about it here is that it forces me to select each Product and add it manually. I was looking for a way to see all my products and then just add a number to the ones I want to have in this week's plan. This way we can also display and sort according to our current amount in storage and other criteria before we decide on the amounts. Would there be a way to do that?
Johannes, I guess you could script it so that when you create a new Production Plan it automatically loops through the product table and creates all new Join Table records for all products ...with empty amounts. (how many products are we talking about?) You could then enter the amounts later. When you are done entering amounts, probably would be a good idea to loop through the related records in the join table deleting any that have the amount field empty. Maybe have a status field in the ProductionPlan table, where changing the status of the weeks production plan form open to closed runs a script to clean up the join table.
Thanks, dsvail. I understand your approach. We have around 250 products and would mostly like to use this over internet connection to our server which doesn't sit behind the fastest connection. I would be a little worried about speed since we already see some performance dips with scripts that seem less intensive.
Is there really no simple and fast way to do this? After my first approach (the dummy) I was sure I am just missing a single step to get this to work.
could use Perform Script on Server
Thanks again. I am really bad with loop scripts. Would this require a Magic Key to create the related records? I have never really figured out how to loop through related records and store their information on another table without using Go to layout back and forth, which would gather all instead of only the related records and would probably result in a blink-fest.
My apologies for simple questions. I am learning by doing. As a food chemist, this is quite a stretch from my regular problems, but we don't have anybody else to do this.
Why do you need a join table? Do you not need just two tables, Products and Production Plan? Search the Products list for all the products you want in this week's Plan. When you've isolated the right list, click your 'Create Production Plan' button, ask the user for the Production Plan Week Number (or date, or whatever you use to identify 'A Production Plan'), and import those Products into your Plan, attaching the Week Number ('Date', whatever) to each imported record.
Your script then flicks over to the Production Plan; you don't even have to do a 'Find' for those records - Filemaker helpfully isolates the imported records automatically for you. You can then enter the amounts manually.
You will always be able to isolate any particular Plan from that table. There is no big network load with that - each is only an import of 250 records (max).
<<I am really bad with loop scripts.>>
So don't loop; when it comes to record creation, importing is faster anyway:
• create a new Plan record
• store its ID
• go to a Products TO
• Show All
• go to the JoinTable
• from the Products TO of step 3, import Products::ID into JoinTable::ID_Product (primary into foreign)
• use “Replace Field Contents“ to set the foreign Plan key for the import set
The last step shouldn't cause locking issues because no-one except you knows these records exist yet … ;-)
Alternatively, you could:
• store the new Plan ID in a $$variable
• define an auto-enter calc for JoinTable:ID-Plan as $$ID_Plan // the $$var from step #1
Thanks! How do I import records via script from another table of the same database? The Import Script only allows me to import from another file or folder.
Ah, sheesh, I can just select the server as an import source. Got it!
Johannes, here is your sample file modified. Think this is what you were thinking....something to play around with at least. Has Portal showing all products with field for the amount that you can keep open until you want to actually push those products and their amounts to the ProductionPlan for the week. Uses looping script to go through the products that actually have amounts and creates new record in the join table (setting the planID, productID, and the amount). It then clears the amounts in the Product table ...sets all amounts for each product to 0. ...ready for your next plan.
The help in this forum is really incredible. Thanks so much, again!