1

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.

Tristan
  • 23
  • 7

3 Answers3

2

In InfoPath you can get the current year in a formula (no programming needed) this way.

substring(today(), 1, 4)

However the format might differ if your system settings are not US English (not a good idea if your users have different system settings).

To get the correct data out of SharePoint you will need to filter your list. You are currently asking for all the list data and getting MAX(ID). Filter this list to only get the current year and then get MAX(ID) and it will give you the correct sequence number.

ktharsis
  • 3,160
  • 1
  • 19
  • 30
  • Ktharsis, This seems like the right approach. I have a question though, how am I applying this filter? Am I just filtering the list in SharePoint (which doesn't make much sense, because when you unfilter it - all the IDs will become sequential again or go haywire), or am I adding some sort of function to my MAX(ID) formula in InfoPath to apply a filter? If I am using the latter, how do I add a function to InfoPath to filter for year before polling the MAX(ID)? I will try some things in the mean time while I wait for your answer. PS - Sorry about the delayed response. – Tristan Feb 14 '11 at 12:28
  • I believe you are going to have to get into a little bit of code. I don't have IP/SP handy but from what I remember you can easily change the datasource connection/command properties to filter the data before it is retrieved. You could also setup views in SP that are already filtered and connect to them. I would lean towards the code though - it isn't very much, can be written straight in script (no managed code needed), and doesn't change the security level of the form since it is just touching the datasource you already setup. – ktharsis Feb 22 '11 at 16:27
  • Alternate I just thought of - base your filter on the field where you store your ID (not sharepoints version). So depending on how you store it (probably string) you can do string manipulation - MAX of the last 4 chars (turned into a number and padded with zeros) where the first four chars are the current year. – ktharsis Feb 22 '11 at 16:32
  • Ktharsis, I like your second approach. I am currently researching on how to filter a data connection (so I can filter for the current year). Once I am able to filter my data connection for the current year, I will change my formula to get the MAX of my sequential number and add 1, and not use SharePoints ID field. My only worry is that if there is no sequential number there already (basically a null), I'm not sure if the data connection will return a zero or not. I will let you know the results. If this works, I'll give you credit. – Tristan Feb 23 '11 at 19:56
  • I've been able to filter my list but I have no idea how to default my drop down to the max report number I've filtered. See my latest edit. – Tristan Feb 25 '11 at 13:02
1

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.

Tristan
  • 23
  • 7
0

Maybe I am missing something here, but if you are getting your sequential number from sharepoint, won't that number always increase unless you create a new list or library to pull it from? If you do that then the number should start over at 0.

Ron Nicholson
  • 86
  • 1
  • 5
  • I would like to keep all the infopath forms in one single list if possible. If there is another way to get sequential numbers besides MAX(ID), then I'm all for it. Any suggestions? Remember the sequential numbers have to reset every year (without programming is the preferred method). PS - sorry about the delayed response. – Tristan Feb 14 '11 at 12:22
  • Tristan, I may have a solution but I am busy this afternoon. I'll get it posted in the morning if not before. – Ron Nicholson Feb 14 '11 at 22:15
  • Tristan, I haven't been able to get anything to work. I created a new column named Year that was a DateTime column. With a cacluated column I pulled out the year. This would be the first part of your number. I then created another column named counter. I had intended for the counter column to contain the next value of the number, but could not get this work. If there was a way to do it the next step would be to reset that number at the turn of the year. I just didn't have any luck getting the counter column to autofill. Sorry. – Ron Nicholson Feb 15 '11 at 16:32
  • How to get the the number counter: You have to have a SharePoint list. Use this formula on your submit options: max(@ID) + 1 – Tristan Feb 17 '11 at 19:23
  • EDIT: I can't seem to edit my comment so I'll just continue what I was trying to write. How to get the the number counter: You have to have a SharePoint list. Use this formula on your submit options, under Rules: max(@ID) + 1 (for your number counter field). The main issue here is how do I reset the number counter when there is a new year. Ktharsis said to use a filter. My question is how? – Tristan Feb 17 '11 at 20:04
  • Unless I am missing something obvious, the ID field will never be back to 1. And you can't set it back to 1 as long as there is data in the list. So what you need is a field that contains the counter and it can be set back to 1 at the first of the year. Is it possible to the count of days since the first of the year? Numbers would be skipped and you could only have one entry per day. I have not used filters so I'm not sure how that would help. – Ron Nicholson Feb 17 '11 at 21:11
  • I have no idea how to reset a field back to 1 at the beginning of the year. That's what I'm trying to figure out here. If I'm not able to reset the ID field, then how do I make a sequential counter that can reset every year? I cant seem to figure out the logic. InfoPath doesn't have IF statements, so it makes it more difficult to implement. – Tristan Feb 22 '11 at 13:15
  • I've been able to filter my list but I have no idea how to default my drop down to the max report number I've filtered. See my latest edit. – Tristan Feb 25 '11 at 13:02