No one has responded to this discussion for at least a year, so this information may be out of date. If you're looking for information about this topic, please search for a more recent discussion or post a new question.

Less than or equal to Formatting Rule not working in InfoPath 2010

  • 2 Followers
  • 2 Replies |
  • This post has 1 verified answer |
Answered (Verified) This question is answered

I am displaying data from a SharePoint List in an InfoPath Form. I am using a formatting rule to filter the data by Date. I have two fields: Start Date and End Date that specify the filter dates. The formatting rule basically hides the data that you don't want to see which leaves the data you want visible. Somewhat of a reverse logic, but it works.  The rule is when Start Date >= to the Date in the data and End Date is <= the Date in the data. This filters the data as expected, but the End Date filters the data that has the same Date as the End Date. You have to go one day past the Date with the End Date to get all the days you want included. There is no problem with the Start Date, just the End Date. IE. If you wanted Monday 5/7/2012  through Friday 5/11/2012, you would have to select Saturday 5/12/2012 as your end date to get the desired date range data. I have tried using just the less than control, but that creates other issues and doesn't fix the problem. Is this a bug or am I just missing the logic? Any ideas?

 

IbisDev

  • Post Points: 20
Verified Answer
  • Hi Jason,

    Thanks for the response. I did get an answer from Hilary Stoupa at the InfoPathDev.com forum. They are a great resource for InfoPath issues.

    To be brief: When you import a date field from SharePoint, even if you have the column set up as Date Only, the data that ends up in InfoPath has the time field appended to it. The XML looks like 20120511T00:00:00. Hillary showed me how to see the actual XML to determine this. Even if you have formatting that displays the Date Only, the time is still there in the data. Naturally when you try to use the data in a rule and control it with a Date value that does not have the time appended, you get fluky results.

    There is apparently no way to strip this out so you have to do it in your Formatting Rule. In my case I was comparing a Date Picker field in InfoPath with a Date Field from SharePoint. The argument looked like this:  End_Date>Date.

    To compare the End_Date field to just the date part of the Date data you have to add a "substring-before" statement in a formula that describes the Date field.

    The Rule argument ends up looking like this: End_Date>substring-before(Date,"T")

    That totally took care of the problem. I would never have come up with this on my own and I doubt it is covered in any text anywhere. Big thanks to Hilary and InfoPathDev.com forum.

    Bob Blasingame

    • Top 500 Contributor
    • Post Points: 0
All Replies
  • Hello Ibis365Dev,        

    This is Jason Burnside from Microsoft Office365 SharePoint Support.

    Thanks for coming to the communities with your concerns about adding formatting rules to InfoPath.

    Unfortunately, the customizing of InfoPath is unsupported by our team however I found the following links regarding formatting with InfoPath 2010:

    Adding formatting rules

    Add formatting rules in InfoPath

    Add rules for performing other actions

     

    I will continue to monitor this thread for further posts or questions.

    Thanks,

     

    Jason Burnside

    Office 365 SharePoint Support

    • Top 50 Contributor
    • Post Points: 0
  • Hi Jason,

    Thanks for the response. I did get an answer from Hilary Stoupa at the InfoPathDev.com forum. They are a great resource for InfoPath issues.

    To be brief: When you import a date field from SharePoint, even if you have the column set up as Date Only, the data that ends up in InfoPath has the time field appended to it. The XML looks like 20120511T00:00:00. Hillary showed me how to see the actual XML to determine this. Even if you have formatting that displays the Date Only, the time is still there in the data. Naturally when you try to use the data in a rule and control it with a Date value that does not have the time appended, you get fluky results.

    There is apparently no way to strip this out so you have to do it in your Formatting Rule. In my case I was comparing a Date Picker field in InfoPath with a Date Field from SharePoint. The argument looked like this:  End_Date>Date.

    To compare the End_Date field to just the date part of the Date data you have to add a "substring-before" statement in a formula that describes the Date field.

    The Rule argument ends up looking like this: End_Date>substring-before(Date,"T")

    That totally took care of the problem. I would never have come up with this on my own and I doubt it is covered in any text anywhere. Big thanks to Hilary and InfoPathDev.com forum.

    Bob Blasingame

    • Top 500 Contributor
    • Post Points: 0
Page 1 of 1 (3 items)