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,

I suggest you try another connector to see if it works in your environment.

In the third step, you can first add the Apply to each connector and add the value element got from the Get items connector. Then add the Compose connector and add the formula items('Apply_to_each')?['(the lookup column name in your environment)/Value'] (In my environment, it is items('Apply_to_each')?['Lookup_Custodial_Group/Value']) in Expression. Finally add the Update item connector and just add the Outputs element in the newly added single-line text column.

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

Have a nice day!

Cliff

Answer 3

Dear Nic,

Thanks for your updates.

The Dynamic content in the singl;e-line text column in the flow shoule be Lookup_Custodial_Group Value instead of Lookup_Custodial_Group. Please check whether you get the data from the list where you want to copy the lookup column value in Get items in the flow. If yes, I suggest you re-add the Update items connector in the third step and then do a test as follows to see and let us know the result.

Thanks fo ryour effort and time.

Cliff

Answer 4

Dear Cliff,

Thank you for clarifying that information. I re-did the steps as per your instructions (using the Custodial_Group Value dynamic content option for the Custodial_Group_Copy field in the Update items step), but the same thing happened as I showed in my last reply- my selection of Custodial_Group Value disappears and a second 'Apply to each' step pops up with the below dynamic content automatically appearing for both 'Apply to each' steps:

Any other ideas would be greatly appreciated. Thank you so much for all of your help with this!

Many thanks,

Nic

Answer 5

Dear Cliff,

Thank you for your continued support with this issue- I am very grateful for your time and expertise.

I have followed your instructions carefully and implemented the changes you proposed for the third step in the flow. The flow now runs to completion, but it doesn't populate the newly created single line text column with the values. I tried this on two different lists (including the test list where your original method worked), but no values are populated for either the test list or my 'real' list.

Here is a copy of the flow- if you see any error or have any other suggestions that I can try, I am grateful for any feedback/advice.

Please note: in the Compose step, the Inputs entry appears different after the flow is saved/run, but I have hovered over this to show that the expressions is still: items ('Apply_to_each')?['Custodial_Group/Value'])

Here is the results (or rather, lack of) back in the List:

Thank you again for all of your help and I hope that you have a nice day too!

Kind regards,

Nic