How to create cascading dropdown boxes in InfoPath

Learn how to use SharePoint lists as data connections in InfoPath and how to use that as cascading drop down lists in InfoPath. Often when I build forms I find it very useful to have SharePoint lists as lookup information, both because it is easy to setup and also the users can change the information without having to design the form in InfoPath and also because it is the easiest way to create cascading drop down list boxes. This video shows how to achieve this. Thanks for watching!

QuickTip: Display multiple values based on selection in InfoPath

I have a form with a selection box where the user selects a Role, and based on that Role I set a multiselect field with the predefined values, in this case Applications. What I wanted to do, was to have the checkboxes selected by default OR not display the checkboxes at all. Because this should be a list of all applications that are included when you select a specific role, so the user should not be able to check or uncheck any boxes. I select a role in the left drop down box, and all the values are shown to the right in a Multiselect box:

It does not seem possible to have all values selected in the Multiselect field, only the top value can be selected and that is not what I want. It’s either ALL values or NO checkboxes. Well simple enough I chose the “ListBox” instead, that rolls up all values without checkboxes. The only downside is that you can select one or more values but I have not published the values in the fields anywhere and I am not saving them so it is fine. So the left drop down box selects a role and the listbox shows all values separated by a new line:

InfoPath forms do not work with replication – Updated!

10 April update: That last version 5.1.7322.8 did not help solving this issue with InfoPath. It seems like the problem with templates has to do with forms that uses Data Connections. There is a new version 6 out that I have not installed yet, will come back with an update after that.

5 March: Awesome news! I got a mail from the vendor that the newest version will have a hotfix that will correct this issue. It will be released in a few weeks. Look forward to that, and will of course update this blog post if things go well.

This post is about enabling replication between servers using a replication software and the problems related to that. It works well for exchanging content, but when it comes to InfoPath there are some real problems. This is not yet solved so there will probably be a part 2 of this post.

I have published a lot of web forms (InfoPath forms services) on the intranet and when they start replicating they run into the problems listed below. Same thing happens to a list that you have customized, the customised form is lost so it looks like a regular list again. The replication vendor has no solution so far.

The error will run in circles because if you make an update on one server, that will replicate to the others and the errors will just keep on coming back. Here are the errors and why they happen.

Advanced settings are not replicated
The first time you set up replication on an InfoPath form and try to open a form on the site, it will load in InfoPath and not in the browser. That is because the “Advanced settings” of the library are not replicated. You need to go in to the library and set the “Open in browser” again and then you also have to open your form template and republish it:

Data connections are lost
When you have done this change, the form gives you a new message. Now the data connections can’t be accessed, since they have lost the connection to the secondary data sources:
 

So you have to go in and add those back again in the IP form template:

By this time, your form has start replicating and now all other target servers has gotten those same errors. So this will just keep on going around in circles.

So what I did was to stop replicating the “Content types” and it works HALFWAY. Because then another problem came up…

The xsn version of the form template changes
Of course when you have done a change locally on a template it will get a new version number. So now all versions of this template is not in sync. And the result of that is that the users get the below error message. If they click “OK” the form loads but still annoying:

 
I have the setting on my IP forms to “Automatically upgrade form version”, which is the default setting. If the user clicks on OK the form loads, but I don’t want that message! I compared versions of the form and on the source server we have version 1.0.0.1701 and on the target server we have 1.0.0.1709 and that is of course because I had to open the form and add the data sources and all the other steps I wrote about before, that were lost while replicating:

So I changed the version to be the same on both servers. And that helped. So now that message was gone. But this is only a temporary help, not a SOLUTION! Once a form is updated again, the problems will all come back. So I will test publishing a new template that has “Do nothing” instead of “Automatic update version” to see if that can help.

My hope now is that the vendor will deliver a solution for this soon, otherwise this is turning into a big EPIC FAIL.

Substring in InfoPath

To get a value AFTER a character
substring-after(FieldName, “;”)
This will return all text after the character ;


To get a value BEFORE a character
 substring-after(FieldName, “;”)
This will return all text before the character ;


Substring and Concatenate

I often use Concatenate to join text from two fields. For example, the Date function now() has a “T” between Date and Time. I usually create a new field where I use substring and concatenate to replace the T with a space. Formula:

concat(substring-before(DateTimeField; “T”), ” “,substring-after(DateTimeField; “T”))

This gives me the date and time in one single field with a space between.

 

Working with Date formats in InfoPath

To extract the day number of a date

number(substring(DateField, 9, 2))


To extract the month number of a date
number(substring(DateField, 6, 2))


To extract the year number of a date
number(substring(DateField, 1, 4))


Calculate hours

addSeconds(now(), 7200)


Get the month name from a date (and time) field

=TEXT([DateField];”mmmm”)


Get three characters of the month name
=TEXT([DateField];”mmm”)


Sort or group a view by month name

To sort, add the month number in front
=TEXT(month([DateField]); “00”)&” “&TEXT([DateField];”mmmm”)

Add two calculated columns that will take the start and the end of the month.

“Start of Month”  =DATE(YEAR([DateField]), MONTH([DateField]), 1)

“End of Month”    =DATE(YEAR([DateField]), MONTH([DateField])+1,1)-1

Add a filter in the view:
“Start of Month” is less than or equal to [Today]
 AND
“End of Month” is greater than or equal to [Today]

Form has been closed

This post is more for my own memory, since I tend to forget how I solved it when I get this message “Form has been closed” when submitting browser forms in InfoPath 🙂

Scenario is the following:
You have a mail view that you wish to mail instead of the default Edit form, so that you can design it better for mail clients and also remove any unrelevant information and buttons etc. So I have two views in my InfoPath form, and my Submit button should change to the Mail view when the form is submitted.
I add a Submit button and some rules. If you add the rules in the wrong order and you submit the form, you will get this message in the browser:

And you are not redirected back into the form library, which is what you want. So in order to get back into the form library after Submit, then add the rules in the following order on your Submit button:

1) Submit to library
2) Switch to your Mail view and Submit to Email
3) Switch back to View1 (default view) and Close form

 

Stop running this script

If you connect a secondary data source to your InfoPath form, and connect to a udcx file which contains a large amount of data: then you might get this error message inside the form:

Very irritating! I did not find any solution to it, only that you can disable the script debugger in IE, but hey – we are talking clients here! I will not be able to edit IE settings on my users computers right =)

The solution to this issue was to import the sql table to a SharePoint list, and then lookup against that list. Error message? GONE! 🙂

Filter on secondary data sources in InfoPath

I have built many forms in InfoPath 2007 and you always find new interesting issues to deal with =)

When you think you have found a brilliant solution, then you can be sure that there is an obstacle when it comes to web enabled forms. So it works with filter on data sources.

I wanted to drag and drop a secondary data source to my form, but I wanted it to show only values that matched a field value in my form. Let me clear: only values in the data source that belonged to the current user should be displayed in the repeating table. Easy, I thought and clicked the “Value” button and then the “Filter data…”. Got this error message:

Hmm after hours of trixing with setting the field from other fields and so on, I disabled the web enabled layout. Now you may press the “Filter data…” button and set your filter. Web enable the form again, publish it and just press the “Ignore error messages” and continue with the publishing…. It worked!

So that error message is BS. I have saved the form, and all data remains in the form.

Display a list view in InfoPath

If you want to display all entries from a SharePoint list inside your InfoPath form, browser enabled or not, then do the following.

You have a list in SharePoint already, right..
Add a data connection to that list the usual way in InfoPath
Open the “Data source” in the right task pane and select the list (it is listed as a Secondary):

Right click it and select “Repeating table” and drag and drop it to your form

The list is displayed with all entries: