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!

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

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

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 )

)

)

Nice Work. I especially like the YYYYMMDDXXXX format

Lee

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.

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 )

)

)

I CANT MAKE IT. PLS ATTACHED SAMPLE FILE

TTQ

ok

see the attachment !

AnnualSerial.zip

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

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

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 )

)

)

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.

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 )

)

)

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

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!

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.

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

Hi Jeffrey

there is an email for you.

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