Clear and then disable controls in infopath

This is simple for reading, but difficult to implement in infopath forms. To disable controls we have to use “Conditional Formatting” option of a control. To clear content of a control then we have to use “Rules” option of a control. But, the sequence of execution of these two causes some problems. Take below scenario.
I have a table and each row the first column contains a checkbox and all other 4 columns are having date field, textbox, drop down and date field respectively. Now, the logic should be this.

1. The default state of checkbox is selected.
2. When user enter some data in all other 4 fields and now he thought the row should not allowed to enter data then he deselect the checkbox. When user deselects then the first thing should happen is clear the content in all the 4 controls and then all controls should be disabled.
3. When again user select the checkbox then the controls should be enabled.

This is what to be happen and the first trail when I tried to implement I did below things.

1. On all other 4 controls I added a rule that when checkbox selected state is false then set the current field to empty.
2. And then I added a conditional formatting on them to disable when the checkbox state is unchecked.

I did deploy them to SharePoint and when I tested, surprisingly they are not working as expected. The controls are going to disable state but not clearing content in them when I deselect the checkbox. And researched and found that the Rules are not executing. [Didn’t find the reason yet.] And then thought about for alternatives and came up with reverse way. That is, applying rule on the checkbox instead of other controls.
Earlier, I have applied rules on the each and every individual control which needs to be cleared based on checkbox state – Which doesn’t work. Now, I have applied the same logic but applied rule on the checkbox [As there are 4 controls, 4 times I have added set field to empty] and which is working like charm.
So the conclusion I want to tell to you is, when I apply conditional formatting and then rules something is causing problems in the sequence of execution. So, depends on what your control, rules needs to be executed apply the rules on that control only. That should work perfect.

Date validation in Infopath forms

In SharePoint infopath forms I need to implement one thing which was a bit tricky as it is not the default supported by infopath forms. The requirement is like this: The date fields on form should not allow date plus/minus 5 years from current server date. For this, so many people recommended to implement validation events in c#. But, my form contains 40+ date fields. That is not possible to implement events for each control. But, when I looked into all option there is an option called “Data Validation”. But, there is no direct option available to implement this requirement.
After thought about sometime got wonderful ideas. Below is the implementation finally came up with.

1. Select the date control and right click.
2. Select the option “data validation”.
3. Click on Add to add a new validation.
4. From the window, the first thing we have to check is “Whether the field is blank or not.” Because once it is not blank then only we go further.
5. And next thing is, validate the expression. User entered date is plus/minus 5 years from current date.

If you observe the first condition I have used is an expression as there is no direct way to validate the date according to my requirement.
Expression we have to use: msxsl:string-compare(., xdDate:AddDays(xdDate:Today(), 1825)) > 0 or msxsl:string-compare(., xdDate:AddDays(xdDate:Today(), -1825)) < 0
And second condition is just the field is not blank then only display “Invalid date” error on screen.
Note: Remember, the expression is very simple that adding/subtracting the 1825 days [5 years] to current date. Depends on your requirement please change the value accordingly.
The last reason why I have used the expression instead of the simple statements is the logical operators. The condition should be validated for this requirement is “((selected date > today + 5 years OR selected date < today – 5years) AND field cannot be blank)”. The brackets are very important, the sequence of executing conditions and logical operators are important. So, to achieve this in infopath forms the only way is implementing expressions. Then only it treats it as the way we want. Enjoy the complex and nice series of posts on infopath and sharepoint coding.

UDCX files in Sharepoint Infopath and dynamic queries

Confused? Can we use dynamic queries in the infopath and UDCX combination? UDCX are meant for not writing any code to get data from database and to show it up the retrieved data on the infopath form. They allow only STATIC queries. Just straight SQL or SPROC names  and parameters to it. But, I got a requirement where I need to pass some dynamic values to the query/SPROC at runtime and gets the data and loads the data on the form. Can I achieve that with same UDCX connections and same architecture?
YES, There is a way to do this. The things to note here are:

1. Using UDCX connections, we have saved all the connection data, query data and credentials data on a single file in SharePoint list/library.
2. Query should be correct and executing without any issues [Otherwise infopath cannot download the resulted schema]. For example, you want to get the user by user name then you may created SPROC with name "GetUserByUserName" and in your UDCX file you give the query tag as "<udc:Query>EXEC "dbo.GetUserByUserName" 'DEFAULT'</udc:Query>". We know there is no record in database with the name "DEFAULT". But, this is what we have to give as default query. [This is what we will change in the c# code dynamically.]
3. Read this connection, query in the infopath c# code.
4. Change the query information in which way you want in code.
5. Execute the query.
6. Reset the UDCX connection information back to original.
7. It will automatically refresh the control data depends on the latest result set after we executed from the C# logic.

So, I believe you got complete picture of what we are going to do. This is very simple but difficult to get the idea. With this implementation I solved the big problems what I had.
To execute the below code I am assuming there is a UDCX connection file available in a SharePoint library and your infopath form is allowing c# code.

//Get the connection details by connection name 
AdoQueryConnection adoConnection = (AdoQueryConnection)DataConnections["Get_User_Details"];
if (adoConnection != null)
{
string orgCommand = adoConnection.Command; //To read original command 
int index = orgCommand.IndexOf("DEFAULT"); //Find where the keyword "DEFAULT" in the command string 
string SPROC = string.Empty;
if (index > -1)
{
try
{
SPROC = orgCommand.Substring(0, index); //Get only the SPROC name. 
adoConnection.Command = string.Format(SPROC + userName + "'"); //Append user name to the query. 
adoConnection.Execute(); //Execute the final query. This is what the command which contains actual parameter value instead of DEFAULT string. 
}
catch { }
finally
{
adoConnection.Command = orgCommand; //Should not forget to write this. We have to do this. 
}
}
}

Things to note:

1. The connection name "Get_User_Details" is the connection name from infopath form [Managed Data Connections option].
2. As we are reading from existing UDCX connection file, we are not hard-coded any of the connection strings or queries.
3. userName is the string variable which holds the user name which comes at run time. You have to write some logic to get these in your code.
4. Read the query and replace the dummy parameter values with the original values.
5. Execute the connection.
6. In finally block, we are resetting the command back to original.

That's it!!! If you are binding this information to the textbox then you should do one final thing as shown in below figure.

The checkbox in above figure "Update this value when the result of formula is recalculated" and applies to only if you use the formula.

We are done. The data now comes from database and passed the parameters to database dynamically, used UDCX connection file and did not hard-coded any of the connection, query information in code. Very clean right?

Hope you understood it well and liked it.

This session has exceeded the amount of allowable resources - Infopath form error

If you are working with infopath forms and your form is really big [I mean, having lot of controls and with so many actions] then you should get this error at some point. Most of the developers did not get it as it will come very rarely.

Error details:
"This session has exceeded the amount of allowable resources."

Event Viewer details:

Event Type:      Error
Event Source:    Office SharePoint Server
Event Category:  Forms Services Runtime
Event ID:        5737
Description:     
Number of form actions 208, has exceeded 200, the maximum allowable value 

per request. This value is configurable and can be changed by the administrator.

So, when we see the log description, it is saying that the form actions is beyond the SharePoint default form actions allowed. SharePoint by default allows 200 form actions per postback of a form. But, here the form I have used is using more than 200. My case it is 208. So, it will not allow to save or submit the form and throw the above error.

The assembly from form template conflicts with a same named assembly in form template

This is the exception which was coming when I tried to deploy the infopath forms to SharePoint environment.

This is the background of the problem. I have infopath project created in visual studio and infopath has 3 views. According to my requirement I have to publish each view as a single infopath form. So totally 3 infopath forms as output. The infopath form has form code in c#. So, we have to deploy dll as well. But, as one version of infopath is already in production, I have to move the new version to production. But, according to some dependency problems, I have to keep the dll of the infopath project as same version as production. So, what I did was opening the AssemblyInfo.cs and changed the assembly version to hard coded like 1.0.3098.12321. There it started the problem. When I tried to deploy the infopath forms below is the exception it was coming.

The assembly infopathproject.dll from form template someform.xsn conflicts with a same named assembly in form template another.xsn

So, after research around 1-2 hours, I found that the three xsn forms are pointing the same dll [I mean along with version]. So, infopath forms are having same dll reference and leads the conflict.

The resolution for this problem is, reverting back the assembly information back to 1.0.* solved the problem. Now infopath forms are pointing to same dll but with different versions. So, problem resolved.

So, if you have the same requirement like same dll using for different infopath forms then, please don't forget to make auto generate versioning for the assembly like 1.0.*.

Hope this helps to solve the problem.

Setting views dynamically in infopath form through code

When working with infopath forms, there are lot of things we have to know. This is not very easy to implement, publish and using it. So, we have to know complete details about infopath in all aspects. I had a requirement in infopath, while loading depends on some scenario I have to switch the view. While form is loading I have logic written to know what view I have to set. But, once I got the view name from logic, how to set that to infopath form? Below is the simple code which works for that requirement.

e.SetDefaultView("PBB"); 

the "e" used in this code is the LoadingEventArgs object in the form event. It has the method named SetDefaultView(), Which takes the view name as the parameter. So, once you set it, it will apply the view to the infopath and display the view on the form.

Hope this helps to understand better the infopath and code.

Infopath form cannot save the following form - Form is Read Only

I have created the infopath project in Visual Studio and deploying into SharePoint environment. After some days, I got to change the infopath form to match the new requirement and redeploy to the SharePoint environment. I have opened the Visual Studio and spent half an hour to edit all the changes and tried to save the form. But, while saving it was started giving me the alert message that "The infopath form cannot save the following form: Template Name is ready only." I did not understand and check all file properties of the manifest.xsf and unchecked the checkbox read only. Still it was giving me the same error message. The error message was completely confused me and no any other clue. So, started searching in internet for the solution and after searched about an hour, found the information. If you have opened the infopath form, then close it. [No other way, you will lose all changes you have made.]

Resolution:

1. Open the manifest.xsf in notepad.
2. PublishUrl: Search for "publishurl". I believe, this pointed to the old location other than what actual the form is submitted to. Make it empty [No problem].
3. runtimeCompatibilityURL: in the notepad, search again for "runtimeCompatibilityURL". Now change its value to "http://sharepointserver/_vti_bin/FormsServices.asmx". 
4. If you are using the source control like VSS, Source vault, TFS or whatever then you have to do below things.

• Open Visual Studio and checkout all the files in the Infopath Form Template folder.
• Once all checked out, then close the Visual Studio and reopen.

Note: replace sharepointserver in above url with your sharepoint server name.

Check for more information here. MSDN
That's it. Now, you have to reopen the file and do whatever changes you have to do. Enjoy.

Role of ItemMetadata.xml in Infopath forms [SharePoint workflow].

Introduction:

ItemMetadata.xml is the file used to store/transfer the data from one Infopath to another. The name of the file is case - sensitive.

For example, in a sharepoint workflow, there are 10 steps, in each step we are using infopath and we want send some information from one to the next infopath form, the information won't save any where. we need to tell explicitly to infopath to store in xml file. that is nothing but ItemMetadata.xml.

It acts as the secondary data source to the infopath.

To compensate for the possibility of schema differences between the task data and the form it only stores the schema instead of data.

Example schema is

<z:row xmlns:z="#RowsetSchema" ows_fieldname=""/>

Note: Every field name should be prefixed with ows

In programming, we can retrieve the data from info path using the property called ExtendedProperties.

afterProperties.ExtendedProperties["filedName"].ToString();