Custom Serial Generator

[caseyc]

I need to create a calculation to that will insert a serial number on demand (ie I want sequential serial numbers, but I don't want one generated for every record (sometimes I want to be able to enter a serial number by hand for legacy records) I need help coming up with a calculation to be performed on a button click but have had trouble (I am trying to use the Max command).

Basically I want, whenever I click on this button the serial number to be set to the highest number in that column + 1

Any help would be greatly appreciated.

Thanks

[T-Square]

Welcome to the Forums!

Unless you have a compelling reason (i.e., you'll go to jail if you don't do this), you would be wiser simply to create an autoenter serial number key field, and let it go on its merry way creating unique numbers for you.

You can always set up a second field to use for display to users; it can be click-to-assign, or it could be a calculation.

Unfortunately, without more information about your application, its difficult to propose a better solution.

David

[David Jondreau]

Max() doesn't work across records of the current table occurence. So...create an "x" relationship (aka Cartesian join) between your current TO and a TO based on the same table (aka self-join). Then Max() on the related field.

[T-Square]

@dj: alternatively (if you're going this route), create the self join on the ID field and sort it descending. Then the first entry is the highest existing ID.

[LaRetta]

Hey Casey,

You are playing with fire. I hope you also have validation (unique) on your script-generated serials because otherswise you can get duplicates by accident (if a User makes a mistake). I also hope you are NOT creating related records using this serial to bind your relationships because, if a User mis-types the serial and kids are created, you will have problems correcting the relationships. And it will also break in multi-user because of record-locking.

Please view the attached file and click the New Record link and see DUPLICATE SERIALS FROM TWO USERS. The first window is one User - the second window is another User. Do you see what happens?

You need to use an FM-generated auto-enter serial. If you wish to capture a different serial number (an external one) then no problem ... but treat it as straight information just as you would if capturing someone's phone number or social security number, as David points out. Don't use it for relationships at ALL. And don't worry about your serials not being sequential - that's life - you will sometimes delete a record and that's fine just as it's fine to assign serials to records which may never need it.

Better to assign FM serials and not need them than to screw up your critial relationships. Please view attached file for example of why one should NEVER EVER use Max() or any self-join if wishing to grab a serial + 1 value.

I could link you to some posts about this subject but I don't have time. (Attached file temporarily removed)

UPDATE: In this instance, using a simple second window to demonstrate a second User with record-lock won't work. But this process DOES record-lock in multi-user and can be seen if the file is served and tested or if IWP is used; neither of those options can be attached here on forums. So you will need to either test it for yourself or just take my word for it.

LaRetta

Edited February 12, 2009 by Guest
Added Update