If you have used InfoPath in your development, you might have come across the issue of filtering data using a date range, after the data is received by the form. To illustrate the issue, I will use a sample InfoPath form.
Add two date pickers and name them ‘fldFrom
’ and ‘fldTo
’:
Add your data source to the form. To this sample, I will use a SharePoint list which contains ID, Created Date and the Version. Drag the data source to the InfoPath form and you will be prompted with three options:
Select the ‘Repeating Table’ option. And if you preview the form, you can see a similar view shown below:
Now, we will add a rule to the repeating table so that the data will be filtered according to a given date range.
Select the data source and click on the ‘Manage Rules’ on the home tab.
Add a formatting rule:
And enter the following to the condition:
Click ok and select the ‘Hide this control’:
And if you preview your form and give the desired date range for the From and To dates, you can easily notice that the filtering of data is not what you have expected. In this example, I want to list data, which the created date is between ‘01-Jan-2011
’ and ‘14-Jan-2011
’. But even if I assign those values to the date pickers, I will not get data which was created on ‘14-Jan-2011
’.
**Please note: In the InfoPath form that I have used, the date format is ‘M/D/YYYY’
So prevent this issue, in the Rule we created earlier, we will use an expression. The logic of the expression is to convert the dates into a number, and compare them.
E.g.: We convert the date to a number format similar to ‘YYYYMMDD’ if the date is ‘01/01/2011
’ after the conversion it will be ‘20110101’.
First, change the dates formats to the following format [‘English (South Africa) and ‘YYYY/MM/DD’].
And change the Condition we added earlier to the following expressions:
The expressions should be:
number(concat(substring(d:Created, 1, 4), substring(d:Created, 6, 2),
substring(d:Created, 9, 2))) < number(concat(substring(xdXDocument:get-DOM()
/my:myFields/my:fldFrom, 1, 4), substring(xdXDocument:get-DOM()/my:myFields/my:fldFrom,
6, 2), substring(xdXDocument:get-DOM()/my:myFields/my:fldFrom, 9, 2)))
number(concat(substring(d:Created, 1, 4), substring(d:Created, 6, 2),
substring(d:Created, 9, 2))) > number(concat(substring(xdXDocument:get-DOM()
/my:myFields/my:fldTo, 1, 4), substring(xdXDocument:get-DOM()/my:myFields/my:fldTo,
6, 2), substring(xdXDocument:get-DOM()/my:myFields/my:fldTo, 9, 2)))
And when you preview the form, initially all the records will be displayed, since there are no default values set on two date pickers. But when I change the dates, the range I set earlier, I will get the desired result shown below:
Hope this might be useful to you.
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.
My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.
My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.
Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports