General

Re: Filter a SharePoint List on a date field

Michael Vasquez — Fri, 26 Oct 2012 20:16:36 GMT

I was using conditional statement to hide the rows in the repeating table. This has to be the worst application on the market when it comes to dates. It works in development (Infopath) but not in production. (Web form) I have spent many hours trying to figure this out. I still got that T even when the test datefield is a test box. If I could set the date picket to use date and times maybe that might work but it seems that I can only use the date. I tried the substring function without success. I did noticed that the classdatetest was in the repeating table so I moved it out of the repeating table and the T was gone.

It's working on the Web Form. What I did was to change the ClassDate (date picker) from text to Date. The ClassDateTest field, which can be hidden, I left as a text box. I set a rule on the ClassDate field to set a fields value(ClassDateTest) to concat(., " 00:00:00"). When I ran it, there was no T in the ClassDateTest field. When I ran the conditional statement to show my only the rows where the date and name of the class match, the Class Roster for that day shows up.

Thanks very much for giving me ideas as to how to handles microsoft's method of handling dates. Why can't they make it easy all the way around with dealing with dates.

I'll be doing more testing and hopefully this is it.

Re: Filter a SharePoint List on a date field

Hilary Stoupa — Fri, 26 Oct 2012 19:26:10 GMT

And also - are you using conditional formatting to hide rows or are you using a filter on the repeating table / section? I re-read your original question, but don't see that info - not sure if that matters or not...

Re: Filter a SharePoint List on a date field

Michael Vasquez — Fri, 26 Oct 2012 19:19:19 GMT

All files are strings. I initially tried the substring but on the Web form it did not work I'll try setting the date using the ClassDate to set ClassDateTest and see if the output is OK.

Thanks,

Re: Filter a SharePoint List on a date field

Hilary Stoupa — Fri, 26 Oct 2012 16:07:25 GMT

Is ClassDate the date field in the main data source? Is it a dateTime data type? XML dateTime values have a T between date & time.

It seems you could have used rules for this instead of code? Put a rule on ClassDate to set ClassDateTest to include the T and the 0s.

I guess I'm also wondering why you aren't just using an expression for your conditional formatting - substring both fields to just the first 10 characters (which would be just the date portion)

Re: Filter a SharePoint List on a date field

Michael Vasquez — Fri, 26 Oct 2012 15:44:05 GMT

Michael Vasquez:

I finally got i to work properly for the SHAREPOINT Class, It should work on the others.

What I did was to change the format of the DateClass Field in code such as:
Public Sub ClassDate_Changed(ByVal sender As Object, ByVal e As XmlEventArgs)
Dim xpn As XPathNavigator = Me.CreateNavigator()
Dim ns As XmlNamespaceManager = Me.NamespaceManagerDim dateValue As String = e.NewValue

If dateValue.Length < 19 Then
xpn.SelectSingleNode("/my:myForm/my:ClassDate", ns).SetValue(e.NewValue + " 00:00:00")
Exit Sub

End If

End Sub

The value shown in the date picker(ClassDate) is still yyyy-mm-dd but the underlying value is actuall yyyy-mm-dd 00:00:00

The conditional formating I used on the Repeating Tables was:

Class_Date(the date on the sharepoint list) is not equal to ClassDate OR

ClassList is not equal to "SHAREPOINT"

Hide this control.

I will now test it on the rest of the classes.

Thanks again.

OK This worked initially. I created another field called ClassDateTest, text box. I set the value to ClassDate and also created an onChange function that will change the value to yyyy-mm-dd 00:00:00. Initially it works for the SHAREPOINT class but when I tried to go to another date the value of the ClassDateTest is yyyy-mm-ddT00:00:00. Noticed the "T". This "T" does not show up the infopath 2007 form, only in the Web form.

Anyother suggestions?

Frustrated Mike

Re: Filter a SharePoint List on a date field

Michael Vasquez — Fri, 26 Oct 2012 14:32:27 GMT

I finally got i to work properly for the SHAREPOINT Class, It should work on the others.

What I did was to change the format of the DateClass Field in code such as:

Public Sub ClassDate_Changed(ByVal sender As Object, ByVal e As XmlEventArgs)

Dim xpn As XPathNavigator = Me.CreateNavigator()

Dim ns As XmlNamespaceManager = Me.NamespaceManagerDim dateValue As String = e.NewValue

If dateValue.Length < 19 Then

xpn.SelectSingleNode("/my:myForm/my:ClassDate", ns).SetValue(e.NewValue + " 00:00:00")

Exit Sub

End If

End Sub

The value shown in the date picker(ClassDate) is still yyyy-mm-dd but the underlying value is actuall yyyy-mm-dd 00:00:00

The conditional formating I used on the Repeating Tables was:

Class_Date(the date on the sharepoint list) is not equal to ClassDate OR

ClassList is not equal to "SHAREPOINT"

Hide this control.

I will now test it on the rest of the classes.

Thanks again.

Re: Filter a SharePoint List on a date field

Hilary Stoupa — Thu, 25 Oct 2012 19:53:30 GMT

I think you have to find out how IPFS is returning the date.

Put a couple of calculated values on your form - one displaying Class_date and one displaying ClassDate. Set the format on the calculated values to display the XML value. Then look at it in IPFS - what are the values?

Re: Filter a SharePoint List on a date field

Michael Vasquez — Thu, 25 Oct 2012 19:45:46 GMT

I spoke too soon. When I went to publish the form, the Web form did not work; but in Infopath it does.

The Class Roster InfoPath 2007 Web Form has 27 data connections, which point to 27 different list. No problem here.

Here is my rule for the class roster on an individual Repeating Table.

The first repeating table points to a secondary data source called SHAREPOINT.

If Class_date is not equal to concat(ClassDate, " 00:00:00") OR ClassList is not equal to SHAREPOINT, "Hide this control."

Class_date is the class date on the SHAREPOINT list AND ClassDate is a date picker field on my form. ClassList is a drop-down list on the form that shows all of the classes offerred.

When I run this list in InfoPath 2007, it works great. When I run it as a Web form it does not work. If I remove the rule on this repeating table, the Web form will show all the people who have registered for this class.

Does anyone know how I can get this to work.

The template is fully trusted.

TIA Mike

Re: Filter a SharePoint List on a date field

Michael Vasquez — Thu, 18 Oct 2012 19:51:38 GMT

You are the best. It worked. I thought I had tried the concat function but I probably had it reversed. Now I just have to do this for the other 20 classes. But at least the hard part is over.

Re: Filter a SharePoint List on a date field

Hilary Stoupa — Thu, 18 Oct 2012 17:36:29 GMT

Couldn't you just concat the value from your date picker with " 00:00:00" ? I've attached a sample - right click the file link in the header of this post and save locally. Right click the saved file and preveiw.

Filter a SharePoint List on a date field

Michael Vasquez — Thu, 18 Oct 2012 17:19:11 GMT

I have searched the Internet and have found some solutions but none work. I have a SharePoint List called SharePoint, which is also a Data Connection on my InfoPath 2007 form. I have created a repeating section to the my data connection, SharePoint. I have also added a test box on the repeating section,which points to a field, @Class_Date. I have also created a Date Picker field on the Infopath 2007 form.

The problem that I am experiencing is the @Class_Date field shows the output as yyyy-mm-dd 00:00:00. I have tried to setup a rule using a substring function to only show yyyy-mm-dd. For some reason this does not work. How do I remove the 00:00:00 on that @field? When I change the Date Picker to a text box and make the default yyyy-mm-dd 00:00:00, my conditional statement on my repeating section works because now both fields match and the output is the records based on the matching dates.

I also noted that when the form loads, the rule is not applied but when I edit the @Class_Date field by removing the last "0", the rule runs and the the is now yyyy-mm-dd, which is what I want.

How do I get this rule to run when the form loads?

TIA

Mike