Hi,
I have two sharepoint lists
1. Automation - this list has a whole bunch of fields, but the main ones involved in my issue now are: ID, Title, totalScore
2. Automation-Score - this list has a bunch of fields, but the main ones involved in my issue are: ID, AutomationID (number field), user, score (number field)
I am working on the form for the Automation list. I have a secondary data connection to Automation-Score. I have added Automation-Score fields into the Automation InfoPath form as a repeating table, and I'd like to sum all of the scores up in the footer row, into the field totalScore. I need to only show Automation-Score lines where AutomationID = ID from the Automation list item
I managed to filter out the lines where automationid <> the ID in the automation list by using rules, but the sum still shows the sum of all records in the list, regardless of automation ID, so I got buried in the fabulous world of xpath.
I am using Automation item #59 as my test item, so I put 3 items in the automation-score list and set their AutomationID to 59. The rule to hide the non-59 ones works beautifully. However, I can't seem to get my xpath formula to play nice!
Here are some of the formula's I've tried in order to get the correct total to be calculated for totalScore
1. sum(xdXDocument:GetDOM("Automation-Score")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:ID = /dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:ID]/d:Score) -> This always returns 0
2. sum(xdXDocument:GetDOM("Automation-Score")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:ID = "59"]/d:Score) -> This returns the correct total for item #59, but obviously won't work for the rest
3. sum(xdXDocument:GetDOM("Automation-Score")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[number(d:ID)= number(/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:ID)]/d:Score) -> This always returns 0
4. sum(xdXDocument:GetDOM("Automation-Score")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Score) -> returns the total for all items in Automation-Score (thought I might get lucky with my rules on the table. Sadly, no)
5. /dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:ID]/d:Score -> returns the number of the current Automation Item (as expected, more just wanted to check that piece of my formula)
Any idea what I'm doing wrong?