InfoPath Dev

Hi Everybody. I need your help!. I created  a basic InfoPath 2013 form for a SharePoint list and can´t figure out why the count() function is not returning the expected result. I created a form with 4 fields (Device - Location - Key (Device + Location) - Number).  The first two fields are drop-down lists. The first one gets the data from another SP list containing devices name, the second drop-down it has a list of cities listed in a second SP list and the Key field is basically the result of concatenating Devices and Location. Im trying to get the number of records in the main SP list (I assume it is the one for the form) that match the key field but for some reason isn´t counting. I created a rule for the Location field to set the Key field value with the result of concat(Device + Location) and then set the Number field with the result of count(Key [. = "Key"]. I created a secondary data connection to the main list trying to make this work but after many attemps I had not good results. See below example:

Device: IPad
Location: Chicago
Key: IPadChicago
DNS: xxxx

If there are two records in the main list having for example "IPadChicago" in the Key field then the next DNS number should be 3 as the result of count(Key [. = Key] + 1. For some reason the the formula is not retrieving the value of the key field at the time of running the count() function so Im wondering if there is something that Im doing wrong or if this is a bug. This is a very simple form as you can see and the only way I could make the count() function to work was by hardcoding the value i.e. count(Key [. = "IPadChicago"]) but this is not very usefull.

Thanks you very much for your time and I appreciate your help.

 

Any chance I can get you to attach a copy of your form under the Options tab in a reply?

Hi Hilary. Thanks for replying. I attached a copy of the form to the post. Please check the rules set for the Location field, the second one is the rule for counting records in the list. I created a seconday connection VCMainV2-Read for this purpose which is pointing to the same SP list (main) due to when I tried count(Key [. = Key]) didn´t work, I guess it is because is referencing to the same field therefore a secondary connection must be created.

Regards.

Your instinct is right - you needed a secondary data connection to return all the records from the list so you could count them. Personally, I would probably set that data connection to NOT run when the form is opened, and instead, on the Key field, set the query field in the secondary data connection to the current key, then run the query. Then you'd only get back the matching rows, and you could count w/out a filter. :-) However, here's a copy of your form with what I think will work for your filter. Save the file locally, right click and select design to open in design mode, then you can preview. Let me know if you'd like more details on using a query field to "pre-filter" the data!

 Thanks you Hilary!!! It worked perfectly, finally is counting as it should. I noticed in the formula the difference between GetDOM and get-DOM(), is that what you refer to when talking about query filed?. I would like to now more about it.

I appreciate your help, you made my day :D.

 

Regards.

GetDOM is the secondary data connection. get-DOM() is the main connection. You may have been selecting the filter fields correctly, but accidentally selected the query field in the main DS instead of the data field - that's an error I've seen before. :-)

 I tried selecting fields from diffent sources and everytime I checked the xPath syntax I saw GetDOM instead of get-DOM, even when I selected query fields instead of data fields. Perhaps Im doing something wrong but looks more related to a bug.

In the other hand, last questions, is it possible to run a count based on 2 fields like count(field1[. = xxxx] and field2[. = xxxx]). I created the key field for this reason due to I needed to match devices and location to get a unique value.

Absolutely - you can add as many fields as you need to the filter. However, how large do you expect your list to get? I'll modify a copy of your form later today to leverage query fields. Then you'll only be pulling back the data you need to count and you can get rid of your Key field.

Try the attached - You'll see on the location field I disabled your initial rules and added one to set query fields & query, then another for the count. I changed your secondary data connection to not query on load. You'll probably want similar rules on Device, in case someone changes that after initially setting both fields. :-)

Hi Hilary Thanks, it worked perfectly. Apologize for my late response, I had to take care of a personal situation but Im back!. I have one more question that came across the other day, I hope this is my last. Is it possible to count values and get the next number following the sequence?. Look at below example. I have a list of numbers that were taken: 1001 1002 -> 1003 1004 1005 -> 1006 -> 1007 1008 What Im trying to do is to count all the values based on what I asked you on my previous questions but also to get the next number to continue the sequence, in this case it will be 1003 and next time it will be 1006 and so on. The reason of this requirement is due to we have a list of devices with a number previously assigned and we have to migrate the list to the new SharePoint site, once this is completed we will enable the form. Please let me know if you need further clarification. Regards.