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,

Thanks for your updates and screenshots.

I notice that you set multiple additional sub lookup columns in the list which is showed in the Power Automate connector and that will cause that Power Automate can't fetch the data from the main lookup column, so the data is null and can't be populated in the single-line text column.

If the additional lookup columns aren't a must in your environment, one simple way to fix the issue may be to uncheck the additional lookup columns and only leave the main lookup column and then do a test to see the result.

If the additional lookup columns are a must in your environment, you may use Rest API and some additional formula to get the values from the main lookup column.

Before creating the flow, I suggest you change the list name and the main lookup column name to a simple name such as ListAA2 and LookupColumn1 in my environment to get rid of some error when running the flow.

You can create the flow as follows.

To get the main lookup column LookupColumn1 value body, use the Get method with Rest API in the Send a HTTP request to SharePoint connector.

Uri: _api/web/lists/GetByTitle('ListAA2')/items(@{items('Apply_to_each')?['ID']})?$select=LookupColumn1/Column1&$expand=LookupColumn1/Column1

To get the results part from the body, use the Compose connector with the formula outputs('Send_an_HTTP_request_to_SharePoint_2')?['body']?['d']?['LookupColumn1'].

To get the LookupColumn1 value from Compose, use the send Compose connector with the formula outputs('Compose')?['results'][0]?['Column1'].

To populate the LookupColumn1 value in the single-line text column LookupColumn1Text, use the Post method with Rest API in the Send a HTTP request to SharePoint connector.

Uri: _api/web/lists/GetByTitle('ListAA2')/Items(@{items('Apply_to_each')?['ID']})

Headers:

{

"If-Match": "*",

"X-HTTP-Method": "MERGE",

"Accept": "application/json;odata=verbose",

"Content-Type": "application/json;odata=verbose"

}

Body:

{

"__metadata": {

"type": "SP.Data.ListAA2ListItem"

},

"LookupColumn1Text": "@{outputs('Compose_2')}"

}

Note: You may change the columnname and the list name in your environment.

Result:

For your reference: How to get a specific value from a JSON in Power Automate SharePoint 2013/Office 365 : Retrieve lookup column value REST-Javascript. ***Disclaimer:***Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Thanks for your effort and time.

Cliff

Answer 3

Dear Cliff,

Thank you for your ongoing support and advice with this issue- I am very grateful for it! I am off work sick at the moment, but will try what you have proposed as soon as I am back.

In the meantime, I just wanted to note that you are correct in pointing out that there are several other lookup columns in this list, which are linked to several other lists. Please see the below diagram- the list I am trying to use for this flow is circled in pink and has 5 lookup columns in total. Each line within the list is a different column (the column type is in brackets after the name). The coloured connector lines show how the lookup lists are connected to each other.

I am beginning to think that it is this complex architecture of lookup lists connected to other lists that may be the cause of many problems I have been having with Power Automate (this issue is just one!). I am also wondering if using Sharepoint Lists for this 'relational-database' may not be the optimal choice. In your experience, would it be easier to work with this type of relational data in Power Automate (and Power Apps) if the same data was founded in a different source (such as Dataverse or SQL)? I am not an IT expert and neither are most of the users of these lists, so would appreciate any advice on how to make this data easier to work with.

With much appreciation.

Nic

Answer 4

Dear Nic,

Thanks for your updates and letting us know the current situation in your environment.

Generally, for the data updating in SharePoint Online, I suggest you use Power Shell such as Shareapoint Online CSOM and Power Automate. But to get a dynamic update and sync values among multiple data source, Power Automate is teh best choice as it can connect to multiple data source.

From your description, if you want to sync data among SQL, you may need to use the Excel connector to transfer the data between SQL and SharePoint by Power Automate. For Dataverse such as Project for the web, Power Automate is also the best choice as it has the built-in connector to interact with the two data sources.

Power Apps is designed to be used to customize the form such as the Excel table, the SharePoint Online list form and so on. It may be unable to trigger the automatic process at work. However, it can be used with Power Automate to simplify the daily work.

As the multiple additional sub lookup column value population can't be achieved by the out-of-the-box option in SharePoint Online, there may be no easier way. Power Automate may be the better way I can recommend in SharePoint Online based on the scenario in your environment.

I suggest you try the steps I mentioned above to see the result.

Thanks for your understanding and have a nice day!

Cliff

Answer 5

Hi Cliff,

Thank you for your insights- much appreciated. My apologies for the delayed reply- I needed to be fully recovered from my illness to attempt the Rest API (without an IT background, this is quite novel!).

Just to get back to you in response to this: "If the additional lookup columns aren't a must in your environment, one simple way to fix the issue may be to uncheck the additional lookup columns and only leave the main lookup column and then do a test to see the result."

While we do have several lookup lists interconnected to this list (as per my last reply), each lookup list already has only one lookup column showing in the list that I am using in this flow (list now called: Test20210225....List). Note- the name has been abbreviated where .... appears.

I tried the Rest API method as you suggested, but there appears to be an error in the Send an HTTP request to SharePoint 2 Step. I am wondering if this may be due to me not correctly replacing part of the formula where you have put 'Column1'? 'Column1' is referred to in several steps in your flow:

• Send an HTTP request to SharePoint 2
• Compose 2

I tried using the column title that appeared equivalent in my lookup list ('Title'), but I had the same error. I would be grateful for your advice on how I can rectify this error:

The full error message is:

{ "status": 400,"message": "The field or property 'CustodialGroup' does not exist.\r\nclientRequestId: ****\r\nserviceRequestId: ***","source": "https......_api/web/lists/GetByTitle('Test20210225....List')/items(1)?$select=CustodialGroup/Column1&$expand=CustodialGroup/Column1","errors": ["-1","Microsoft.SharePoint.SPException"]}

Here is what I currently have entered for this step:

Uri: _api/web/lists/GetByTitle('Test20210225....List')/items(@{items('Apply_to_each')?['ID']})?$select=CustodialGroup/Column1&$expand=CustodialGroup/Column1

This is my lookup List:

This is my list that I am using in this flow (wanting to copy and make a text version of the CustodialGroup column, which data is a lookup from the 'Title' column of the above Units list.

Thank you for your ongoing support with this issue Cliff, I appreciate all of your time.

Kind regards,

Nic