Lookup that selects the newest entry from list

[mattlight]

Hi,

I'm stumped in FM7 on one thing!

I have a database which has a "product listing". You can create a product in the database giving it a description and catagory. Auto serialised product code.

Then in a portal you can enter in a "purchase of new stock" related to the product code stating quantity purchased, buy price, retail price and where you bought it.

In the "purchase new stock" database I made a relationship based on the product code and purchase number to ensure I'm looking at the correct stock of any item. The relationship's other end was the sales database - I then created a sum(sales db::quantity) calculationship to keep track on how many are sold.

I then have a field which calculates that if the qty sold = qty bought gives the result OLD, else OK. This is so that I can do a relationship based upon PRODUCT CODE skipping any purchase order number that is out of date - hence, the right purchased version of the PRODUCT may be priced differently to the first batch I had bought. The result of this relationship is the freshest PURCHASE NUMBER for the product.

Then I use the PRODUCT CODE and PURCHASE NUMBER to lookup the most current and up to date price - this is the one that is altered in the Stock database when buying something.

The reason I'm going this route is to ensure I don't keep changing product codes for things I may buy and sell on a regular basis and it ensures I don't have a repetative listing of products doubled up a million times when once is enough.

Any ideas?

cheers

When I do a

[mattlight]

If I try to simplify this process that I'm trying to do, I'm asking this:

How can I get a relationship to lookup data from the latest matching record if there is more than one found record based upon a relationship?

Thanks

[DanBrill]

I'm stumped in FM7 on one thing!

One thing? *******! I wish I was you!

I'm not sure I understand exactly what you are doing, but as lookups will take the first related value they find, try sorting the relationship based on whatever indicates that the entry is newest. So if the newest entry will have the highest serial number, then sort the relationship by the serial number DESCENDING so that the greatest serial number is always the first related record. If the newest entry is the one with the most recent date, sort by the date, again descending, so that the newest one is first.

Hope this helps,

Dan

[mattlight]

that sounds like a plan! I'll give that a go - that makes sense!

Thanks - I will let you know what happened!