SharePoint 2010 & InfoPath: Lookup with formatted dates is possible

I am sure this post is not really new, but i would like to share some thoughts with you. I had to create a InfoPath fom which contains a lookup / dropdown to another SharePoint list – a calendar. In the lookup there should be all the starting times of the calendar which are greater than today. No Problem with the filtering possibility in InfoPath.

I created a dataconnection to the calendar list, then i added a dropdown to my form and choose the dataconnection as source. I set the filter to starting date (Event Date) to greater than today and the result does not make me smile:

Can anyone tell me why the output is so unpretty? I expected to get a format like 17.05.2012 13:00:00. Anyway why can’t i tell the dropdown how to format the values? I can’t show this my client and tell him “Well that’s SharePoint o InfoPath”. So i tried it several things till i came to the desired solution.

The source is the source

That’s true for this. At first you have to create a calculated column in your calendar list with this formular:

=TEXT([ColumnName],”dd.mm.yyy hh:mm”)

Remember that english / german might need other formulas like a semicolon instead of a commata or at the date format.

After that i updated my data connection in the form and added the new column. Now i can choose that the new column should be the display column in my lookup and it will display the values like i wanted:

Great, it is possible with a little trick with standard features, but it surprised me that i had to use this workaround.

..:: I LIKE SHAREPOINT ::..

The article or information provided here represents completely my own personal view & thought. It is recommended to test the content or scripts of the site in the lab, before making use in the production environment & use it completely at your own risk. The articles, scripts, suggestions or tricks published on the site are provided AS-IS with no warranties or guarantees and confers no rights.

Karsten Pohnke About Karsten Pohnke
He is Consultant for SharePoint Solutions for collaboration, communication and business processes. He provides his customers applications based on standard features as well as development or combining the power of several microsoft tools like Dynamics CRM. In his free time he tries to collect tipps and worthy experience in this blog.

2 comments on “SharePoint 2010 & InfoPath: Lookup with formatted dates is possible

  1. Karsten,

    Thank you, I was struggling with that exact date lookup formatting problem and your solution worked great. Unfortunately, now that the lookup is in text format I can’t filter for future dates. In the article you indicated you’d done that. By any chance have you posted that solution somewhere?

    Thanks in advance. And great job with your Blog, it’s very helpful.

    John

    • Hey John,
      i already answered your mail and didn’t see that you posted a comment, so let me answer here as well. My suggestion was to use the computed field in the form and each time the value changed you can set a date column by rule with the selected date. This date column you will display in the listview. This gives you the possibility to use a normal date field with sorting and filtering in the listview and a nice field in the form.

      Let me know if this helped you out.

      Kind regards
      Karsten

Submit comment

Allowed HTML tags: <a href="http://google.com">google</a> <strong>bold</strong> <em>emphasized</em> <code>code</code> <blockquote>
quote
</blockquote>

Please fill in the captcha: * Time limit is exhausted. Please reload CAPTCHA.