Jump to content

Using the "max" function to access a record ?


Thomas An
 Share

This topic is 5762 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5762 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.