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

Tuesday 18 January 2011

Repeating a SQL row based on a value in a different column

There are times that we get requirements such as populating and duplicate SQL rows, based on a value, on another column. E.g.: In an inventory system when items are received those details will be saved in the following format (ItemDetails) :

screen_01

And we are asked to create a GUI for end user to enter ‘Serial Numbers’ for each item. And we have to repeat the above mentioned item codes number of times which equals to the ‘ItemQty’. Of course we can achieve that using a SQL cursor or iterate using C# coding. But following example I will show how to do it using SQL.

The task would have been very simple if we would have a another table with a structure similar to this: (TempTable)

screen_02

So when the two table are joined ‘ItemDetails’ will repeat according to the row count of the ‘TempTable’. But it is not very practical, and it will result in duplicating data, which will grow your database un-necessary when time goes.

But instead we can use on single table which contains a series of numbers. These numbers will start from ‘1’. And the end should be the maximum quantity which an Item can have. For this example I will take ‘10’ as the maximum value. And that table should have the following structure.

screen_03

Use the following T-SQL statement to create the table:

CREATE TABLE [IntermediateTable](
[MaxQty] [int] NULL
) ON [PRIMARY]


For this example I have inserted up to 20. But in a real world scenario it may be required to enter values (More than 1000). In a similar situation you can use the following T-SQL statement to insert values.


insert into IntermediateTable
select
thousand.number*1000 +
hundred.number*100 +
ten.number*10 +
one.number
from(
select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) one
cross join (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) ten
cross join (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) hundred
cross join (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) thousand
where (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number) > 0 and (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number) <= 2000
order by (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number)



** Please Note : Above statement will insert values from ‘1’ to ‘2000’. But removing the where condition will insert values from ‘0’ to ‘10000’.


And using the following T-SQL statement we can join the table and produce the required result.


select A.*
from ItemDetails as A
join IntermediateTable as B on B.MaxQty <= A.ItemQty
where A.BatchNo = 'B1'


screen_04

Designing a windows form where size exceeds your screen’s resolution

Some times we need to design forms in our  applications, beyond the size of our development machines screen resolution. But the worse case is, our development environment does not allow us to resize our forms beyond our screen resolution. (Screen resolution – Borders to be exact).

One method is to do the development on the client resolution. But it’s not going to work all the time, if we have to do the development and send it across to a different physical location.

The other method is to insert a ‘Panel’ to the form, change it size to the one at the clients end. The one I am working at has the resolution of ‘1440 X 900’. But I want to make a form which fits for ‘1920 X 1200’. But I will change the Panel’s size to a bit less than ‘1920 X 1200’. Because I have to leave space for the borders and the scroll bars. So I would make it somewhere around ‘1870 X 1150’

And change the following form properties also :

  • AutoScaleMode = None
  • AutoScroll = True
  • AutoSize = True
  • WindowState = Maximized

 

And you can note that there are two scroll bars available at the design time, which you can scroll and place controls beyond your screens resolution. And when run on clients environment, the form will  maximize and since the panel is bit smaller than the actual screen size, the scroll bars will not be visible.

screen_01

Tuesday 28 December 2010

Fixed - One or more ActiveX controls could not be displayed because either:...– When opening windows services screen

 

Sometimes when opening windows services screen (or by running ‘services.msc’ from the command prompt) you might receive the following error screen:

screen_shot_1

And gives you an empty service screen.

screen_shot_2

This can be caused due to many reasons. But most of the time it can be fixed by simply editing a registry entry.

Open the registry editor (or run ‘regedit’ from the command prompt) and browse to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones

And if you see a pseudo-graphic number before zone 0 like shown below, it could be the issue.

screen_shot_3

Delete this. How ever if that doesn’t fix the issue, or if the entry does not exist, select zone ‘0’. And change the value of ‘1200’ to ‘0’ or to ‘1’ (Usually the value will be ‘3’). The values 0,1 and 3 are associated with the following behavior:

  • 0 = Enabled, or the action is permitted
  • 1 = Prompt
  • 3 = Disabled, or the action is not permitted

screen_shot_4

And if this doesn’t work try to reset the Internet Explorer. To do that follow these steps:

  1. Click on Start & Run.
  2. Type ‘inetcpl.cpl’ & click ok
  3. Click on Advance Tab & click on Reset (Make sure to put the check mark on the delete the personal setting for IE8) then again reset.
  4. Click close.

Thursday 9 December 2010

Constructor Chaining in C#

 

What’s ‘Constructor Chaining’ ?

Constructor Chaining is an approach where a constructor calls another constructor in the same or base class.

This is very handy when we have a class that defines multiple constructors. Assume we are developing a class call ‘Student’. And this class consist of 3 constructors. On each constructer we have to validate the students id and categorize him/her. So if we do not use the constructor chaining approach, it would be something similar to the one shown below:

screen_01

Even the above approach solve our problem, it duplicate our code. (We are assigning a value to ‘_id’ in all constructors). This where constructor chaining is very useful. It will eliminate this problem. This time we only assign values only in the constructor which consist most number of parameters. And we call that constructor, when other two constructers are called.

class Student {
string _studentType = "";
string _id = "";
string _fName = "";
string _lName = "";

public Student(string id)
: this(id, "", "") {

}

public Student(string id, string fName)
: this(id, fName, "") {

}

public Student(string id, string fName, string lName) {
//Validate logic.....
_studentType = "<student_type>";

_id = id;
_fName = fName;
_lName = lName;
}
}


**Please note: If you do not specify anything [In this example we used ‘this’), it will be consider that we are calling the constructor on the base class. And it’s similar to using ‘: base(…)’]

Friday 3 December 2010

Show properties of a class on Property Grid

When developing user controls, usually we use Booleans, Integers, Strings, etc.. as data types of the attributes of the control. But sometimes, we have to use structures or other classes as attributes. So when we use those, we should be able to change or browse the attributes of those structures or classes.

Open visual studio IDE and create a new Windows Forms Application type project.

Add a new class and name it as ‘MyCustomClass’. And define two properties. One integer and a string type.

public class MyCustomClass {

public int MyIntProperty { get; set; }
public string MyStringProperty { get; set; }

public override string ToString() {
return "...";
}
}







**Please note that I have overridden the ‘ToString’ method. Because this what will be shown on the property grid when the properties are collapsed.



Now add a new user control to the project and name it as ‘MyCutomUsercontrol’. And create three properties. One integer, string and MyCustomClass. And use ‘[TypeConverter(typeof(ExpandableObjectConverter))]’ attribute on the third property. The syntax should be:






public partial class MyCutomUsercontrol : UserControl {
public MyCutomUsercontrol() {
InitializeComponent();
}

private MyCustomClass _MyCustomClass = new MyCustomClass();

public int Property1 { get; set; }
public string Property2 { get; set; }

[TypeConverter(typeof(ExpandableObjectConverter))]
[EditorBrowsable(EditorBrowsableState.Always)]
public MyCustomClass Property3 {
get {
return _MyCustomClass;
}
set {
_MyCustomClass = value;
}
}

}





 



Now build the solution. And add it a windows form. And on the property grid you can see your custom controls properties.



screen_1

Wednesday 1 December 2010

Calling Methods using Named Parameters using C# 4.0

Another new feature added to .Net 4.0 is to call methods using named parameters. This is very handy when calling a method which has optional arguments (You can use this to call other methods which consist of non optional parameters too). We’ll consider the following method.

static void SampleMethod(string ar1, 
string ar2 = "",
string ar3 = "",
string ar4 = "",
string ar5 = "",
string ar6 = "") {



//Some logic..
Console.WriteLine(ar6);
}



And assume that we only need to pass a value only to the last parameter. If the named parameter method was not there, the way to call the method would be something like this:


SampleMethod("message", "", "", "", "", "Hello World!");



But instead we can call the method using the following syntax.


SampleMethod(
ar1: "message",
ar6: "Hello World!");



And you will get the following output:


screen_1

Defining Optional Parameters for Methods using C# 4.0

The ability to create methods or functions with optional parameters/arguments was there in VB 6.0. How ever it was taken away when .Net came into action. And it was not there in earlier versions. But now it's available again in framework version 4.0.

Assume you have a method called "Greeting" which defines a single optional parameter:

static void Greeting(string message, string user = "Guest") {
Console.WriteLine(message + ", {0}",user);
}


 


So when the method is called by passing a value only to the first parameter, the default value of "Guest" will be assigned to the second argument. And you will get the following output.


Greeting("Hello");

screen_1

 


And when the method is called passing parameters to both arguments, you will get the following output.


Greeting("Hello","John");

screen_2

Thursday 25 November 2010

Disabling tab pages on a Tab Control

Sometimes there can be a requirement, which we have to prevent users from accessing certain tab pages on a tab control. But there’s no straight forward method provided on the Visual Studio IDE (2005,2008 or 2010). But we can do that easily.

Add a tab control to your windows application. (tabControl1)

Add few tabs and few controls to each tab.

img_01

Now we’ll disable one tab using the following code on the form’s load event:

tabControl1.TabPages[2].Enabled = false;


** Please note : Though it does not list the ‘Enabled’ property on intellisense, it’s available.


Now if you run the application you can see, that all the controls in tab page 3 are dissabled.


img_02




But if we want to prevent from users accessing that tab, we can use this coding on tab controls selecting event (Not the page, but the control’s)


private void tabControl1_Selecting(object sender, TabControlCancelEventArgs e)
{
if (!e.TabPage.Enabled)
{
e.Cancel = true;
}
}


Now if you run the application and try to select that tab page, you will not be able to do so..