Edit 4:
I was finally able to solve my own issue. See checkmark answer below.
Edit 3: I feel like I'm really close to solving this, just one hurdle remains in my way. Filters can only be applied in list boxes, combo boxes, or drop down list boxes. I am able to get my information filtered, but I need to get a MAX value for the information I've filtered and I have no idea how to do this. I've tried the max function and all I'm getting is NaN.
See screenshot.
You'll notice my "Current Year #s +1 defaults to NaN. But when I select a number from the filtered drop down, it works. I need to be able to default to the max number in my list. See another screenshot. I can't seem to figure out how to default to the max report number. Any suggestions or ideas?
Edit 2: Question still remains unanswered. Going to move most recent edits to the top.
Edit 1: It seems like we are going with Ktharsis approach. He suggested using a filter for the year before we get the MAX(@ID) +1. I'm not sure how to implement this yet. Is there a formula to filter for the year in Sharepoint before getting the MAX(@ID) +1 so that our sequential IDs will reset for each year? I will post another update once this is answered.
I am trying to create a unique number counter ID that will reset on the new year. I have the number counter part down, but I have no idea how to make it reset when a new year begins.
Example: 2010-0057, 2010-0058, 2010-0059..... 2011-0001, 2011-0002 etc.
Instead it's just continuing the sequence. Any ideas on how to implement this? The preferred method is without the use of programming. On a side note, I am integrating this with a SharePoint site and I am actually getting my sequential number from SharePoint's ID field. I am using a MAX(ID) formula for my number sequence.
** First time using this site so I am sorry if this question doesn't belong here.
I've finally figured it out! Although Ktharsis did give me several tips, he did not answer my question to where I actually had something that worked. Therefore (not to be a prick), I am answering my own question and showing you guys what I did.
How I reset the autogenerating number at every new year: Ignore all my previous edits about using drop box filters. None of that works. This what you need. On your submit rules, add a rule to query data from your sharepoint location (not entirely sure if this step is necessary, but it doesn't hurt). Add another rule below to "Set a fields value". Select your "Number Counter Field" under the "Field" option.
Under the "Value" option, enter this:
max(@Report_Number[@Year = substring-before(today(), "-")]) + 1
This will get the max report number for the CURRENT year and add 1 to it. So if report number 20 is your highest report number in your sharepoint database, it will now make this report you are submitting number 21. This will start over each year.
For my form, my "Number Counter Field" basically feeds my "Report Number Field" and adds zeros when necessary. For example if I'm on report number 2, it will post Report Number 0002.
I haven't tried seeing what happens if there aren't any reports for the new year. But you can add a rule right before this to find the max report number and if it is zero, make this report number 1.
If you have any questions on my solution. Please let me know. I've spent months on this problem before I posted the question. Thanks to everyone that helped.