Get the text value of lookup columns

[Zgolbarg]

Hello,

I want to create a flow that whenever an item creates or update in Dynamics sale's Account, the same event happens in the Sharepoint list.

In the account of CRM, I have two types of lookup, one is a lookup in the User table and the other one is a lookup in Account.

I used this action. but I have the GUID of lookup columns in the Sharepoint list.

can you help me which action help to get that value?

[abm]

Hi @Zgolbarg 

To get the lookup value here are the steps.

Note: In your provided screenshot you are using a deprecated connector for D365. You can change it to Dataverse connector now. 

For Dataverse

For both connectors you need to capture the id and use Get row by id to get other details of the related entity.

[Zgolbarg]

@abm 

Thanks for your reply. 

Regardless of getting the text value of the lookup column I have a general error on "create item" action. the message is that "item could not be created";

do you know what is the problem? give me a "bad request" warning as well

[abm]

Hi @Zgolbarg 

Could you post a screenshot of your mapping?

Thanks

[Zgolbarg]

@abm 

if you look at the picture Successor and parent company are lookup fields in the account(same entity) but the original field type is  "successor id"  and "parent_company id"

"Primary_Account_Manager" "Colleague_account_ manger " and "Account Support"  are 3 look up column in user entity and the original type are claim

so give me the error type

[BradWest]

I would suggest using Dataverse, and an option you can try is:

1. Row Added
2. Get Row use the ID from the trigger and use the expand query function.  

The Expand Query function allows you to bring in fields & data from the other related entities to use in the flow.  Go to the link below for more on using Expand Query.  Fetch XML Builder in XRM Toolbox is pretty easy to work with. 

https://dynamicsbackpage.wordpress.com/2019/12/27/using-expand-query-in-power-automate-with-cds/

Just a thought. 

[Zgolbarg]

@BradWest 

Thanks for the reply.

I tried the above method but the entity (let's say Account) will update everywhere at the same time. I want to do this in one direction from Dynamics to SP because the reliable source is Dynamics. I don't want the data change when something create/update in SP.

can I still use this method?

[BradWest]

The alternative to try based upon what you mentioned is to use the "Dynamics List Rows" in step two, filter by the ID of the record in the trigger, and do the Expand Query in the "List Rows".  Third step would be the Sharepoint Create an Item.  Yes, the flow is only one way since the trigger is when a row is added to the table you designated in Dynamics.  

[Zgolbarg]

@BradWest 

Thanks. I am very new to writing queries. can you send me a resource to find how to write filter and expand query in "List records"?

[BradWest]

The article below provides a walk-through.  Fetch XML Builder is available through XRM Toolbox. 

https://dynamicsbackpage.wordpress.com/2019/12/27/using-expand-query-in-power-automate-with-cds/

[BradWest]

The article below provides a walk-through.  Fetch XML Builder is available through XRM Toolbox. 

https://dynamicsbackpage.wordpress.com/2019/12/27/using-expand-query-in-power-automate-with-cds/