InfoPath Dev

Hi I'm trying to create a form to capture Change and need to have AutoNumbering on the form base on Current Year and customer Prefix. The auto number need to be reset when it cross over the year. The format will be yyyy[Customer Prefix][AutoNumber], so example the form capture 2019AB01, 2019BC01, 2019BC02..2019AB35... Then in year 2020, the form should capture as 2020AB01, 2020BC01 and not 2020AB36 or 2020BC03. How do I get it to reset and restart the auto number again base on the Customer Prefix. Anyone can advise on this.

So - I dislike any such auto numbering sequence - I think it is fraught with the possibility for error. However, I understand that sometimes business requirements aren't as flexible as one would like! I'd probably promote the year to the library you submit to (assuming the form submits to SharePoint?) and then you can set the year query field, query the list, and if no rows are returned, you know it is 1. If rows are returned, you can count them and add one.

Hi Hilary Yes, I'm submitting the form to SharePoint. Sorry, I'm not sure how to query the list to retrieve the last record in the list base on the year field. I do have a Year field in the form, and the autonumbering field with this formula in the field "max(ReqNumber[CustPreFix = ReqPrefix]) + 1". it will look for the biggest number in the list and do a incremental of 1. My Year Field is set as "substring(today(),1,4)" I can't do If and Else statement in InfoPath, so I'm not sure how can I reset the autonumbering when the Year Field is a new Year.

When you publish the form to the library, include the Year field as a promoted property. Then, add a secondary data connection to the same library that your form is published to - make sure to deselect the checkbox that automatically loads data when the form is opened - you don't need data till you get ready to submit your form. As part of your submit rules, when you set the field that contains this sequential value (have a condition to only set it when blank so the value stays stable), first set the Year query field (your secondary data connection to the library will have query and data fields) to the year in the current form. Then run the query. All the returned data will be for the same year as the current form. If the count of returned rows = 0, you know that this is the first entry for the year. Otherwise, you should be able to count the returned rows to find the correct number of entries and then increment that.