"Get" Greatest Value?

[Wickerman]

Hi -- this is a simple one, but I'm still a scripting tyro. I want to have a button on a layout that will enter the next available value in a field called 'Barcode'. This value would be the highest existing Barcode value + 1. Although the Barcode is a string of numerals, I have it as a text field since it's not used mathematically (right?).

Is there a Function that "Gets" the highest value for a given field across all records? Or would my scripting approach here be to do a "Find All" / "Sort" / "Go to Last Record"/ Set Variable (to Barcode Field) . . . and then how would I get back to my original record to enter the value?

Thanks!

Albert

[comment]

Why don't you use auto-entered serial number?

[Wickerman]

I'm not using an auto-entry serial # because not all records in the database are being assigned a barcode #. It's a situation where, for example, an organization enters say 100 records for all materials donated to the organization, but only a selection of these items are selected for full "Acquisition" status and Barcoding.

Actually, I can check and see whether there would be harm in all records having a barcode # after all. But even so, I'd like to get a solution for this sort of scripting scenario for my learning purposes.

Albert

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/172165/

[Fitch]

First of all, I think you're right to use text rather than number for the barcode, since a number cannot start with a zero but a barcode might, and some barcodes do contain non-numeric characters.

Your scripted Find will work, but don't use Find All, since as you say not all records have barcodes. Search instead for non-empty barcodes. Do it in a new window so you won't lose your place.

You didn't say whether it matters if there are gaps between numbers, e.g. if a record gets deleted. If it doesn't, then instead of a scripted Find, you can make a cartesian self-relationship and use Max()+1 or Last()+1.

Michael's suggestion to use a separate table to generate the numbers is also a good approach to consider.

(FYI there is a GetNextSerialValue function, however that's not going to work here since you're not using an auto-enter serial number.)

[comment]

The problem with the proposed scripted method, same as with using a relationship, is that it's easy to get duplicates when two users are doing this more-or-less at the same time.

[Fitch]

True, although it would have to be split second timing. You could perhaps use a one-record preferences table, where you could lock the pref record at the start of the barcode+1 script, and unlock it when finished.

[comment]

it would have to be split second timing

Not necessarily - until user A has committed his/her record, all other users will keep getting the same Max/Last value.

If another table needs to be used, I'd rather make it a 'numerator' table as explained in the link. That way you get a trail, not just a blind count.

[Fitch]

It only takes a split second to create and commit a record.

[comment]

True, IF you do committing by script. Still, there's that split second...

[Wickerman]

You didn't say whether it matters if there are gaps between numbers, e.g. if a record gets deleted. If it doesn't, then instead of a scripted Find, you can make a cartesian self-relationship and use Max()+1 or Last()+1.

Oh, this is a terrific solution -- very simply efficient! Thanks very much.

Albert

[Wickerman]

Whoops! I spoke too soon -- this is working fine, but I've run into another simple problem. I have the Barcode defined as a Text field because the barcode #'s have leading zeros (00005467 for example). The Max+1 function appears to be returning a number, leaving off the zeros (5468).

Best way to address this? Do I need an extra script step counting the digits and then appending (concatenating) the needed number of 0's?

Albert

Edited May 23, 2008 by Guest

[comment]

I still don't think that's a good method, but you can pad a number with leading zeros like this:

SerialIncrement ( "000000" ; number )

[Wickerman]

Ooh! When I opened up my Functions & Scripts book to read about 'SerialIncrement' the book randomly fell open to a Custom Function called . . . . fnPadCharacters !

What are the chances of *that*? It actually provides an example nearly identical to my situation! So I'm taking the occasion to learn a new skill, never having used the Custom Function feature before.

Here goes!

albert

[comment]

Learning a new skill is always good. Using a complex calculation where a simple one would suffice is not.

[Wickerman]

I still don't think that's a good method, but you can pad a number with leading zeros like this: SerialIncrement ( "000000" ; number )

I'm not sure which "that" it is you don't think is a good method -- is it the Cartesian Join with max()+1? I really can't set this up as a Serial# in this situation -- and setting up a separate table for the Barcode #'s seems like overkill . . .

Using a complex calculation where a simple one would suffice is not.

I'm not sure I'm seeing which "simple one" you mean . . . sorry if I'm being slow. At any rate, the fnPadcharacter approach seems to have worked, so I'll stick with it unless I'm missing some sort of bad implication of this approach.

Albert

[comment]

which "that" it is you don't think is a good method -- is it the Cartesian Join with max()+1?

Yes. It's inconsistent (if you delete the last record, then the number is reused, if you delete another record, it's not), and it has a slight chance of producing duplicates. If you don't mind gaps, why not use the "real" serial number? If you don't mind a small risk of duplicates, then a random number would probably do just as well - with a lot less resources. I don't think a separate table is more work than a self-join, but in any case doing it right is not overkill - at least not in my book.

which "simple one" you mean

This one:

SerialIncrement ( "00000000" ; number )

[Wickerman]

Oh I see -- thank you for the clarification.

In this particular case, the issues you raise don't happen to be a problem. It's a situation where a cataloguer is assigning existing barcode numbers from a page of stickers (not random), in ascending order. Numbers from records that have been deleted would indeed not be re-used (or if it needs to be for some odd reason, could be re-entered manually). It's basically just a time/labor-saving button next to the Barcode field - 99% of the time, when a record "needs" a barcode, the cataloguer just wants the next available one in the sequence. So clicking the button should produce the next available sticker # -- or if not, will alert the user that she perhaps forgot to enter the number on the last record. I think I'm all set.

albert

[Fitch]

I have the same book, Albert, and this is not a big deal, but that CF does seem to me a bit over-engineered for this application -- it even calls another CF.

There is Michael's formula or the old standby:

Right( "00000000" & number ; 8 )

[Wickerman]

Ahhhh . . .yes, that would appear to be just a *little* simpler. Thanks again.

albert