Jump to content

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!

Link to comment
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

Link to comment
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).

Link to comment
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 )

)

)

Link to comment
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 )

)

)

Link to comment
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 )

)

)

Link to comment
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 )

)

)

Link to comment
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

Link to comment
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.

Thanks in advance.

mikmac.

Link to comment
Share on other sites

  • 3 weeks later...
  • 7 years later...
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
Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.