I wish to create a serial number based on this calculation...

The authority prescription number is eight digits in length.

The first seven digits are the base number, incremented by one for each subsequent prescription, with the eighth digit being a

It's a bit tedious, but not difficult.

First, define a TEXT field named BaseSerial. In field options, specify auto-enter serial number, next value = 0003457, increment by 1.

Next, define a calculation field (result is text) with the following calculation:

Let ( [

input = GetAsNumber ( BaseSerial ) ;

pos1 = Mod ( Div ( input ; 10^0 ) ; 10 ) ;

pos2 = Mod ( Div ( input ; 10^1 ) ; 10 ) ;

pos3 = Mod ( Div ( input ; 10^2 ) ; 10 ) ;

pos4 = Mod ( Div ( input ; 10^3 ) ; 10 ) ;

pos5 = Mod ( Div ( input ; 10^4 ) ; 10 ) ;

pos6 = Mod ( Div ( input ; 10^5 ) ; 10 ) ;

pos7 = Mod ( Div ( input ; 10^6 ) ; 10 ) ;

checkSum = ( pos1 + pos2 + pos3 + pos4 + pos5 + pos6 + pos7 ) / 9 ;

checkDigit = Mod ( Int ( checkSum * 10 ) ; 10)

] ;

BaseSerial & checkDigit

)

First you need something to calculate the base:

"000" & Middle ( prescriberNbr; Length ( FMforums_calcTest::prescriber )-4; 4)

Then you need a scripted fashion of upping that base number with one each time a new prescription is generated.

Once you have that you can use a calc or a scripted set field to generate the prescription number (concatenate the base number and the mod of the sum of the numbers in the string)

base & Mod ( sumNumbersInString ( base;0); 9)

"sumNumbersInString" is a custom function I wrote to make this easy. Takes two inputs: string and theSum (initially set to 0)

Let

(

// variables

[

leftOne = Left(string;1);

rest = Case(Length(string)>0;GetAsText(Right(string; Length(string)-1));"");

tempsum = thesum+ leftone

];

// calculation

If(Length(rest) > 0; sumNumbersInString(rest;tempsum); tempsum)

)

Custom functions are great for this kind of recursive "add one element to another".

Thanks a lot, this seems more 'understandable and intuitive" and it works!!

cheers