6 Replies Latest reply on May 26, 2012 2:54 PM by AllPointsMediaWorks

How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

I'm needing to accomadate a case number that creates a unique case number based on year/month of creation and increments sequentionally. Each month the sequence resets to 1. I'm presuming we have a autoenter field to create the date, but the part that's got me confused at the moment is the sequence reset requirement. I would presume I somehow need to evaluate the last batch number and compare against the current date or perhaps a global field and determine if that day is different from today's date and if so reset another global field or somethign like this.

Anybody have a best practice example for this?

Thanks!

Jim

Note that this needs to be able to support multiple users creating cases concurrently with no duplicates numbers.

• 1. Re: How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

Easy just have your script that is attached to your button to add a new record +1 to the last three digits of your number and if the day of the month equals "1" just replace the last three digits to "001". No calculation field is needed for this at all just a text field and let your script do all the math and set the number for you no dates fields or anything else. Just use the code below:

If (DayOfMonth = 1

SetField (Counter) Year(Get( CurrentDate )) & If(Length( Month(Get ( CurrentDate )) )=1; 0 &  Month(Get ( CurrentDate )) ;Month(Get ( CurrentDate ))) & "001"

Else

SetField (Counter)

Let([

Counter = Counter Field;

Len = Length(Counter Field)];

Replace ( Counter; 7;3 ; Case(

Right(Counter;3)+1 ≤ 9; 00 &  Right(Counter;3)+1 ;

Right(Counter;3)+1  >   9 and  Right(Counter;3)+1 ≤ 99; 0 & Right(Counter;3)+1 ;

Right(Counter;3)+1   ≥   100;Right(Counter;3)+1

)))

In place of the counter field place your field.

You see, nothing to it

-Mark

• 2. Re: How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

What happens when the 2nd new record is made on the 1st of the month?

• 3. Re: How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

Yes good point. You'll need to make a self join relationship based on an auto enter text field of year and month like 20125 and then check that the first record for that month hasn't been created. You do this but using the List and value count function to count how many records have been been already that month and if it's less then 2 then reset the counter. Sorry I should have thought of that. Therefore change the first part of the formula like this:

If (DayOfMonth = 1 and ValueCount(List(SelfJoinPrimaryKey)  < 2;

SetField (Counter) Year(Get( CurrentDate )) & If(Length( Month(Get ( CurrentDate )) )=1; 0 &  Month(Get ( CurrentDate )) ;Month(Get ( CurrentDate ))) & "001"

• 4. Re: How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

Another possibility is no new rerecord is created on the first day of the month.

• 5. Re: How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

Yes that is true so just use the second part of the equation to check if any records have been created that month or not regardless of the day using the self join. this way you solution is just asking "Have any records been created this month?" If no start at one, if yes increment the current number. I think this is the most foolproof approach.

• 6. Re: How to create a Batch (Case) number Like 201205XXX Where xxx resets each month?

Thanks All! Appreciate the combined efforts on this.

Regards,

Jim