I have 7k records in a table, each with a unique serial number ID. The field is a number field. (The serials auto enter in increment of 1). I want to get the highest serial value in the current set (or even all records in the table)

It seemed so logical to use MAX(_ID) to get this, but it only gives me the value of the current record.

Does MAX only work from the perspective of a related TO calculating the MAX value in the "child" TO?

Isn't there a function that gives you the max value in the current table in the current found set?

Hi there,

Max() is an aggregate function, like Sum() and Count(). These functions work on either the related values, repeating values in the current record, or a semicolen (comma in FM6 and below) separated list of values for the current record.

If you wish to get aggregates of the found set, summary fields should be used.

However, in this case, a summary Maximum is not very efficient. It's faster to use a sorted relationship with a calc using the last() function to extract the last value used. For the last value used in the entire table, the relationship is just a cartesian (X) join between any two fields. Otherwise, try to filter the relationship by the same criteria that determined the found set.

Edited November 4, 200619 yr by Guest

Thanks. I actually simplified my situation for sake of shortening the question. In reality I have 28 tables I want to export, then import again (into updated "schema") and then increment the serial numbers on the import side. So I'm doing it all via Script.

Somehow I had it in my head that I could go something like this:

Go to Layout ["Incidents" (INCIDENT)]

Show All Records

Set Next Serial Value [iNCIDENT::_ID;MAX(INCIDENT::_ID+1)]

But of course, that only sets the Next Serial to the same _ID value of whatever record I happen to be on at that moment.

So I have to do a sort, then a goto last record then set next serial to _ID+1.

That works, but it's fairly tedious scripting.

On the other hand, since I only run it when I need to, maybe it's better than setting up a bunch of relationships and doing it that way (with LAST or whatever).

Would LAST work w/out sorting and going to the last record? I assume it goes w/the current found set in the current sort order... and it sounds like a relationship is required, like MAX

Edited November 5, 200619 yr by Guest