Unique Serial Numers?

[rsglewin]

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!

[Raybaudi]

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

[Raybaudi]

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).

[Raybaudi]

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 )

)

)

[Lee Smith]

Nice Work. I especially like the YYYYMMDDXXXX format

Lee

[hasru]

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.

[Raybaudi]

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 )

)

)

[hasru]

I CANT MAKE IT. PLS ATTACHED SAMPLE FILE

TTQ

[Raybaudi]

ok

see the attachment !

AnnualSerial.zip

[hasru]

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

[hasru]

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

[Raybaudi]

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 )

)

)

[tunin]

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.

[Raybaudi]

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 )

)

)

[tunin]

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

[Raybaudi]

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

[tunin]

Thank you.

Over and out!

[mikmac68]

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.

Thanks in advance.

mikmac.

[Jeffrey Whittaker]

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

[Raybaudi]

Hi Jeffrey

there is an email for you.

[Mack (JP)]

Hi, I trouble with below script.

Can you please help me to check what the problem with it?

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

 

Please help.

Thank you