Restart the ID once the fiscal year started

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

HI expert,

i am trying create an auto generated id like in the format

"US-CASE-2022-001"

my fiscal year starts from November

so from November i again want the id to restart from 001

so on Nov 1 2022, when user enter the record the new id that is autogenerated would look like as "US-CASE-2023-001"

thank you again

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous
You have very little detail around your use case but some general logic would look like
With( { wUS_CASE: "US-CASE-" & Year(Today())+1 & "-001", wLastID: First(Sort( yourDataSource, FiscalYearID, Descending )).FiscalYearID }, If( Month(Today()) >= 11 && Day(Today()) >= 1 && CountRows( Filter( yourDataSource, FiscalYearID = wUS_CASE ) ) = 0, // then case wUS_CASE, // else case Left(wLastID,13) & Text( Value( Right( wLastID,3 )) + 1, "000") ) ) // check using a DatePicker called DP001 With( { wUS_CASE: "US-CASE-" & Year(DP001.SelectedDate)+1 & "-001", wLastID: First(Sort( yourDataSource, FiscalYearID, Descending )).FiscalYearID }, If( Month(DP001.SelectedDate) >= 11 && Day(DP001.SelectedDate) >= 1 && CountRows( Filter( yourDataSource, FiscalYearID = wUS_CASE ) ) = 0, // then case wUS_CASE, // else case // I've assumed your ID length and setup never changes Left(wLastID,13) & Text( Value( Right( wLastID,3 )) + 1, "000") ) )

I've added some code for you to check this using a datepicker, if you want to. I've also assume that the Next Fiscal Year starts in November eg 1-Nov-2022 Fiscal Year = 2023
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

hi Eddie,
thank you for the response
what i have done is in form
OnSuccess: Set(PCounter, NLast+1) "//Nlast is textinput"
Set(UniquecaseID,CountryDDL.Selected.Value&Text(DateAdd(Now(),2,Months),"[$-en-US]yyyy")&"-"&Text(Value(PCounter)+1,"[$-en-US]yyyy"));
in Nlast: in deafult: Last(Split(Master.UniqueID,"-")).Result
so here
in Uniqque ID datacard the value is defaulted to UniquecaseID variable
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Set(PCounter, NLast+1) "//Nlast is textinput"
in Nlast: in deafult: Last(Split(Master.UniqueID,"-")).Result

On success screen of Form:
If(Month(Today())>11&Day(Today())>1&&CountRows(Filter(Filter(Master,FileName="Case"),fileID=Last(Filter(Master,FileName="Case"))=0,Set(UniqueID,CtryDDL.Selected.Value&"-CASE-"&Year(Today())+1&"-001"),Set(UniqueID,CtryDDl,SelectedValue&"-CASE-"&Text(DateAdd(Now(),Months),"[$-en-US],yyyy")&"-"&Text(Value(Pcounter)+1,"[$-en-US],yyyy")));
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

when i save t he record than than the ID e number is not generated and once saved the default value like
-Case-2022-001 is saved in text box, and new item is added in SharePoint list but without ID field
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
hi Expert
i have a sharepoint list with Case_Number as ID column and my ID is in <Country>-CASE-YYYY-<Number" i,e. US-CASE-2022-001
country has a dropdown as US canada,
Default section of Textinput the following code is inserted to get the last id

Last(Split(Last(Filter(Master, Filename="GOV")).CaseUniqueID,"-")).Result

in form in ON success button:
following code is written:
this will take this 185 and add 1 to the variable IDcounter

Set(IDcounter,LastNumber+1);

now in next portion: there is condition that will check the year and if the date is greater than last date of October than the fiscal year changes and the id number get restarted from 1
in ON success screen of the form following code is written:

If(Month(Today())>=11 && Day(Today())>=1 && CountRows(Filter(Filter(Master,Filename="GOV"),CaseUniqueID=Last(Filter(Master,Filename="GOV")).CaseUniqueID))=0,Set(UniqueCaseID,CountryDDl.Selected.Value&"-Case-" & Year(Today())+1 & "-001"), Set(UniqueCaseID,CountryDDL.Selected.Value&"-Case-" & Text(DateAdd(Now(),2,Months),"[$-en-US]yyyy")&"-"&Text(Value(IDcounter),"[$-en-US]000")));

this above code was supposed to generate the new caseUnqiue id by adding 1 to the last id,

now in save button ONSelect property following code is written to check if the generateded id already exist or not.
If(datacardUniqueID.text=Last(Filter(Master,FileName="GOV")).CaseUniqueID,Notify("DuplicateID",Error),SubmitForm(Form2))

If(datacardUniqueID.text=Last(Filter(Master,FileName="GOV")).CaseUniqueID,Notify("DuplicateID",Error),SubmitForm(Form2))

Now, i am having error as the id is not generated and the country is not selected from the dropdown, further there is title warning in the the save button.
my sharepoint looks similar below. so i am only generating case id based on the filter filename so only id for GOV is generated.

my code is not working please help

CaseUnqiueID Name Country Filename
US-Case-2022-001 xyz US GOV
CAn-Case-2022-002 abc CAN GOV
docs-case-2022-Ca-001 dba CAN prvt

please advise
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
hi EddieE

I used your code below but now, after nov 1 the id did restart at 1 but then it get stopped to generate and cannot submit form as well. it was working previously till nov 1

please advise

With( { wUS_CASE: CountryDDL.Selected.Value & Year(Today())+1 & "-001", wLastID: First(Sort( Forecast, ProfielID, Descending )).ProfileID }, If( Month(Today()) >= 11 && Day(Today()) >= 1 && CountRows( Filter( Forecast, ProfileID = wUS_CASE ) ) = 0, // then case wUS_CASE, // else case Left(wLastID,13) & Text( Value( Right( wLastID,3 )) + 1, "00") ) )