Generate sequential number based on fiscal year and month created

ponch0v — Fri, 16 May 2014 14:32:31 GMT

I am working in a government environment (locked down, so cant use any third-party solutions) on Infopath 2007 and Sharepoint 2010.

We have request forms that get submitted through an Infopath form and I need to generate two sequential numbers based on data in the form.

The first needs to be a button on the form that will generate a sequential number based on the fiscal year and month what a date field in the form is. Call this "authority #"

That is, the first form submitted in May will generate 14-05-001, then 14-05-002, etc. The numbing resets each month, so 14-06-001 is the first form submitted in June. The year would also reset in Oct to 15-10-001, and so on.

The second number is generated using a concat of the calendar year, the sequential number and then another text field. I have this one working by querying a list for the max + 1 of the sequential field, then adding the year and text field. This all gets written back to the list using a CAML submit. Call this "log #"

I just cant figure out how to query the first list (authority #) to know what year/month it is, then get the max + 1 of that criteria, then set a field's value of that number.

Sorry if this is confusing, let me know if I can clarify. Thanks for any insight.

Data Connections

Generate sequential number based on fiscal year and month created