InfoPath Dev

 I have a repeating table containing employees timecard details.  I need to count the number of absences in the last 12 months for a particular employee.

The count is bound to the employee ID field.  I can count absences ("A") for a chosen employee, but I have a problem when I introduce a date filter.

Please Help

the xPath I have so far is:

count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[@NotesSun = "A" and @ID = current() and number(concat(substring(string(xdXDocument:get-DOM()/dfs:myFields/my:FormFields/my:CountFields/my:AnotherDate), 1, 4), substring(string(xdXDocument:get-DOM()/dfs:myFields/my:FormFields/my:CountFields/my:AnotherDate), 6, 2), substring(string(xdXDocument:get-DOM()/dfs:myFields/my:FormFields/my:CountFields/my:AnotherDate), 9, 2))) > number(concat(substring(string(xdXDocument:GetDOM("Absence")/dfs:myFields/dfs:dataFields/d:Absence/@AbsenceDate), 1, 4), substring(string(xdXDocument:GetDOM("Absence")/dfs:myFields/dfs:dataFields/d:Absence/@AbsenceDate), 6, 2), substring(string(xdXDocument:GetDOM("Absence")/dfs:myFields/dfs:dataFields/d:Absence/@AbsenceDate), 9, 2)))])

 This might help you - http://www.manjuke.com/2011/01/how-to-filter-data-correctly-on.html

 For those interested - the solution was:

Set all date formats to yyyy-mm-dd (you may need to change the format saved when using Access DB tables)

Convert date to integer in a second field using :   number(concat(substring(string(xdDate:AddDays(xdDate:Today(), -365)), 1, 4), substring(string(xdDate:AddDays(xdDate:Today(), -365)), 6, 2), substring(string(xdDate:AddDays(xdDate:Today(), -365)), 9, 2)))

Note that this returns a value 1 year (365 days) before today

I converted the second date to an integer in the conditional statement using the following :

 number(concat(substring(string(@StartDate), 1, 4), substring(string(@StartDate), 6, 2), substring(string(@StartDate), 9, 2)))

Note that relative xPath has been used. The absolute xPath was used in the first part of the statement

Complete statement used was : 

 count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[number(concat(substring(string(@StartDate), 1, 4), substring(string(@StartDate), 6, 2), substring(string(@StartDate), 9, 2))) > xdXDocument:get-DOM()/dfs:myFields/my:FormFields/my:CountFields/my:AnotherDate and @ID = current() and @NotesSun = "A"])