Using the "max" function to access a record ?

[Thomas An]

Hi all,

Quick question:

Suppose we use the Max() function to get the highest date stamp within a table.

Now ... once we have found the highest date how can I get the ID of the record that contains this date ? ... and then use this ID to access a sister field in that same record ?

(P.S. I do not wish to add a mirror table in the graph and do this via relationships)

[Tim W]

Hi Thomas,

Why not use the max function to set a variable(global in v 7), then use the variable as search criteria to find the record?

Like so:

set(globalfield; Max(Field))

enter find mode

Set(Field; globalfield)

perform find

HTH

Tim

[comment]

Why not use the Last() function to get directly to any field in the last record?

[Thomas An]

Hi Tim,

Thank you for your comment !

The reason I am not doing it that way is because it is needed as a calculation for the entire table.

-There is a main table called "Product Catalog"

-Product catalog is related to "Price archives" (Each record in "product catalog" has a history of all price changes done to that product) ... the most recent change is the "current" price.

-Now, there is a calculation field in "Product Catalog" that keeps the "latest price change date" for each record.

-So, we would like to have a second calculation field that fetches the "latest price", based on the "latest date" (the latest price field is later referenced by other tables)

(It is already done right now by creating a mirror "priceArchive" table and use a relationship to peg the latest record based on date and product_ID). The thing is, that being a minimalist, I would like to see if there are more elegant ways of achieving this; other than cluttering the graph with mirror tables

[Thomas An]

Why not use the Last() function to get directly to any field in the last record?

wow !!

I really need to look into this function (didn't know about it)

EDIT: Success ! All I had to do is use a sorted (by date) relationship for the "priceArchives" table and then use the last() function.

This is it. Thank you for teaching me something new.

Best,

Thomas