What's the best/most efficient means of finding the next highest number (i.e., highest existing value + 1) in a DB for which not all records have a value in the given field (which I'll call #Fld)? i.e., this is not a serial number; Of thousands of records, only a few dozen will have a number assigned in #Fld. This number must be unique and should be assigned in sequence when it is needed. (There may be gaps in the existing numbers.)

I've figured out a way to do this, but it seems unnecessarily clumsy to me and I suspect that there is a much more efficient way to do this.

What I'm doing now is (all scripted) find all records containing a value in #Fld, sorting descending, go to first record, copy the value in N#Fld [a calc field which = #Fld + 1], return to original record which needs a number created for #Fld, then pasting the value into #Fld.

It works. Is there a better way?

Thanks,

RDecker

Try this and see if it works

1. Create a summary field called maxFld = maximum of FLD

2. Set current FLD = maxFld + 1

Summary fields still require a found set and a sort. What you want is a calculation:

Max(self:#Fld) + 1

...where "self" is based on either a "constant" field (i.e. a calculated number field that = 1), or in FileMaker 7, a Cartesian relationship (i.e. all records).

Uh oh.... I never know that summary field requires a found set! This is bad.

Thanks for the head up, Fitch Times to go back and change my project :(

Unfortunately, Max() is very slow to calculate. A better way is to set up a relationship to the id number. Anything can be on the left hand side and you use the cartesian relationship (x) match. Set the sort order of the right side of the relationship to be by the ID number, descending. So the first matching value of the relationship will be the greatest ID number. Add one to this and you are all set. Really speeds up finding the max value.

Or use two globals, and set up the second to be an auto-enter calc using (lookupNext, lower). Autoenter calcs are very powerful.

Set up a relation from the first global to the ID field, throw in an impossibly high number.

Set the second global to

(GetAsNumber(LookupNext ( Relation::IDField ; Lower) ) + 1

You get back your answer. Globals can be lookups now!

FYI, I did a test yesterday on the speed of Max(related::date) vs. sorted relation::date. I set the result to another field and looped through 10,000 records. The time for the sorted relation was 215 seconds. The time for Max: 214.

I would welcome more discussion of speed using Max or Min vs. sorted relationships, or Count vs. Status (CurrentFoundCount), as in this article -- or any other ideas to optimize the performance of large databases.

Did you try the lookup? It's going to be instant.

I didn't try the lookup, but I'll keep it in mind for future applications. In general, though, we tend to avoid lookups because our application is complex and difficult to maintain, and eliminating lookups wherever possible has helped us avoid hidden "gotchas" (e.g. scripting a new record and forgetting that the lookup is triggering). Also if you need to change the lookup, all users must be kicked off if it's a field level change (we're stuck with FileMaker 6 for another 8-12 months).

Did you perform the Max test directly after the relationship one? There may be caching issues involved if you did, which may be why Max appeared to be faster.

Tom - it does not appear that you understood the suggestion.

I suggested a GLOBAL lookup - no actual record data is changed. You do what you want with the field contents afterwards. The ability to use globals for lookups is something new to FM7.

In Sorting in valuelists all the words strating with '_' and ending with '_' are sorted out first based on the character after the '_' and sorting of all words with first character as an alphabet are sorted after that.

If words are :--

_Consult_,Consult 1,Consult 2, _Hospital_, Hospital 1, Hospital 2

I wish to do the sorting to bring the result in following manner:--

_Consult_

Consult 1

Consult 2

_Hospital_

Hospital 1

Hospital 2

Whereas What I am getting is:-

_Consult_

_Hospital_

Consult 1

Consult 2

Hospital 1

Hospital 2

Plzzzzzzzz help [color:"red"]

I know, it wasn't a very scientific test. One of these days when I have time...

I will be patient then.