Reliably assigning a sequential serial number by SCRIPT

[jasonwood]

I have a system with Plans and Certificates.

Each plan has many certificates.

Within each Plan, certificates must have sequential numbering starting at 1 (not used as a relationship key - it is for other business reasons).

I currently use a relationship and set the CertificateNumber to Max ( relationship to existing certificates in same plan ), but if two users create a certificate at the same time, it will get the same id! I need to make this impossible.

I was thinking...

Put a field in Plans called "NextCertificateNumber".

When creating a new certificate, make sure the Plan record is unlocked, then lock the record, then create my new certificate, setting the CertificateNumber to NextCertificateNumber, then increment NextCertificateNumber, then commit the record (unlock it) allowing other users to run the script...

Assuming you agree that's what I want to do, how do I actually lock that record and ensure it stays locked until the end of the script?

Thanks!

[eos]

Wouldn't this be more efficient?

• Open Record (add error handling should record already be opened, maybe a loop with (a brief) timed pause)

• Set Variable ( $myCertificateNumber ; NextCertificateNumber ]

• Set Field [ NextCertificateNumber [ NextCertificateNumber + 1 ]

• Commit Record

create Certificate using $myCertificateNumber etc.

This minimizes the time the record is open, i.e. not accessible for other users,

but will always generate a unique CertificateNumber.

[jasonwood]

I hadn't thought of using a variable to minimize the amount of time the record is open - good idea.

What's the best way to open the record. Just by using "Set Field" on any field in the record (and set it to itself so it doesn't actually change anything)?

[jasonwood]

Ooops! Never even knew about the "Open Record" Script step!

But I want to do this through a relationship so that won't work. Here's what I have... looks ok?

Set Variable $i = 1

Loop

Set Field Plan::nextCertificateNumber = Plan::nextCertificateNumber

Exit Loop If not Get ( LastError )

Pause for 1 second

Set Variable $i = $i + 1

If $i = 10

Show Custom Dialog "Please try again later"

Exit Script

End If

End Loop

Set Variable $certificateNumber = Plan::nextCertificateNumber

Set Field Plan::nextCertificateNumber = Plan::nextCertificateNumber + 1

Commit Records

Set Field certificateNumber = $certificateNumber

[eos]

Ooops! Never even knew about the "Open Record" Script step!

But I want to do this through a relationship so that won't work. Here's what I have... looks ok?

It's not only using a variable to minimize the lock phase, but also committing the record

as soon as you have incremented the NextCertificateNumber field (aka “grab the value and run”)

and before doing all the remaining, potentially time-costly stuff .

Your script looks good to (I like the timeout part), except for a few points.

Why not lock the record explicitly by using Open Record [ ]?

Also, since you're handling a potential error yourself, you need to set error capture on

to suppress any alerts FM may throw up.

I added some steps which will create the new Plan record simply by going “there” and

setting the required IDs. Setting the value via relationship will simply overwrite the

value in the first matching record, which is probably not what you'd want.

Set Variable $i = 1

Set Error Capture [ On ]

Loop

Open Record

Exit Loop If not Get ( LastError )

Pause for 1 second

Set Variable $i = $i + 1

If $i = 10

Show Custom Dialog "Please try again later"

Exit Script

End If

End Loop

Set Variable $certificateNumber = Plan::nextCertificateNumber

Set Field Plan::nextCertificateNumber = Plan::nextCertificateNumber + 1

Commit Record

Set Variable $planID = Plan::<yourPrimaryPlanKey>

Go to Layout ( anyCertificateLayout )

New Record/Request

Set Field [ TOofAnyCertificateLayout::<yourForeignPlanKey> ; ; $planID ]

Set Field [ TOofAnyCertificateLayout::certificateNumber ; $certificateNumber ]

Commit Record

Go to Layout [ original layout ]

Hope this helps

[bcooney]

What is a certificate is voided or deleted? Do you need to "reuse" that number? How will you handle that?

[jasonwood]

That's great, thanks.

I will never allow deletion and I will not allow the number to be reused in the case of a void, so that's ok.