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?

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.

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