I am using SharePoint 2010 and InfoPath 2010.
I am trying to create a report which works out the payments we need to make for people on a rota. I have 3 lists in SharePoint
Rota which has Month, Year, Team, Person, Date - this is the team someone belongs to and the date they are on the rota.
Payment Days which has Date and PaymentType - this is the type of payment you get for the date - eg is it a weekend, public holiday
PaymentType which has 2 columns Title and Payment - this is the value of the payment type
I have the data connections in InfoPath and am able to create a table which has Month, Year, Team, Person, Date, Payment all correct - by using the current() function.
(xdXDocument:GetDOM("PaymentType")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Payment[../d:Title = xdXDocument:GetDOM("PaymentDays")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:PaymentType[../d:Date = current()/d:Date]])
I now need to sum all the payments for a particular person in a team for the month, followed by a sum of all the payments for the team for the month and then all the payments for the month regardless of team.
I cannot get my head round it today. The key to ensuring the correct payment was made for the day was the =current()/d:date at the end of the xpath.... but when wanting to sum the payments there are numerous dates
I can envision exactly what to do if it were sql tables but not in this case...
Thank you.
Catharine