InfoPath Dev

Hi, I am really struggling with this and I'm hoping someone will be able to help! I have two lists List A (Area) and List B (Units). List B has a Lookup field (called Area) to List A (Column "Title"). List B has a status field which can one of two Status - "In service" and "Out of service". I have two fields in List A called "In service" and "Out of service"so beside each Area item I want to show a count in the field "In service" where all the items in List B Area field is equal to List A Title Field AND Status is equal to "In service" I have tried a few ways to do this and cant find anything that works . Thanks in advance to anyone that may have any insight!!! Dee

Any chance you can attach your template (under the Options tab in a reply) - the XPath filter for this will depend on your data sources.... Also, just in case you weren't aware, XPath is case sensitive, so if your list data is actually "In Service" instead of "In service" for example, you won't get a match on that text :)

Hi Hilary, I tried a few times to save as template but am having no luck. It may be something to do with the company permissions. What I have tried is I am opening Area list in Infopath and have setup a data connection to the second list (pulling the Fields "Area" and "Status") then in Area List in Field called In Service I have set the default value to count(Area[. = Title and Status = "In Service"]) So count in List 2 (using second data connection and fields selected from dataFields) the field Area and then filtered to Area (second data conn) = Title (Main Connection) AND where Status (Second Connection) = In Service (in correct case) . All fields have been selected from the dataField drop down for both connections. This always returns a value of 0, when there should be a higher value. I'm obviously missing something??? I hope I explained that okay. Any advice you could give would really be appreciated. Dee

In the formula editor, can you check the Edit XPath option and paste the XPath into a reply?

Hi Hilary, The Xpath is as follows: count(xdXDocument:GetDOM("RDT List Count Status")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Area[. = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Title and ../d:Status = "In Service"]) Thanks!!!!

So this is a default value in a field in the main data source in the form? I'd guess this might work: count(xdXDocument:GetDOM("RDT List Count Status")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Area[. = current()/../my:Title and ../d:Status = "In Service"])

Hi Hilary, I have tried the new formula and it's still returning a value of 0, in saying that I hope that I applied it correctly, it checked the XPath option in the formula box and pasted in the new formula. Thanks so much for your help, Dee

I think you'll have to find a way to get me the template, I'm afraid. When you have the template in design mode, can you navigate to File > Save As to save a copy? And then attach the saved file to a reply?

Hi Hilary would it be okay if I sent you a PM??

We found the issue - since Area was a lookup in the secondary list, we needed to change the filter to use ID instead: count(xdXDocument:GetDOM("RDT List Count Status")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Area[. = current()/../my:ID and ../d:Status = "In Service"])