Compare two text fields.

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

I am trying to compare two text fields [Field1] and [Field2]. If fields do not match, I want to say “Mismatch.”

 

The example below is a match; I need to create a formula that will always take lower case “g” and change it to “G” then see if [Field1] and [Field2] match.

 

Data in [Field1] is 12345678g

Data in [Field2] is 12345678G
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
No problem,
The operator for = wants and exact match and is case sensitive.

But the Contains functions is not so picky and tolerates case differences.

So try this


If(
Length([Field 1]) = Length ([Field 2])
and
Contains([Field 1], [Field 2]), "Match", "Mismatch")
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
If I wrote the formula like this will it return only mismatches:

If([Customer - Related Client]=20,
If(Length([Customer - Enrollment - Meter Number]) = Length([Customer - Meter number]) and Contains([Customer - Enrollment - Meter Number],[Customer - Meter number]),"Meter number does not match enrollment"&"\n")
)&
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
I'm not understanding if you are asking me a question. But your formula should be true if they match, but your words say mismatch.
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
I only want to see those that don't match.

Sorry for any confusion.
(Edited)
Photo of Ken

Ken

  • 170 Points 100 badge 2x thumb
Formula could be:

If(Lower(Trim([Customer - Enrollment - Meter Number] & ""))
   <> Lower(Trim([Customer - Meter number] & "")),
   "Mismatch")

To display only mismatched records, create a report and set the Filter to where that formula field = Mismatch.  This assumes you have entered both meter ID's.  You may want to check for incomplete data also:

If Trim([Customer - Enrollment - Meter Number] & "") = ""
   OR Trim([Customer - Meter number] & "") = "",
   "Missing Meter Info",
   If(Lower(Trim([Customer - Enrollment - Meter Number] & ""))
      <> Lower(Trim([Customer - Meter number] & "")),
      "Mismatch"))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
Try this then

If(
Length([Field 1]) <> Length ([Field 2])
or 
not Contains([Field 1], [Field 2]), "Mismatch")