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 ::..
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