General

Re: Conditional count() using dates

L Mac — Mon, 24 Feb 2014 07:57:15 GMT

Jimmy,

Thanks. I'll insert that when they ask me for the next set of changes to that form.

L Mac

Re: Conditional count() using dates

Jimmy — Sun, 23 Feb 2014 16:39:00 GMT

Hi L Mac,

Sorry that nobody responded to your question sooner, but once you've got all your dates into yyyy-MM-dd format, the formula to use is much simpler than what you've got there. It's simply the following:

count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[translate(@StartDate, 'T:-', '') > translate(xdDate:AddDays(xdDate:Today(), -365), 'T:-')]

That should do it!

Re: Conditional count() using dates

L Mac — Fri, 21 Feb 2014 23:19:34 GMT

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"])

Re: Conditional count() using dates

L Mac — Mon, 27 Jan 2014 08:50:19 GMT

I think that I have found the issue...... I have one date in Format : dd/mm/yyyy and the other is yyyy-mm-dd

Now that I have that part straight in my head can anyone help with the concat( substring-before and substring-after) to reverse the first date??

Then I can translate() to get a number that might return a valid count.

Conditional count() using dates

L Mac — Sun, 26 Jan 2014 06:06:25 GMT

I am having issues with counting data returned from an Access DB Table using InfoPath 2010.

The data source (Timecards) is secondary. I need to filter out all data greater than 12 months old.

So far I have xPath as follows:

count(xdXDocument:GetDOM("Timecards")/dfs:myFields/dfs:dataFields/d:Timecards[@NotesMon = "A" and @ID = current() and substring-before(@StartDate, " ") > xdDate:AddDays(xdDate:Today(), -365)])

The first part returns a count as expected, the last part (BOLD) is what I am having issues with. It draws no errors, but returns a value of zero

Please help.