Returning Data Source as Lower Case for Comparison - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Returning Data Source as Lower Case for Comparison

Last post 08-02-2018 11:38 AM by fuzzbear. 13 replies.
Page 1 of 1 (14 items)
Sort Posts: Previous Next
  • 05-26-2016 10:00 AM

    Returning Data Source as Lower Case for Comparison

    My data source contains names with varying capitalization (RPotter, dpotthof, DPotthof, etc.). I want to compare entries in this data source to a lookup field in my form. I can force my lookup field to be all lower case, but how can I compare my lookup field to my data source so that capitalization doesn't matter? I want to type in "pot" (searching for data source fields that contain "pot") and bring back RPotter, dpotthof, and DPotthof into a dropdown. Oh yes...without code?
  • 05-26-2016 12:08 PM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    You'll have to use the translate function. See attached sample. Save the file locally, right click & select design. Preview & type some text in the text box & tab out.
    Hilary Stoupa

  • 05-27-2016 04:52 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    Yes, that's what I'd like, but I couldn't find the translate rules anywhere. I looked in Manage rules for each field and in the fx value under Properties.
  • 05-27-2016 08:04 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    As per the emails you sent, I believe you found the formatting rule on the table now?
    Hilary Stoupa

  • 05-27-2016 09:44 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    Here are the xPaths for my 2 fields:

    /my:myFields/my:Sect_LegalHolds/my:Sect_NameSearch/my:TB_SearchCriteria

    /dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Associate_x0020_Full_x0020_Name

     

    I took your translate expression:

    not(contains(translate(name, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "abcdefghijklmnopqrstuvwxyz"), translate(xdXDocument:get-DOM()/my:myFields/my:field2, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "abcdefghijklmnopqrstuvwxyz")))

     

    and created:

    not(contains(translate(Associate_x0020_Full_x0020_Name, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "abcdefghijklmnopqrstuvwxyz"), translate(xdXDocument:get-DOM()/my:myFields/my:Sect_LegalHolds/my:Sect_NameSearch/my:TB_SearchCriteria, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", "abcdefghijklmnopqrstuvwxyz")))

     

    But, I see everything as soon as the form opens and when I type something into TB_SearchCriteria, I see the repeating table with header only.

     

    I’m sure it is something silly – possibly my references to my fields. The Associate_x0020_Full_x0020_Name field is an imported data source reference.

     

    I also get duplicate names. Is there an easy way to display unique names only?

  • 05-27-2016 09:55 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

     You'll see the same in the sample form I provided you - I was showing everything by default. You could simply put the repeating table in a section, and hide the section if TB_SearchCriteria is blank.

    I'm guessing that  Associate_x0020_Full_x0020_Name probably has a namespace prefix that you are missing. In the Fields task pane, right click the field and select copy XPath. That should show you the namespace prefix (maybe "d:"?) so you can add that.

    A preceding-sibling formula can be used to hide rows so that only unique display - but let's get this condition worked out first. :) 

    Hilary Stoupa

  • 05-27-2016 09:57 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    I noticed when I put translate for the Associate Full Name field into a separate formula and clicked Edit xPath, there was a "d:" missing - so I entered that and it works now. I'll create a separate rule to hide the results when empty.

    Now...ideas on how to eliminate duplicate entries?  :o)

  • 05-27-2016 10:02 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    This would be another formatting rule on the table. The condition would have the Associate Full Name in the first drop down, is equal to in the second drop down. For the third, select Use a formula - and the formula is probably something like this:

    preceding-sibling::d:SharePointListItem_RW/d:Associate_x0020_Full_x0020_Name 

    Hilary Stoupa

  • 05-27-2016 10:32 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    Okay, ready for the preceding-sibling formula. I'll reseach in the meantime. BTW, this has been a GREAT help. DAP
  • 05-27-2016 10:37 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    See the post directly above - I already posted it for you?
    Hilary Stoupa

  • 05-27-2016 01:23 PM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    All is working like a champ. I didnt' quite understand the last snippet you sent, but it pasted well and works even better. Tnx

  • 05-27-2016 01:42 PM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    So, that formula uses preceding-sibling and is basically saying "if the name is the same in this row as the row before, hide this". More here: http://www.xmlplease.com/axis
    Hilary Stoupa

  • 07-19-2018 07:51 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

    I'm trying to do something similar, but not sure how this solution applies to my context. I am returning data from an SQL query, and attempting to use a filter to return the data from the SQL query only when one of the columns matches the accountID of a person field @COLUMNA[COLUMNB = AccountID] in my form My problem is in the SQL database all of the data im trying to match is coming back in various case i.e "Johnny.Appleseed, JOHNNY.APPLESEED, johnny.appleseed, jappleseed, Jappleseed" I am normalizing the results of the AccountID converting it all to lowercase, but i'm not sure how to handle all the data flowing in from the SQL query. I attempted to edit the SQL statement in my data connection .udcx to use the T-SQL Lower() function, but when I use that, the column returns no data in Infopath.
  • 08-02-2018 11:38 AM In reply to

    Re: Returning Data Source as Lower Case for Comparison

     I was able to pull this off, but you have to think backwards. Basically when your doing an is XXXX = YYYY in a data filter, if you want to apply a function, you can only do it in the YYYY part.. so instead of doing is TRANSLATE(XXX, "ABC", "abc") = YYY I had to switch it to, is YYY = TRANSLATE(XXX, "ABC" , "abc")

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