Discussions

 View Only
  • 1.  Compare two text fields.

    Posted 08-07-2017 20:23

    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


  • 2.  RE: Compare two text fields.

    Posted 08-08-2017 01:08
    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")


  • 3.  RE: Compare two text fields.

    Posted 08-09-2017 15:41
    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")
    )&


  • 4.  RE: Compare two text fields.

    Posted 08-09-2017 21:12
    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.


  • 5.  RE: Compare two text fields.

    Posted 08-10-2017 13:48
    I only want to see those that don't match.

    Sorry for any confusion.


  • 6.  RE: Compare two text fields.

    Posted 08-10-2017 14:44
    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"))


  • 7.  RE: Compare two text fields.

    Posted 08-10-2017 21:28
    Try this then

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