Controls

Re: Trim unwanted characters from combo box that retrieves data from SharePoint column

Hilary Stoupa — Thu, 15 Nov 2018 19:33:53 GMT

Well.... You could re-purpose some other list fields for helpers. The issue is you have repeating data, and you need to update each instance. So you could include some list fields you don't need in your data connection, and (for example) set a rule on ID to reset the date fields to the format you want. Set ID to 0 in a form load rule (or sometime after your query has run) and since your ID field will never be 0, all the instances will change. The rule on the ID field could reset the date field using substring formulas to format the date. I just think adding a calculated column to the list is simpler. Here's a link to a sample form with some date formulas: http://www.infopathdev.com/files/folders/examples/entry41104.aspx

Re: Trim unwanted characters from combo box that retrieves data from SharePoint column

Daryl K — Thu, 15 Nov 2018 15:50:14 GMT

Thanks Hilary. So, no way to do this within Infopath? I could have swore I came across several other queries just like mine, where they said they used the substring-before to clear the extra characters, but did not provide detail how/where.

Re: Trim unwanted characters from combo box that retrieves data from SharePoint column

Hilary Stoupa — Thu, 15 Nov 2018 15:41:22 GMT

Daryl K:
Last note: FWIW the field is configured for DateOnlyType, not date and time.

In SharePoint, the actual data type is datetime. That's why you end up with the blank time. SharePoint is just displaying date only, and not asking for time values when the data is entered.

Re: Trim unwanted characters from combo box that retrieves data from SharePoint column

Hilary Stoupa — Thu, 15 Nov 2018 15:40:18 GMT

In SharePoint, you are seeing a formatted date. When InfoPath queries the list / library, it returns the actual value - which is a date time (with a blank time). So - the issue you are going to have here is that you can't use a formula to tidy this up in InfoPath, because, as you mentioned, you are using this data as the values for a combo box. What I would do is add a calculated column to the SharePoint list / library and use SharePoint date formulas to format the date as you'd like to see it in IP. Set the calculated column to just return text, and InfoPath should display it as you have it formatted. Make sense?

Re: Trim unwanted characters from combo box that retrieves data from SharePoint column

Daryl K — Thu, 15 Nov 2018 15:38:47 GMT

Last note: FWIW the field is configured for DateOnlyType, not date and time.

Re: Trim unwanted characters from combo box that retrieves data from SharePoint column

Daryl K — Thu, 15 Nov 2018 15:19:22 GMT

Interestingly, I noticed something else; Infopath reformats the date, so that in the SharePoint column it looks like this: 10/15/2018 but comes across into the combo box as 2018-10-15T00:00:00. I don't care so much about "/" becoming "-" but why does it move the year to the front?

Trim unwanted characters from combo box that retrieves data from SharePoint column

Daryl K — Thu, 15 Nov 2018 15:05:03 GMT

I have an InfoPath form that pulls several columns of data from a SharePoint list into combo boxes. One of the columns is a date field, and even though it is formatted correctly on the SharePoint side as date-only InfoPath adds a time component so that it always looks like this: 2018-10-15T00:00:00. I have done a bit of research and read many times about using a substring-before function to remove the unwanted characters (i.e. substring-before(TimeField, "T") but for the life of me I cannot figure out where/how to use this function in a combo box. Even MS's own help documents mention clicking the Fx button, but this does not exist on a combo box. If it matters this is Infopath 2013. Is there any way to trim the unwanted characters within the combo box?