# Unique Serial Numers?

This topic is 2767 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Hi,

This is probably a very simple question for most of you... I am trying to define a field so that it automatically inputs a serial number in the following format:

mmddyyyy##

So, if the date is 01/09/2006 and it's the first entry of that day, the serial number would be 010920061. If it's 01/09/2006 and it's the second entry of that day, the SN would be 010920062.

Any help would be appreciated!

Thanks and Happy Holidays!

##### Share on other sites

Hi rsglewin

not so simple...

but this is the calc for you:

Let([

digit = 2;

today = Get ( CurrentDate );

lastId =GetNthRecord ( id ; Get ( TotalRecordCount ) - 1);

lastDate =Date ( Left ( lastId ; 2 ) ; Middle ( lastId ; 3 ; 2 ) ;Middle( lastId ; 5;4 ) )

];

Right ( "0" & Month( today ) ; 2 ) & Right ( "0" & Day ( today ) ; 2 ) & Year ( today ) &

Case(

lastDate ≠ today or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";

SerialIncrement ( Right ( lastId ; digit ) ; 1 )

)

)

BTW look at the attachment :

id.zip

##### Share on other sites

Hi

into the calc above, I have definited the variable: digit = 2

because you wanted 2 digits on the right side of the serial number...

If instead you wish 3 digits (or any other numbers of digits), change it to 3 (or any other number of digits).

##### Share on other sites

So I end up with a custom function, that can be used for:

1) Autoenter a serial number that increments daily

2) the format of the serial number is essentially: YYYYMMDD##

3) the serial number may have how many digits (#) you choosed

4) the format can be specified, so to have:

format = 1: MMDDYYYY##

format = 2: DDMMYYYY##

format = "": YYYYMMDD## <--this is the default result

The Auto-Enter field must be setted to always evaluate;

the custom function isn't recursive, so you can use it as a simple calc;

the custom function will work only with FMP8 and above.

/*

DailySerial custom function

Author: Daniele Raybaudi

Format: DailySerial ( AEfield ; digit ; format )

Result: text

parameters:

AEfield: text - the text field wich will hold the serial number; it must be setted as auto-enter, always evaluate

digit: number - the number of digit (#) that must increment by one and restart from one every new day

format: number - the format can be specified, so to have:

format = 1: MMDDYYYY##

format = 2: DDMMYYYY##

format = "": YYYYMMDD## <--this is the default result

12/26/2005

*/

Let([

today = Get ( CurrentDate );

lastId =GetNthRecord (AEfield ; Get ( TotalRecordCount ) - 1);

dayLastDate = Case(

format = 1;Middle ( lastId ; 3 ; 2 );

format = 2;Left ( lastId ; 2 );

Middle( lastId ; 7;2 )

);

monthLastDate =Case(

format = 1;Left ( lastId ; 2 );

format = 2;Middle ( lastId ; 3 ; 2 );

Middle( lastId ; 5;2 )

);

yearLastDate = Case(

format = 1;Middle( lastId ; 5;4 );

format = 2;Middle ( lastId ; 5 ; 4 );

Left( lastId ; 4 )

);

lastDate =Date ( monthLastDate ; dayLastDate ; yearLastDate )

];

Case(

format = 1;Right ( "0" & Month( today ) ; 2 ) & Right ( "0" & Day ( today ) ; 2 ) & Year ( today );

format = 2;Right ( "0" & Day( today ) ; 2 ) & Right ( "0" & Month ( today ) ; 2 ) & Year ( today );

Year ( today ) & Right ( "0" & Month( today ) ; 2 ) & Right ( "0" & Day ( today ) ; 2 )

)

&

Case(

lastDate ≠ today or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";

SerialIncrement ( Right ( lastId ; digit ) ; 1 )

)

)

##### Share on other sites

Nice Work. I especially like the YYYYMMDDXXXX format

Lee

##### Share on other sites

it's good to control daily record. how to modify that script to control by year.

example

2005001

2005002

2005003

i tried other script using portal by decending record.

##### Share on other sites

AnnualSerial ( AEfield ; digit )

Let([

year = Year ( Get ( CurrentDate ) );

lastId = GetNthRecord (id ; Get ( TotalRecordCount ) - 1);

lastYear = Left ( lastId; 4 )

];

year

&

Case(

year ≠ lastYear or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";

SerialIncrement ( Right ( lastId ; digit ) ; 1 )

)

)

##### Share on other sites

I CANT MAKE IT. PLS ATTACHED SAMPLE FILE

TTQ

##### Share on other sites

ok

see the attachment !

AnnualSerial.zip

##### Share on other sites

thanks for very fast reply. the file meaning full to me. Appriacte very much

##### Share on other sites

again i fail to manupulate the script. i try to change the 3 figure end after year (2005XXX) to 5 figure (2005XXXXX). Which part on the script i have to modify. can i have another sample file relate to my needs

##### Share on other sites

You have to modify the "digit" parameter, so for 5 digit:

Let([

year = Year ( Get ( CurrentDate ) );

lastId = GetNthRecord (id ; Get ( TotalRecordCount ) - 1);

lastYear = Left ( lastId; 4 )

];

year

&

Case(

year ≠ lastYear or Right ( lastId ; 5 ) = 99999; Right ( 100000 ; 4 ) & "1";

SerialIncrement ( Right ( lastId ; 5 ) ; 1 )

)

)

##### Share on other sites

Is it possible to add the USER initials in front of end of this serial? So in case you want to track who entered the data... Thank you.

##### Share on other sites

Hi tunin

yes, it is possible.

I used the accountName

Let([

year = Year ( Get ( CurrentDate ) );

lastId = GetNthRecord (id ; Get ( TotalRecordCount ) - 1);

lastYear = Left ( lastId; 4 )[color:red];

user = Upper ( Left ( Get ( AccountName ); 2 ) )

];

year

&

[color:red]user

&

Case(

year ≠ lastYear or Right ( lastId ; 5 ) = 99999; Right ( 100000 ; 4 ) & "1";

SerialIncrement ( Right ( lastId ; 5 ) ; 1 )

)

)

##### Share on other sites

I am not that good with codding... please help where to put the above code, tried everything.

Let([

count = Case(

Get ( TotalRecordCount ) - 1 = 0;" 00";

" " & Right ( "00" & Get ( TotalRecordCount )-1 ; 2 )

);

lastId =GetNthRecord ( id ; GetAsNumber ( count ));

lastDate =Date ( Left ( lastId ; 2 ) ; Middle ( lastId ; 3 ; 2 ) ;Middle( lastId ; 5;4 ) );

today = Get ( CurrentDate )

];

Case(

lastDate ≠ today or Right ( lastId ; 2 ) = 99;

Right ( "00" & GetAsText ( Month( Get ( CurrentDate ) ) ) ; 2 ) & Right ( "00" & GetAsText ( Day ( Get ( CurrentDate ) ) ) ; 2 ) & GetAsText ( Year ( Get ( CurrentDate ) ) ) & "01";

lastDate = today;

Right ( "00" & GetAsText ( Month ( Get ( CurrentDate ) ) ) ; 2 ) & Right ( "00" & GetAsText ( Day ( Get ( CurrentDate ) ) ) ; 2 ) & GetAsText ( Year ( Get ( CurrentDate ) ) ) & SerialIncrement ( Right ( lastId ; 2 ) ; 1 );

""

)

)

This works but the code for the account name!=!= :B

##### Share on other sites

Ok tunin

this topic is becoming too long...

so now I don't know which type of calc you wish.

1)MMDDAAAA(AN)## where AN = AccountName

2)DDMMAAAA(AN)##

3)AAAAMMDD(AN)##

or the same , but more digits ?

BTW attached an example of 2

idANDaccount.zip

Thank you.

Over and out!

##### Share on other sites

• 11 months later...

Hello every body,

Thanks a lot for that extraordinary forum. I love It.

My problem is:

I use a Valuelist: with 06 possible choices: BSP,BRP,BID,BLE,BRE,BDI. (The choce is made by user).

When done, I want to generate an incremental serial ID for each category, wher the format is

- BSP1,BSP2,BSP3,...

- BRP1,BRP2,BRP3,...

- BID1,BID2,BID3,....

When a new record is added, and the selection of category entred by the user, I want a search in the table to find the last categ serialized and add 1 (exp: BSPxxx+1 and so on) same for the other categories.These Numbers should be displayed auto to the user and no chages allowed on it.

How to perform this.

I found so many posts treating that subject but, I didn't find out how to implement it for my own use.

mikmac.

##### Share on other sites

• 3 weeks later...
• Newbies

Hi,

I am trying to create serialized number sequence that looks like this.

D070001 would be D Year Serial

D = letter D...

07 Year

0001 sequence for year.

The sequence would reset to 0001 at the beginning of the next year...

D070001

D070002 etc.

Thanks

##### Share on other sites

Hi Jeffrey

there is an email for you.

##### Share on other sites

• 7 years later...
Hi, I trouble with below script.
Because I get the answer the serial not increment.
Thank you

Let ([
todayyear = Year (Get (CurrentDate));
todaymonth = Month (Get (CurrentDate));
LastID = GetNthRecord ( AEfield ; Get (TotalRecordCount ) -1);
lastMth = Left (LastID ; 2) ];

Right ( "0" &  todayyear;2) & Right (  "0" & todaymonth; 2 )
& Case(
todaymonth  ≠ lastMth or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit  ; digit-1 ) & "1";
SerialIncrement ( Right ( lastID ; digit ) ; 1 )
)
)

My next serial number for the next record is not increase.
Example: 1402001
1402001
1402001

Thank you
##### Share on other sites

This topic is 2767 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account