InfoPath Dev

Hi, I found a way in this forum to filter a column sum, but I don't understand the formula, can someone explain how I can use this formula please?

RaYvA:

Hi, Yes you can do this from a list form.

You can use these formulas in rules to update fields in your schema or you can use them in Calculated Values. Easier to use the 'Edit Formula' (Fx) button to help you. You coulds then also filter the return based on other selected fields in on your form or explicit filters:

Example, after querying the 'Costs' data connection this formula returns the sum of the costs ONLY for the 'Commercial Owner' who's values is 'RaYvA':
sum(xdXDocument:GetDOM("Costs")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Amount[../d:Commercial_x0020_Owner = "RaYvA"])

Cheers

sum(xdXDocument:GetDOM("callup-DC")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:total_x0020_value)

this is what I've got so far, and it gives me the column sum, but I don't understand how @RaYvA filtered it. does anyone understand this? I would like to filter it based on field in this list, but if there is a way to filter based on another list that would also be massively useful Thanks

When you select the total value field you should have an option to filter the data. The part of the XPath in the square brackets above is the XPath filter. If you attach a copy of your form under the Options tab in a reply with some info on where you are trying to do this, I can help you.

haha, duh, thank you so much. I think I may have over promised what I can do with Infopath and now i'm starting to panic. I'm having trouble seeing what's right in front of me

what i'm doing is creating three lists that are all linked to each other, first I have a Contract list; title, end date, value

then I have a vendor list; name, Contract (lookup), proportion (vendors are given a porportion of the contract value)

lastly I have a call-up list, these call-up are made against the contract, so my columns are; title, contract (lookup), vendor (lookup), value

when a call-up is made against a contract I need to know how much has been awarded to the vendor, so on the call-up list i'm getting a filtered total of what has been awarded so far. does this make sense? any suggestions? Thanks

Hi Hilary, thanks, i've posted a brief description of what i'm trying to do, would love any suggestions you might have. I just tried filtering this out, but the design checker told me I'm using something only available in repeating tables, does filtering a column sum only work in repeating tables, I'm hoping i just did it wrong? Thanks

Is there any chance I can see your form? You can attach it under Options in a reply - or email me via the forum, I'll reply and you can send it via email. It is just SO much easier for me to understand when I can see the form.

Hi Hilary, thank you so much, I'll do that shortly. honestly, I haven't built the form yet, I'm still sorting out my columns and have been doing some google research to see if what I want is even possible.

Ok. In general, yes, this should be possible. You'll need to add a query data connection to your call up list so when in the form a vendor and contract are selected, you can query for the existing call up records. If you use query fields in your data connection and set those to your selected vendor and contract prior to querying, you won't need XPath filtering and can just sum the returned rows.

hi Hilary, attached are a screen of the three lists, vendor lookups up to SO, and callup does a lookup to both. the form itself doesn't have any modifications yet, other than a calculated field to sum call-up totals. I'm trying to filter it, but I think i'm doing it wrong because it keeps telling me i'm using a control that needs to be in a repeating table

I'm assuming at this point you have a form? Or you would not be getting this message? Can you let me see the form itself, instead of screenshots of the lists?

this is what I've got so far. I've created two formula controls to pull the total value for the selected vendor and the selected SO. I don't think I need these numbers to save to SharePoint, so I'm not concerned about binding them. Can i use these values in a calculation? I was thinking that I might like to show totals for all vendors on the form, but is that possible? displaying vendors associated with an SO? it should be in SharePoint, but I'm not certain about info path. I don't know how additional lookup columns work in infopath. Thanks again for your help with this, I just started using infopath in January and this task is proving much more complicated than I had originally assumed

So these two calculated values on your form - are they working as anticipated? Or are they what are returning the error you mentioned earlier? In terms of displaying totals for all vendors, for example, you can use a repeating section, and some conditional formatting to hide any that are already displayed - using preceding-sibling formulas. I don't know what this means: "I don't know how additional lookup columns work in InfoPath" - can you explain further?

they are behaving exactly like I want them to, when a vendor or SO gets selected in the form, it calculates a total for that vendor or SO. the error that I'm getting is in the design checker and it references the filtered total for vendor "binding a non-repeating control to a repeating field or group is not supported in sharepoint list forms" it still lets me publish and it works, so.... What I meant by lookup columns is when you use a look-up column in sharepoint, you have the option of showing other columns from the list that was looked up, and these will display the values for the lookedup item. this is my next step because I need the value of the SO and the porportion of the vendor to do some more calculations. What are "preceding-sibling formulas", I've never used a repeating table, do you think what you've referenced would be easy to accomplish? Thanks

Got it. Okay, that design checker message is an InfoPath Browser Form bug - I've only seen it cause an issue if you are using print views (if I recall correctly). If needed, I can help you with that - we can manually modify the form view files to remove the control binding. For showing other Vendor Info, for example, for the selected Vendor - you can use another secondary data connection to the Vendor list and either filter the data on the ID of the selected vendor, OR you could set the query field to the selected vendor, then run the query to get back data for just that vendor (this would be a good idea if you think the vendor list will get very big!). I've added a repeating table to your current Vendor secondary data connection in this copy of the form and added my best guess for conditional formatting to show only data for the selected data. Save locally, right click and select design to open. Preceding-sibling is an XPath function. I can probably help you set up a repeating section that has each vendor name and a total, for example - so let me know if you want a hand with that.

wow, this is really cool. i think it's also finally opened my eyes to what I require. when an SO is selected, i need to see all vendors attached to that SO (repeating table?), from each of those vendors I'll need a total Value (filtered by vendor), along with a % that is [vendors total / SO total], pull in the porportion % from the Vendor list, compare them, and show which vendor is "next", I think this would be a sort on the % and return the vendor with the most % to fill to get to the right proportion. does this make sense to you? barely makes sense to me, haha. do you think I'll be able to make infopath do this? Thanks again!

Sorting will be an issue here. I think in terms of the display of data you want, that will be possible. We could highlight instead of sort - highlight the row with the lowest percent?