General

Re: Returning Data Source as Lower Case for Comparison

fuzzbear — Thu, 02 Aug 2018 18:38:01 GMT

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")

Re: Returning Data Source as Lower Case for Comparison

fuzzbear — Thu, 19 Jul 2018 14:51:30 GMT

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.

Re: Returning Data Source as Lower Case for Comparison

Hilary Stoupa — Fri, 27 May 2016 20:42:43 GMT

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

Re: Returning Data Source as Lower Case for Comparison

dapklp — Fri, 27 May 2016 20:23:04 GMT

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

Re: Returning Data Source as Lower Case for Comparison

Hilary Stoupa — Fri, 27 May 2016 17:37:30 GMT

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

Re: Returning Data Source as Lower Case for Comparison

dapklp — Fri, 27 May 2016 17:32:21 GMT

Okay, ready for the preceding-sibling formula. I'll reseach in the meantime. BTW, this has been a GREAT help. DAP

Re: Returning Data Source as Lower Case for Comparison

Hilary Stoupa — Fri, 27 May 2016 17:02:33 GMT

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

Re: Returning Data Source as Lower Case for Comparison

dapklp — Fri, 27 May 2016 16:57:18 GMT

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)

Re: Returning Data Source as Lower Case for Comparison

Hilary Stoupa — Fri, 27 May 2016 16:55:16 GMT

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. :)

Re: Returning Data Source as Lower Case for Comparison

dapklp — Fri, 27 May 2016 16:44:41 GMT

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?

Re: Returning Data Source as Lower Case for Comparison

Hilary Stoupa — Fri, 27 May 2016 15:04:38 GMT

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

Re: Returning Data Source as Lower Case for Comparison

dapklp — Fri, 27 May 2016 11:52:31 GMT

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.

Re: Returning Data Source as Lower Case for Comparison

Hilary Stoupa — Thu, 26 May 2016 19:08:16 GMT

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.

Returning Data Source as Lower Case for Comparison

dapklp — Thu, 26 May 2016 17:00:23 GMT

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?