Showing posts with label InfoPath 2010. Show all posts
Showing posts with label InfoPath 2010. Show all posts

Tuesday 19 April 2011

How to display distinct data from a duplicated data source in an InfoPath form

Sometimes we have to get data to our InfoPath forms from various sources. (From SQL Table, using a Web Service, from a SharePoint list etc..)

But most of the it’s really annoying, if you do not have the option of filtering them directly from the backend (so all the duplicated data will be displayed).

For this example I will be using the following SQL table with duplicated data.

img_scr_001

So when I connect and retrieve data to an InfoPath form. I will get the following result. (I am using an InfoPath list box to display the data). And if you note, you can see various duplicated values are there in the list.

img_scr_003

And I have used the following connection to retrieve data.

img_scr_002

We can eliminate the duplicated values by applying the following filter to the list box. To do that follow these steps:

  • Right click on the list box and select ‘List Box Properties’

img_scr_004

Click on the ‘Select XPath’ button in front of Entries field.

img_scr_005

Click on the filter button on the next screen.

img_scr_006

And add the following filter:

not(. = ../preceding::<XPath>)


‘<XPath>’ is the data source entry of your list box control. To find the XPath, right click that data field and select ‘Copy XPath’.


In my example the XPath is:


/dfs:myFields/dfs:dataFields/d:temp_table/@Name


But since I am referring to it from ‘/dfs:myFields/dfs:dataFields/’ branch, it should be omitted and only ‘d:temp_table/@Name’ is required.


So the filter should be :


not(. = ../preceding::d:temp_table/@Name)

img_scr_007

Click ‘OK’ on all dialogs and close them. And when you preview your form, you can see only the distinct values.


img_scr_008









Hope this will help you.. Smile

Thursday 27 January 2011

How to filter data correctly on a repeating table in an InfoPath form using a date range

If you have used InfoPath in your developments, you might have come across with the issue of filtering data using a date range, after the data is received to the form. To illustrate the issue I will use a sample InfoPath form.

Add two date pickers and name them ‘fldFrom’ and ‘fldTo’

screen_01

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 prompt with three options

screen_02

Select the ‘Repeating Table’ option. And if you preview the form you can see a similar view shown below.

screen_03

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.

screen_06

Add a formatting rule.

screen_07

And enter following to the condition.

screen_08

Click ok and select the ‘Hide this control’

screen_10

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 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’

screen_11

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’]

screen_12

And change the Condition we added earlier to the following expressions:

screen_13

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.


screen_14


Hope this might be useful to you.. Smile