Table A : list of shops
Table B : list of all available products (and details) to sell in a shop
Table C : list the products sold in each specific shop (visible via a portal in table A)
A <- C <- B
In my case, I made a script that add all the products available when I create a new shop.
Why do this step? I think this is were your basic design should be changed.
Let's first check to see if I understand:
Table B functions like a catalog or master list While table C is being used to document information specific to a shop listed in Table A. Correct?
I don't see the purpose in physically copying all the data from B to C.
Could the following design change be made to work for you?
Define a value list that lists all products from table C. Whenever you choose to add a product from Table B to Table C, you do so by selecting an item from this value list. There are several ways to set up a script for this so I'll limit myself to a general description. Because prices, descriptions are subject to change, your script copies data from B to C to create a new record for the Shop specified in your current record for Table A.
If that works for you, simply adding/removing/editing the contents of Table B will update the information available for all the shops automatically.
First, this notation is incorrect:
A <- C <- B
Unless I misunderstood what you're saying, it should be:
A -< C >- B
or, in a more comprehensible way:
Shops -< ShopProducts >- Products
Now, a relatively simple way to add a new product to all shops is:
1. Place the ProductID in a global field or variable;
2. Show all records in the Shops table;
3. Import the records from Shops into ShopProducts (mapping ShopID to ShopID);
4. In the imported set, replace the contents of ProductID with the value stored in step #1.
Many thanks to you comment
it works fine
Seems so easy afterwards
Thank you all for your help