number increment reset annually on given date

(0) Share

Report

Posted on by R Bakker

725

Hi everyone

I have a number that increments with every new record, eg.. IR 21-29, IR 21-30, IR 21-31 and on the first of October it switches to the next year as the following.. IR 22-32, IR 22-33, IR 22-34, which is good but what I also want is that the last two digits to reset on the 1st of October like.. IR 22-01, IR 22-02, IR 22-03

Sp as Datasource.

Thank you for your time.

All responses (2)

Answers (1)

Vedant28 86 on at

Like (0)

Report
Re: number increment reset annually on given date

hi Bof
i am facing the same issue but my code get stopped in november
i tried two code:
code 1
If(Month(Today())>=11&&Day(Today())>=1,Dropdown24_1.Selected.Value&"-DIS-"&Year(Today())+1&"-001",Dropdown24_1.Selected.Value&"-DIS-"&Text(DateAdd(Now(),2,Months),"[$-en-US]yyyy")&"-"&Text(Value(TextInput27_5.Text)+1,"[$-en-US]000"))
Code 2:
With( { wUS_CASE: Dropdown24_1.Selected.Value &"-DIS-"&Year(Today())+1 &"-001", wLastID: First(Sort( DISLIST, Case_Number, Descending )).Case_Number }, If( Month(Today()) >= 11 && Day(Today()) >= 1 && CountRows( Filter( DISLIST, Case_Number = wUS_CASE ) ) = 0, // then case wUS_CASE, // else case Left(wLastID,13) & Text( Value( Right( wLastID,3 )) + 1, "00") ) )
in both the case i am not able to submit the form after nov 1. Until oct 31 it was working fine.,

thank you in advance
Verified answer

v-bofeng-msft on at

Like (0)

Report
Re: number increment reset annually on given date

Hi @Corissandageri :

Could you please tell me:

Is October 1st of each year the first day of each cycle?

I've made a test for you reference,I assume there is a collection something like:

ClearCollect(TheList,{ID:"IR 21-01"},{ID:"IR 21-02"},{ID:"IR 21-03"},{ID:"IR 21-04"})

You could add a row by this formula:

With( {Year:If(Month(Today())>=10,Text(Today(),"yy")+1,Text(Today(),"yy"))}, If( IsBlank(LookUp(TheList,"IR "&Year in ID)), Collect(TheList,{ID:"IR "&Year&"-01"}), Collect(TheList,{ID:"IR "&Year&"-"&Text(Last(Split(Last(TheList).ID,"-")).Result+1,"00")}) ) )

Best Regards,

Bof