1 Reply Latest reply on Sep 5, 2014 10:50 AM by philmodjunk

    I am missing a text command

    gregdc

      Title

      I am missing a text command

      Post

      I have a new database.  One table has a field (imported data) that is LastName,FirstName.  In that same record there is a field (again imported data) that is FirstName_LastName.   I would like to write a calculated field that would compare these two fields and find out if they are the same person.  

      I started to write an IF(Left(field1, xxxxx-1) = Right(field2, Lenght(field2)-yyyyy), "Match", "No Match").  Then I went looking for a way to calculate xxxxx and yyyyy, aka xxxxx = locate(field1, ",") and yyyyy = locate(field2, "_").   Locate would return the location of the "," or "_" in the field1 or field2 respectively.   But I can not seem to find any such command in FM13.     

      What am I overlooking?  Or do I need to write two looping calculations to do this, then do the IF ???

      Thanks again for the help,

      Greg

        • 1. Re: I am missing a text command
          philmodjunk

          Try this Boolean expression:

          Substitute ( Field1 ; "_" ; "," ) = Field2

          But look out for spaces after the comma, you may need to use:

          Substitute ( Field1 ; "_" ; ", " ) = Field2

          If there is a space after every comma

          or even use a filter function to filter out all space characters from both fields to better compare the field values.