InfoPath Dev

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?

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.

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.

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

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?

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

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)

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 

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

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

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

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

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.

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