I am missing a text command
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,
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.