Trim unwanted characters from combo box that retrieves data from SharePoint column - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

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

Last post 11-15-2018 11:33 AM by Hilary Stoupa. 6 replies.
Page 1 of 1 (7 items)
Sort Posts: Previous Next
  • 11-15-2018 07:05 AM

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

    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?
  • 11-15-2018 07:19 AM In reply to

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

    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?
  • 11-15-2018 07:38 AM In reply to

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

    Last note: FWIW the field is configured for DateOnlyType, not date and time.
  • 11-15-2018 07:40 AM In reply to

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

    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?
    Hilary Stoupa

  • 11-15-2018 07:41 AM In reply to

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

    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.
    Hilary Stoupa

  • 11-15-2018 07:50 AM In reply to

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

    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.
  • 11-15-2018 11:33 AM In reply to

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

    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
    Hilary Stoupa

Page 1 of 1 (7 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.