I am trying to make a relational database. I basically have the

Following, one table for reseller info, one for sales records and another one

for stock levels.

The whole thing is working quite well, I can see how much of a

particular product each reseller has because I have a table with a key

of resellerID_productID' to which I can have a stock value and know

when I need to restock. I have over 200 products and over 100

resellers so my stock by reseller table is quite long.

I need some help with a script to make it that when I add a new

reseller, my 'stock by reseller' table writes new lines of

'newResellerID_productID' for however many products I have.

Similarly when I add new products I need a script to add

'resellerID_newProductID' for however many resellers I have.

i.e.

KEY ---------------------------STOCK LEVEL

resellerA_productA --------------- 5

resellerA_productB --------------- 5

resellerA_productC --------------- 5

resellerB_productA --------------- 5

resellerB_productB --------------- 5

resellerB_productC --------------- 5

resellerC_productA --------------- 5

resellerC_productB --------------- 5

resellerC_productC --------------- 5

button to add new product should change this table to...

KEY ---------------------------STOCK LEVEL

resellerA_productA--------------- 5

resellerA_productB--------------- 5

resellerA_productC--------------- 5

resellerA_productD--------------- 0

resellerB_productA--------------- 5

resellerB_productB--------------- 5

resellerB_productC--------------- 5

resellerB_productD--------------- 0

resellerC_productA--------------- 5

resellerC_productB--------------- 5

resellerC_productC--------------- 5

resellerC_productD--------------- 0

Is there an easy way to do this? I would be grateful if anyone could

help with this as I can't seem to find the answer.

Andy

Set the new reseller id to a global field in the Product file using any relationship from the Reseller file. Create a calculation field in the Product table equal to this global & "_" & id. Create a relationship from this calculation field to the key field in the Stock_Reseller join file (I'll call it S_R|c.t.reseller_id|||t.key). Then add a script in Reseller to Perform Script [sub-scripts, External, Product.FP5], the external script consisting of the following:

Show All Records

Go to Record/Request/Page [First]

Loop

[color:"white"]__Set Field [s_R|c.t.reseller_id|||t.key::somefield, "somedata"]

[color:"white"]__Go to Record/Request/Page [Exit after Last, Next]

End Loop

When you set a related field in a relationship that doesn't exist, FileMaker forces it to be valid. So your key field is automatically set to complete the relationship. I'm not sure how you're setting stock level, whether it's a calculation or number field. But this should give you an idea of how to proceed.

You can do the same for adding new products by running the loop in the Reseller file with the relationship from it to the Stock_Reseller file.

Make sure the relationships are set to 'Allow creation of related records'.