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 394 Points
Posted 2 years ago
QuickBaseCoach App Dev./Training, Champion
 69,572 Points
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")
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")
 394 Points
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")
)&
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")
)&
QuickBaseCoach App Dev./Training, Champion
 69,572 Points
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.
 394 Points
 200 Points
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"))
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"))
QuickBaseCoach App Dev./Training, Champion
 69,572 Points
Try this then
If(
Length([Field 1]) <> Length ([Field 2])
or
not Contains([Field 1], [Field 2]), "Mismatch")
If(
Length([Field 1]) <> Length ([Field 2])
or
not Contains([Field 1], [Field 2]), "Mismatch")
Related Categories

Formulas & functions
 3004 Conversations
 80 Followers