InfoPath 2010

Re: Less than or equal to Formatting Rule not working for me

Wheels — Tue, 12 Nov 2019 15:58:31 GMT

Thank you Hilary. A lot of our InfoPath expertise has left the company, and it is a great comfort to know that you are on the other end of the computer for guidance. Have a great day.

Re: Less than or equal to Formatting Rule not working for me

Hilary Stoupa — Tue, 12 Nov 2019 15:26:13 GMT

Perfect, glad you got it figured out. So SharePoint stores dates as datetime. Even if you have the format set on the field to display only date - it is still datetime. That's why the time portion is included! But if you strip off the string from the T on, as you found, you get the date value. String-compare works (which if you look at the XPath expression is what InfoPath uses for greater than / lesser than for non-numeric data) because our XML dates are yyyy-MM-dd, i.e. 2019-02-24 or 2018-05-09.

Re: Less than or equal to Formatting Rule not working for me

Wheels — Tue, 12 Nov 2019 13:28:02 GMT

Please disregard. I have it working now with this:

FormDate is greater than or equal to substring-before(Level_x0020_Start, "T")

FormDate is less than or equal to substring-before(Level_x0020_End, "T")

Re: Less than or equal to Formatting Rule not working for me

Wheels — Tue, 12 Nov 2019 13:06:45 GMT

Good morning Hilary.

I am having a similar issue and found this old thread. I have a start date and end date in SharePoint. Although they are set to Date only, it appears the dates come in with the time portion also. What I am trying to do is take a date picker date (default to today()), and compare to see if the InfoPath date is between the two SharePoint dates. I figured out it was the time portion that is throwing it off as SharePoint comes through as 00:00:00 and when there is a date and time in a datepicker, it is 11:59:59.

I looked at your example and if it were in textboxes it would be a lot simpler, but I am using the dates to filter for a drop-down list to populate a lists of names. There are several date rates for each individual and when someone selects the date, only the list of names for that date period should appear in the drop-down. Once the name is selected it pulls in other information related to that individual for that point in time.

I populated a textbox with the value of the datepicker (FormDate) and formatted it with both substrings methods below:

Specify Filter Conditions

FormDate is greater than or equal to concat(substring(Level_x0020_Start, 6, 2), "/", substring(Level_x0020_Start, 9, 2), "/", substring(Level_x0020_Start, 1, 4)) and

FormDate is less than or equal to concat(substring(Level_x0020_End, 6, 2), "/", substring(Level_x0020_End, 9, 2), "/", substring(Level_x0020_End, 1, 4))

FormDate is greater than or equal to substring-before(Level_x0020_Start, "T")

FormDate is less than or equal to substring-before(Level_x0020_End, "T")

Neither seems to work.

It feels a little odd to compare dates as strings and I had considered converting them to Julian #s without much success.

Any suggestions, WHEELS

Re: Less than or equal to Formatting Rule not working for me

Hilary Stoupa — Fri, 11 May 2012 13:26:20 GMT

I'm so glad you were able to work through this! You've learned some things that are going to help you troubleshoot - conditional formatting can be a bear when the values have been obfuscated by formatting - like you were seeing with the dates. Good luck with your future InfoPath endeavors, and post again if you get stuck.

Re: Less than or equal to Formatting Rule not working for me

IbisDev — Fri, 11 May 2012 02:29:12 GMT

Hilary,

That was absolutely it. And as you said in the sample file it really is crazy. I can see how the data fields would not match up with the data side having the Time appended to the date. Even though the Time was always 00:00:00, it was there. I thought that when I set up the SharePoint column to be Date Only that I had taken care of it. But it is called a Date and Time column. I have no idea how I would have found this without your help, let alone figured out how to correct it. I have gained some excellent knowledge through this.

Thanks for your help and patience. I am really glad I found this forum.

Bob Blasingame

Re: Less than or equal to Formatting Rule not working for me

IbisDev — Thu, 10 May 2012 23:33:07 GMT

Hilary,

I added the date field as a test again and the data does have a T . 2012-01-27T00:00:00 Let me work on this for a bit and see if I can add the substring correctly and I’ll post my results. Thank you so much for your patience and your help. I found the InfoPathDev forum by searching for some other issues I was having and saw some posts from you back in 2010 and 2009 I think. Nice to have someone who sticks with it.

I’ll update as soon as I get this finished.

Bob

Re: Less than or equal to Formatting Rule not working for me

Hilary Stoupa — Thu, 10 May 2012 23:17:32 GMT

Well, first off, you aren't dense, you are new. Some of these initial concepts can be pretty confusing. If you keep in mind that your data may be different from the presentation, that will help. For example, a decimal could be displayed as currency: $4.00, but the value would still be 4.00 (no $ sign!)

You do need to preview the form to enter some data, or to have data returned from your secondary data connection - your steps sound correct. In my sample form, I set default values so it would be easy to see the difference even in design mode.

Since your data is coming from SharePoint - you won't be able to get rid of the time - you can, however, modify your formatting rule to remove it when comparing - if the actual data looks like this:

2012-05-10 00:00:00

With a space between date and time, you could compare your End Date with a substring - like

EndDate >= substring-before(SharePointDate, " ")

I think your data from SharePoint probably does have a space. If it has a T - there is a sample for that in the form I posted, but it would be the same as above, just substring before "T":

EndDate >= substring-before(SharePointDate, " ")

Does that make sense?

Re: Less than or equal to Formatting Rule not working for me

IbisDev — Thu, 10 May 2012 23:09:31 GMT

Thank you so much for taking the time to build that example. I may be a little dense, so let me ask a couple of questions about what I am doing.

When I want to see the XML of the data that is going to the Date field or the Start/End Date Picker field this is what I did.

Open the Text or Date Picker Properties. Under the Data Tab in Binding it says Data Type - DateOnlyType.

Next to that is the Format Option button. In Format Date and Time, I can select None (Display XML Value)

If I click Display XML value and apply it.

Go to the Preview and enter some dates the field shows what I assume is the XML data without formatting.

Or, am I looking in entirely the wrong place or the wrong way. I'm not sure how else to see the data without previewing the form.

I do know that when I added the test field and bound it to the Date field that it had the time in the Date field, so I guess that tells me it is there, so how do I get rid of it?

Re: Less than or equal to Formatting Rule not working for me

Hilary Stoupa — Thu, 10 May 2012 22:43:03 GMT

I think I maybe wasn't clear.

Changing the formatting won't change the value. I just wanted you to be able to see the actual value, to help you figure out what was going on. So when you change the format, that's all you are changing - how the data is displayed. The value will still be what you saw when you changed it to a text box. I hope that makes sense. So, no, time has not been removed at all - it is simply not displayed.

I've attached a simple sample form that may help explain what I mean a little better. Save it locally, right click the file and click design to open in design mode.

Re: Less than or equal to Formatting Rule not working for me

IbisDev — Thu, 10 May 2012 22:28:51 GMT

The End Date date picker as XML showed only the date. The Date field in the repeating table as XML did show the Date00:00:00. So I tried changing the format of the control to show the Date without the * and selected do not diplay time. Didn't stick. I don't need the Date field in the repeating table to be a Date Picker, so I changed it to a Text field as you suggested and was able to get rid of the time and show only the date. This worked in the test field you suggested earlier also. So as near as I can tell, time has been removed, but I get the same result. I tried chaging the argument from Less Than to Less Than or Equal To and back again. No change. It seems logical that when using a Formatting Rule with Hide Control, that it would hide any value the equals the End Date, but on the Start Date it shows Dates that are equal to the Start Date value. Seems like the two would work similiarly, except one is > and the other <.

Re: Less than or equal to Formatting Rule not working for me

Hilary Stoupa — Thu, 10 May 2012 21:59:13 GMT

Sometimes we just don't get our data back in our form in the format we'd expect. So, drag the field onto the form (like you did above), go ahead and delete the time field, and change the date picker to a text box (right click and change the control). Right click the text box (which should be bound to your field) and under Properties, click the format button. Then you can display the XML value. You can do the same for your end date. What you need to know is the actual value of your end date, and the actual value of the date from SharePoint - and if InfoPath is helpfully formatting them for you, you may not be able to figure out what is happening in your form.

Re: Less than or equal to Formatting Rule not working for me

IbisDev — Thu, 10 May 2012 21:46:27 GMT

I set the SharePoint Column up as Date only and when I look at the detail of the field in InfoPath, it says it is a Date Only Type. That said, and I am just getting into InfoPath, when I drug the datafields onto the form and selected Repeatable Table, it set up two fields in the Date column, one for Date and the other for Time. In as much as there was no time value being imported, I just deleted the Time field. Was it a default for it to set up a time field or did it detect one? I guess that is the question.The Formatting rule works fine for the Start Date and also for End Date as long as I use an End Date that is one day past the last date that I actually want to display. It may take me a bit to do the checking you suggested, so I wanted to get back to you as quickly as possible. I really appreciate the help.

Bob

Re: Less than or equal to Formatting Rule not working for me

Hilary Stoupa — Thu, 10 May 2012 21:24:52 GMT

The date probably includes time information. You can see the actual data if you put a control on your form bound to the field you are comparing to, then format it to show the XML value of the date - it is probably something like "2012-05-10 00:00:00". If your end date was including time, you may see a different behavior than you anticipate....

Less than or equal to Formatting Rule not working for me

IbisDev — Thu, 10 May 2012 19:12:09 GMT

I am displaying data from a SharePoint List in an InfoPath 2010 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. I get the same results if I use just > and < without the =. 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 actually think the < arguement is the more correct logic, but the from behaves the same either way. I tried adding +1 to the Date, but probably chose bad syntax as it failed. Any suggestions on why this doesn't work as I expect it to? I have tried about everything I can think of.

Bob