DBMAX function question

[Anis]

I'm trying to use dbmax and use a field name in the keyvalue, it keeps telling me unrecognized field name, how can I include field name as a key value in the function.

table = clcf1_history
index = Account
keyvalue = the filed named Aaccnt_
field to use for maximum = Initalmrg


?dbmax("clcf1_history.dbf","Account",clcf1_hostory->Aaccnt_,"Initalmrg")
ERROR: Field is not recognized: 'clcf1_history->Aaccnt_'
I'm trying to find the maximum value for every Account in the database ?
is there a better way ?

Thanks

[Stan Mathews]

Re: DBMAX function question

Where are you trying this? In the interactive editor, clcf1_history->Aaccnt_ has no meaning as the table is not open.

Since you specify the table as the first parameter of dbmax() the

clcf1_history-> is not necessary, but does no harm.

If you create a calculated field for the table clcf1_history and make its expression

dbmax("clcf1_history.dbf","Account",Aaccnt_,"Initalmrg")

and reevaluate the field rules you should get the desired value. (Given an index "Account" on the AAccnt_ field.

You can do something like this....

tbl = table.open("frito")
? tbl.type
= "CHEETOS      "

? dbmax("frito","tst","cheetos","sls")
= 520.000000

? dbmax("frito","tst",tbl.type,"sls")
= 520.000000

tbl.close()

[Anis]

Re: DBMAX function question

Thanks Stan; I'm trying this on a report in the calculated field area, but I was working with the interactive window just to test.

In the calculated area of the report it didn't take my expression, it deletes it everytime when I use the keyvalue as Aaccnt.


dbmax("clcf1_history.dbf","Account",Aaccnt_,"Initalmrg")

[Stan Mathews]

Re: DBMAX function question

Can you zip/attach a small sample of the table upon which the report is based? Zip from the control panel so the support files are included.

Works fine here.

[CALocklin]

Re: DBMAX function question

I'm trying to find the maximum value for every Account in the database ?
is there a better way ?

Based on this, I think you are trying to tell it to check every record in the clcf1_history database where the Aaccnt_ field matches a value in the Account index (this would be all records, of course) and find the highest Initalmrg value. The function doesn't work that way.

The syntax is:
DBMAX( Lookup_Table as C, Index_Tagname as C, Key_Value as A, Lookup_Expression as C )

Where Key_value is a single value that would match a value in the index - not a field name in the lookup table itself. The Key_value is often the value from a table but not from the table that the lookup is in.

I think the only way you can use DBMAX() to do this is to create a 'dummy' field that is always filled with one value then build an index on that field. HOWEVER, it's probably much easier with...

TABLEMAX( Lookup_Table as C, Filter as C, Lookup_Expression as C )

Probably:
Tablemax( "clcf1_history", ".T.", "Initalmrg" )

[Anis]

Re: DBMAX function question

Here's the database zipped.

Thanks Cal for jumping in.

In the A5 documentation it says the keyvalue could be a field name for DBMAX!!

To create a dummy field, the expression you had will pick the largest amount in the table not for that specific account.
If the database has many accounts and many dates, I want to pick up the largest "Initalmrg" for each specific account historically.

[Stan Mathews]

Re: DBMAX function question

Works fine here.

[CALocklin]

Re: DBMAX function question

OK. I misread your intent with "I'm trying to find the maximum value for every Account in the database". I read that as the max of all records.

Re: In the A5 documentation it says the keyvalue could be a field name for DBMAX!!

What it actually says is, "An explicit value or the name of a field in the table containing a value."

And, yes, that's poorly worded. I think it would be better as, "An explicit value or the name of a field in an external table containing a value." The function only returns one value so which one should it return if you give it the name of a field in the lookup table itself? It can't automatically get a list of all values for that field and return a list of all maximums from another field. A function could be written to do that but this one doesn't.

I don't have time to go into your sample in depth right now - maybe later if Stan doesn't get it first.

EDIT: After looking at Stan's screenshots, maybe I'm wrong. As long as the layout is using the same table, I can see where it might work. I've just never thought of using it that way.

LATER: Yep - learned a new technique today. It works. I tested it in a form and it works fine there - as it should once I thought about it. The trick is the "scope" of "a field in the table" as used in the Help file. As long as the field has a value - such as when used in a layout that is based on that table - it will work. However, using that same expression in the Interactive Window won't work unless you first open the table then run the dbmax() function. In other words, the dbmax() function can't "see itself" but if you think of the dbmax() function as opening the table in another session, you can use a field from the same table in a different session to specify the index value to be used - i.e., when working in a layout based on the same table, you can give it the name of a field in the layout even though it's from the same table used in the lookup. You are really passing a value to the function - not a field name. The value simply happens to be represented by a field name in this case.

[Anis]

Re: DBMAX function question

Thanks Stan/Cal

Stan; I'm completely astonished, becuase I reviewd that entry many times and it was correct, yet it was rejected.

I'm trying again now

[Anis]

Re: DBMAX function question

Thanks Stan, that just worked fine. I'm still puzzled.

I appreciate your time guys.

[Stan Mathews]

Re: DBMAX function question

the dbmax() function can't "see itself" but if you think of the dbmax() function as opening the table in another session

Cal,

Just tried something this morning. Not only can the dbmax() function not see itself. The dbmax() function doesn't see an exclusive use flag.

tbl = table.open("frito",FILE_RW_EXCLUSIVE)

? dbmin("frito","tst","cheetos","sls")
= 0.000000

? dbsum("frito","tst",tbl.type,"sls")
= 1134.000000

tbl.close()

[G Gabriel]

Re: DBMAX function question

It sees.
Any table function is NOT restricted by opening the table in any manner else the world of xbasic will come to a halt.
Try dbmax(), not dbmin(). Zero is probably the correct answer for dbmin().