Hello,

I am just a little confused about the logic of the following and how the tables should be related for this to work.

I have 3 tables (Purchase, Purchase Lines, and Delivery). The logic is the the following: "each Purchase can have many Purchase Lines and each Purchase Line can have many Deliveries".

This is all being done on one layout of the (Purchase) table, which contains a portal that is displaying the data from the related tables. I have allowed the user to be able to add or delete data on the portal onto the other tables through the relationship.

Issue: When trying to enter a two different delivery dates and quantities, both date and quantity fields show the same data. I have tried doing different table instances of the delivery table but unfortunately this did not work.

I have added a sample and a diagram of the ERD.

Any Ideas?

Thanks

Alberto

Purchase.zip

You need a way to distinguish the relationships Delivery, Delivery1 and Delivery2 because they are all in the same table.

To do this add two calculation fields or global numeric fields on the Purchase_Lines table and set them to '1' and '2', respectively. You can call them N1, and N2. Then change the relationship for Delivery 1 to be PurchaseLineID = FK_PurchaseLineID AND N1 = PK_DeliveryNo. Do the same thing for the relationship with Delivery2.

Incidentally, it looks like you want to apply the same DeliveryNo to each line item depending on which 'DeliveryNo' heading it falls under. You should move the DeliverNo to the Purchases table. (Otherwise you'll need to enter it for each line item). You can pull it into the Delivery record with a calculation as shown in this attachment.

Purchase.fp7.zip

Edited November 30, 201015 yr by Guest

Having suggested using a calculation for the DeliveryNo, I should state that this has a downside. If you want to enter data into this field manually, say on another layout, you will be locked out because it is a calculated field. This might be a place where you would want to use a lookup, a script, or auto-entry data in the field definition.

It has other downsides, too - mainly that you need a calculation field and a relationship for each delivery. So instead of one-to-many, you have one-to-[as many as you have provided for].

I would suggest selecting a specific PurchaseLine in order to view/modify its related deliveries.

Here's an example of how it could it work:

http://fmforums.com/forum/showpost.php?post/334277/

Great description, very constructive. Thank you both for the comments I have this clear now and have got it working.

Regards

Alberto