Data Connections

Re: Counting list items based on query

mrah75 — Tue, 26 Feb 2013 15:46:55 GMT

Well, I knew that I could set a field's value based on a single query of a data connection, I just didn't realize that I could set three different query fields in the same connection.

Then I just queried the data, and assigned the annual counter field the default value of MAX([Annual Count])+1

It was quite a bit simpler than I was trying to make it, after I thought about it for a little while. Now I just need to figure out how to get rid of the NaN error I'm getting when the counter needs to reset to 1...

MR

Re: Counting list items based on query

Hilary Stoupa — Tue, 26 Feb 2013 15:32:34 GMT

Fantastic! Want to post your solution?

Re: Counting list items based on query

mrah75 — Tue, 26 Feb 2013 15:29:26 GMT

In case anyone is following this, I did figure out how to perform this query...

Re: Counting list items based on query

mrah75 — Thu, 14 Feb 2013 19:52:24 GMT

Thanks for the reply, Brandon, but I don't think that will do what I hope to accomplish.

You see, the counter needs to restart at 1 for the first event of every year, and is also unique to the fields District and Division.

e.g. There can be all of the following event numbers in the list:

William Penn-North-2012-1
William Penn-North-2013-1
William Penn-South-2013-1
Tuscarora-North-2012-1
William Penn-North-2012-2
William Penn-North-2012-3

The "annual counter" needs to be a product of the other three fields...

If I misunderstand your suggestion, please let me know, but it seems to me that the ID field you're speaking of is the ID field of the entire list, which would number into the thousands...

MR

Re: Counting list items based on query

Bnighgrow — Thu, 14 Feb 2013 19:18:42 GMT

You can try to create a new data connection in Info Path 2010. Have that data connection retrieve the 'ID' field from the list/library. Upon load or submit(I would do submit to make sure that two forms don't happen to be open at the same time.)

Then have the Event Number set to concat(District, Division, year(startdate), count(ID) + 1)

Or you can do a max(ID) + 1 which just takes the largest ID and adds 1.

Check this website

http://www.bizsupportonline.net/browserforms/autonumber-infopath-form-submitted-sharepoint-library.htm

I hope this is what you were asking for

Thanks,

Brandon

Counting list items based on query

mrah75 — Mon, 11 Feb 2013 20:25:48 GMT

OK, I'm pretty new to InfoPath and SharePoint. I'm also not sure if this is the correct forum to post this, but here goes... So, I have this SharePoint list and an InfoPath form for adding new items. I've got several DDL's and other fields populated with data - no problem. I need to populate an "Event Number" field based on other selections. The Event Number field is a concatenation of three fields, plus a calculated count field, and I can't figure out how to get the count! The list I'm using is named "Events" District and Division are DDLs, Start Date is a Date Picker. Event Number = [District] & [Division] & YEAR([Start Date]) & ["Annual Counter"] The "Annual Counter" is simply a count + 1 of all the items in the list WHERE District, Division, and Year are equal to the new item. I could do this so easily in Access or with SQL, but I am lost with InfoPath's formulas! Any help is much appreciated! Thanks, MR