I do not understand what you what to achieve.
I had a look at your file and I do not understand why the ClientID and to be related to the ProductID
Normally, a client has more products so the relation is ClientID with fkClientID in the product table (foreign key). So, in the product table you store in all records that are related to that particular client the value of the ClientID.
The ProductID identifies each products. To classify the products you need another field i.e. fkProductDescriptionLookupId in which store the value of the product category (1 for banana, 2 for grapes, etc.).
look at the attached file
Example.zip 177.5 K
Thank you very much for looking into to this, my problem is that when i change the productfield in your example to a value of 2 i should say "Grapes" as value for Productdescription, but it doesn't do this when i change the value. So the Productfield and the ProductDescriptionfield should match each time a change value in productfield.
I hope you understand what i mean, as for the relations thanks !
Maybe I am misunderstanding here, but why would the Product ID and the Product Description be in two tables? Can a product have multiple Descriptions?
Hi John Austin,
No sorry your right product en productdescription can be in the same table!
Ok I see now. What I would do concerning your relationships right now is set up a Join table in between Client and your Product table with Description.
The Join table would act as a new entity, possibly called ClientProduct. This represents a product that belongs to a client. You require this as a single client can have multiple products and a single product can be held by multiple clients.
Relate the ClientID of the Client table as a foreign key in the ClientProduct table. Relate the ProductID of the Product table as a foreign key in the ClientProduct table.
This will give you a table of clients and their products.
-Added modified version of your file here for reference
Example.fmp12 2.zip 47.7 K
in my wxample you have to go to ProducTable layout and change the fkProductDescriptionLookupId field to the value you wnat (i.e. 2) and the description change
Hi Gianandrea and John,
thank you for the help !!
It makes much more sense i'm such a newbie hahaha