How to make column values that are the result of JSON column formatting available in list export and filtering?

Question

I have created two columns of type Single line of text.

They reference a Person column via the following JSON column formatting:

Department

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$Author.department]"
}

Job Title

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$Author.jobTitle]"
}

I get the desired behaviour of being able to see the following in the list:

Person Name  |  Business Department  |  Job Title   

However, the following undesired behaviors are occurring:

• Exported list has no values in the Department or Job Title columns

• There are no values in the Department or Job Title section that I can filter on

I understand why this is happening (because the JSON formatting is grabbing values after the data has loaded - and it is just formatting, not real data), but I am just wondering if there are any approaches to getting the desired behaviour working? i.e being able to export and filter on the Department and Job Title values.

I thought about using a Calculated Column to reference the properties of the Person column, but Person is not supported.

Answer 1

You could create a Power Automate flow as a workaround: when an item is created get "Department" and "Job Title" value from person column then set value to Single line of text columns.

Here is article about Getting SharePoint field values in Power Automate for reference.