Stock Inventory database

Inventory Table

Purchase Order Items Table

Purcahse Order Table

When I create a Purchase Order to add items to our inventory, I want the Unit Price to update our Inventory Unit price with a Lookup. The reason for the Lookup is so we can manually change the inventory price if we want to.

The Lookup by its nature takes the first occurrance, which is NOT what I want, since I want the latest price reflected.

So, in FileMaker Pro 8, I have a relationship based on Inventory ID and Date.

Inventory Table - Req Item Table

InventoryID = InventoryID

AND

Date Today = Purchase Date

is there something about the Dates that won't allow this to work?

Date Today is Get(CurrentDate)

Purchase Date is Modification Date

Inventory IDs match and Dates match when I look at the records, but the Lookup doesn't work.

Is there anyway to find out why?

If you sort the original relationship by date descending, then the Lookup (or an auto-enter calculation) should retrieve the most recent data's price.