DailySerial ( AEfield ; digit ; format )

Autoenter a serial number that increments daily

Average rating: 4.3 (33 votes) Log in to vote

Daniele Raybaudi - Show more from this author
ACI
http://www.aci.it

Sample input:

DailySerial ( id ; 2 ; "" )
DailySerial ( id ; 2 ; 1 )
DailySerial ( id ; 2 ; 2 )

Sample output:

2005122601
1226200501
2612200501

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*

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

This custom function 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.

Comments

		Jeff, Chicago Jan 7, 2013
This almost worked. Output record 1: 070120131 Output record 2: 0701201332 Output record 3: 07012013333 Output record 4: 070120133334 Any ideas where this went wrong?

		Daniele Raybaudi, ACI Jan 7, 2013
How did you enter the function? IOW, which are the values of your parameters?

		Jeff, Chicago Jan 8, 2013
AEfield: DailySerial ( Serial Number ; characters ; "" ) digit: 1* format = "" * I have since discovered that this was wrong and have changed it to a "2" Now I am receiving the following: 2013010801 2013010801 2013010801 note that It is not advancing to the next value.

		Jeff, Chicago Jan 8, 2013
Okay, false alarm and I have re-implemented the custom function and it now works well. Thank you!

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

DailySerial ( AEfield ; digit ; format )

Comments

Support this website.

Support this website.