Max value in field

[Dennis Mathias]

Somewhere I must have missed how to do this:

I have a field with a thousand records in it and I want to increment the maximum value of that field by one. I'm in effect building an calculated field that will increment a fields value.

Did some searches and I didn't turn up anything--but that's probably because it is so simple!--I would think.

Been away from this for a while so maybe that's my problem.

[Edward Larrabee]

The easiest way to do this is to define the field as auto-increment in Field Rules. See Field Rules: Field Types.

[Dennis Mathias]

But I wanna...

That's true but I want the user to be able to enter a different number if necessary but the field defaults to whatever the biggest number is plus one. That is an increment but an overridable increment--incremented default if you will.

[Stan Mathews]

That's true but I want the user to be able to enter a different number if necessary but the field defaults to whatever the biggest number is plus one. That is an increment but an overridable increment--incremented default if you will.

You want a deata entry field rule, default expression, simple default expression, something like

tablemax("yourtablename",".t.","yourfieldnamer")+1

[Dennis Mathias]

Hmm. Well, I expect that might work. I do have a numeric type for the field so I would have to convert to Char then convert back. I was hoping there might be a numeric function such as biggest(field_name) which is a number and would return a number so I could add a one to it.

[Stan Mathews]

Hmm. Well, I expect that might work. I do have a numeric type for the field so I would have to convert to Char then convert back. I was hoping there might be a numeric function such as biggest(field_name) which is a number and would return a number so I could add a one to it.

What part of what I posted do you not understand?

I do have a numeric type for the field so I would have to convert to Char then convert back.

no conversion necessary.

I was hoping there might be a numeric function such as biggest(field_name) which is a number and would return a number so I could add a one to it.

- there is such a function and it is called tablemax() but you have to tell it what table to look in and it returns the same data type as the fieldname supplied and I indicated that the expression you wanted was an appropriate tablemax() + 1.

Here are some samples using the AlphaSports sample database from the interactive editor.

 tablemax("invoice_header",".t.","customer_id")
= "00000056"

? tablemax("invoice_header",".t.","date")
= {05/17/2006}

? tablemax("invoice_header",".t.","discount")
= 5.000000

? tablemax("invoice_header",".t.","discount") + 1
= 6.000000

[Dennis Mathias]

Holy Crap!

Holy Crap! This works! In the function, I assumed the C meant Character--well it does WANT a character although the field is numeric! It must convert behind the scenes and that certainly wasn't what I expected. I mean, even the logical operator is entered as a string. Gez, I'm buffaloed!

[Dennis Mathias]

Bowling for dollars

Actually I've been Bowling Greened--not buffaloed. And if I did'nt thank you..THANKYOU!

[Stan Mathews]

Holy Crap! This works! In the function, I assumed the C meant Character--well it does WANT a character although the field is numeric! It must convert behind the scenes and that certainly wasn't what I expected. I mean, even the logical operator is entered as a string. Gez, I'm buffaloed!

What goes in is not necessarily what comes out. In reading the syntax of the function

Largest_Of_Values as A = TABLEMAX( Lookup_Table as C, Filter as C, Lookup_Expression as C )


Largest_Of_Values as A (this is what the function returns) <- as A means as any type. If the function returns the largest of a numeric field it will be numeric, if a date field it will be date type.

Lookup_Table as C <- as C means the table name must be specified as a character string

Filter as C <- as C means the filter must be specified as a character string

Lookup_Expression as C <- as C means the expression to be evaluated in the context of finding the maximum must be specified as a character string.

The format of the parameters required has nothing to do with what the function yields.