Validate Text in all Records by Checking Against a List

[Scott Lyle Raymond]

I have a report. One of the fields in this report is EmployeeName, which is auto-populated by the system which generates the report. This field contains user IDs pulled from employee accounts on other in-house systems. There may be some variety. For example, John Doe might sometimes appear in the report as "John Doe," and at other times "JD.." or "jdoe001".

I need a means of maintaining a list of employee names and their various aliases. I suspect a table is more appropriate than a list. This table would contain two fields: EmployeeName and __kp_Alias.

Then I need a script that will systematically check the contents of EmployeeName in all records, and substitute aliases with actual employee names.

I am relatively new to FM, enough that I have built a few simple scripts, but I have no idea where to begin with this challenge. Can someone point me in the right direction?

[comment]

If the Employees table contains two fields, CorrectName and Aliases, and in the Aliases field you have a return-separated list of each employee's various "names", you can then define a relationship as:

YourTable::EmployeeName = Employees::Aliases

Then place the CorrectName field on the layout of your table (make it non-enterable, to prevent accidental entry).

Note that there is an assumption here that no two employees share an alias.

[Scott Lyle Raymond]

Wow, I had no idea FM could parse lists like that! Thanks.