Forum Discussion
Store both current employees (AD) and past employees (non-AD) in MS List column?
I have an EMPLOYEE text column in an Excel spreadsheet. I plan to import the spreadsheet as an MS List.
The EMPLOYEE spreadsheet column contains both current and past employee names. If I create a new PERSON column in the List to store this data (for clean querying and automation purposes), only the current employees will match up with the Active Directory picklist, not the past employees. If I enter past employees into the PERSON column, the List won't let me save those values since they're invalid.
Is there a way to force the past employee names into the PERSON column as broken/unlinked values, such as through a Power Automate flow? Or do I have no choice but to use a TEXT column instead with a custom picklist (manually maintained picklist values)?
2 Replies
You can't achieve what you want to do with a person column, you'll need to have a single line of text column.
A Person column is not a good place to keep former employees as unlinked names. It stores a reference to a valid directory identity, rather than just the display text shown on the item. A flow does not bypass that requirement: it can resolve an active account, but an account that no longer exists in the directory will fail validation. SharePoint describes Person or Group columns as a searchable list populated from the configured directory and site groups.
For a small list that rarely changes, keep Employee name as a text column or Choice column. For reporting and Power Automate, a better long-term design is an Employee Directory list containing every current and former employee, with fields such as: Employee ID, Display name, Employment status, Current account — a Person column, left blank for former employees.
The imported list can then use a Lookup column to the Employee Directory record. That keeps former employees selectable, preserves a stable employee ID, and still gives current employees a valid Person field when one is needed. Lookup columns are designed to use values from another list on the same site.
So the practical approach is not to force invalid values into a Person field. Import the existing Excel names into text first, create the employee directory, and use either a Lookup column for all employees or a separate optional Person column for active employees only.
