Create a text version/copy of a lookup column in a Sharepoint List

Question

Hello,

I have a long SharePoint List which contains some lookup columns. I need to do the following:

1. Duplicate an entire lookup column.
2. Have the duplicate column display the lookup values as text.

I have been trying to figure out how to do the above all at once using Power Automate, but I am very new to designing flows, so have not been successful. Specific details are below:

In the below LB_List table, I am trying to copy and make a text version of the Custodial_Group column. This column is a lookup column, which is pulled from the below Faculty_Units table.

If this can be done (in PowerAutomate or through any other means), I would be grateful for instructions that a novice can follow. 

Thank you :)

Answer 1

Dear Nic,

Thanks for your updates.

I noticed that the the CustodialGroup column seems to be linked with the Title column. So the Uri needs to be _api/web/lists/GetByTitle('Test20210225....List')/items(@{items('Apply_to_each')?['ID']})?$select=CustodialGroup/Title&$expand=CustodialGroup/Title.

I suggest you re-add the connector and then run it to see the result.

Thanks for your effort and time.

Cliff

Answer 2

Dear Nic,

Yes, this can be done by Power Automate.

The flow just needs three connectors.

For example, I create ListA and create a column Column1. Then I create ListB, create a lookup column Lookup to Column1 and create a single-line text column Lookup to Column1 (Copy) to copy the values from Lookup to Column1 as text.

You can create the flow as follows.

Result:

Welcome to share any updates when you have time if you need further help on this issue.

Thanks for your effort and time.

Cliff

Answer 3

Dear Cliff,

Thank you so much for your reply and detailed instructions- I appreciate your time and effort very much (my apologies for the delayed reply- my work is part time).

I followed your steps carefully, and while the flow does seem to generate a duplicate of the lookup column, the way that the data is displayed is not quite right- please see image. The Custodial_Group_Copy shows the results of the flow (which should look like the Custodial_Group column, just a text version).

Do you have any idea of what went wrong? I am not sure if I selected the correct dynamic content option for the new Custodial_group_Copy column in the Update item part of the flow (last entry). There are several options that came up (see below), I used the 'Custodial Group' option for the flow, as the other options that I tried seemed to add an extra 'Apply to each' step, resulting in failure when running the flow.

Thank you so much for your help with this Cliff.

Kind regards,

Nic

Answer 4

Dear Nic,

Thanks for your updates.

I do a test with Power Automate again and the flow populates the text well. May I know whether you just create a single-line text column? Do you deploy some code with Column Formatting on the column such as JSON code?

If the column type isn't the single-line text type, I suggest you use the single-line text type, reconfigure teh flow with the new single-line text column and then do a test to see and let us know the result.

Thanks for your effort and time.

Cliff

Answer 5

Hi Cliff,

Thank you for your reply and further advice. I can confirm that the column type is a single line text column and I have also not used any JSON code for the column formatting.

I think that I have found the source of the problem, but in doing so have discovered a new problem! The only way that I could get this flow to run successfully in my real list was to have the highlighted dynamic content entry below as the equivalent to 'Lookup to Column 1', rather than 'Lookup to column 1 Value' . I assume that in your flow, you used 'Lookup to column 1 Value'?

When I created a new List A and List B, as you did in your example, I was able to successfully run the flow and get a text version duplicate of my desired column. However, when I try to put together the exact same equivalent flow in my real list, in the Update Item step, when I select 'Custodial_Group_Value' as the dynamic content for the newly created single line text column called 'Custodial_Group_Copy', the content automatically disappears and instead a new 'Apply to Each 2' step pops up with 'Current item' already populated. See below:

When I run this flow, it fails as shown below:

Do you know what is happening and how I can fix it to get the flow working on my real list?

Many thanks for all of your help.

Nic