Getting wrong data values in my layout

[majestic12]

I'm quite puzzled. I made a very simple database that has the following:

A table for orders: - order ID (unique) + information about the order including the article IDs

A table for article: - article ID (unique) + information about the article

I linked the two tables together with a one to many relationship (because the same article can be ordered more than once) and wanted to make a layout where I could see the article information when cycling through order IDs. For example, I'd search for ID 120245 and it would show me how many of the article was ordered, the order form code, the article dimensions, colour, etc. Except that ... it doesn't do this. For every value I link to that is not in the orders table, its value gets "stuck". I get 20 entries with all the same description, same dimensions & colour even though this is clearly wrong and then seemingly at random it suddenly changes to another value and gets stuck again.

I don't get why FM would do this - the relationship is obviously straightforward: since article ID is linked and a one to many, the article ID in the order table can only correspond with one single record in the article ID table yet it takes information from the wrong records! Can anyone help me out?

[Lee Smith]

Why don't you attach your file?

[LaRetta]

Hi Majestic12, welcome to FM Forums!!

You describe:

Orders >- Articles

... but it is not a one-to-many (1:n) ... it is a many-to-many, i.e. one Article can belong on many orders and one order can have many Articles. You are missing a join table.

Orders -< LineItems >- Articles

See this demo for great example in post #8 http://fmforums.com/forum/topic/63425-auto-fill-one-field-with-text-from-two-fields/page__p__300150#entry300150