Calculating data in templates

Question

Looking for best practices on calculating data based on earlier data for templates. I have a customer who wants their CentralAccount to essentially be an incrementing field. The first user entered should be A1000000, then B100001, then c100002, then back to A100004.

There are a few ways to accomplish this. I've written a stored procedure that will automatically fetch the next best user name. Not sure if this is the best way to accomplish this. I can use CreateCol to fetch the entire Person table, and iterate, but this is probably really inefficient.

Any ideas on how to address this without killing the database?

Markus Weiss-Ehlers · Accepted Answer

The AutoIncrement feature itself (the configuration parameter and the explicit logic behind it) is not part of the product since version 7. But you can use DialogNextID to do the same. 
 The following answer is copied from another thread. It is still using the old object layer syntax but will work fine in 7.1.2 
 
 To get the same behavior you need a template for the column PersonnelNumber like the following: 
 '~~> Calculation of a unique auto incremented number Dim nextid As ISingleDbObject Dim intOrderNumber As Integer = 0 nextid = Connection.CreateSingle("DialogNextID") nextid("Ident_DialogNextID").NewValue = "Person_IdentityNumber" intOrderNumber = CInt(nextid.Custom.CallMethod("GetNextID")) Value = intOrderNumber 
 After that, every newly created person will get an automatically incremented number in that field. 
 The table DialogNextID is used to save the value which will be the next. 
 
 HtH

oliver.paulzen · Answer

Hi George, 
 sounds like a use-case for DialogNextID. With its GetNextID() method you can "fetch" a new integer value. This can easily be done in the CentralAccount@Person template. The prefix (A, B, C) can then be calculated based on the value. Don't forget to unmark "Overwrites" 
 HTH, 
 Oliver

Markus Weiss-Ehlers · Answer

Hi George, 
 you could use the "AutoIncrement" feature of the identity number for the person to achieve this. (Search for AutoIncrement in the IdentityManagement.pdf). 
 If this is activated, the system generates a new identity number based on the value of DialogNextID.NextNumber (where Ident_DialogNextID='Person.IdentityNumber'). 
 The system initializes the value of NextNumber with the max value of Person.IdentityNumber, so creating an entry in the table DialogNextId with Ident_DialogNextID='Person.IdentityNumber' and NextNumber=100000 needs to be done first in your case. 
 Then change your template at Person.CentralAccount to use the identity number like: 
 If CStr($ImportSource$) <> "ADS" And _ (Not CBool(Connection.Variables.Get("FULLSYNC")) Or CStr($ImportSource$).StartsWith("EBS")) Then If Not String.IsNullOrEmpty($IdentityNumber$) Then Select Case (CInt($IdentityNumber$)-100000) \ 3 Case 0 Value = String.Format("A{0}",$IdentityNumber$) Case 1 Value = String.Format("B{0}",$IdentityNumber$) Case Else Value = String.Format("C{0}",$IdentityNumber$) End Select End If End If 
 HtH

Calculating data in templates

Top Replies