Set value to Look up field from SQl server using Power Automate

(0) Share

Report

Posted on by Elpibato

160

Hi all,

I'm hoping you will be able to help me a Flow I'm working with! The main goal of my flow is to update or create records. I'm updating an Account record that has a lookup field to a custom entity called Payment Terms.

I have a field also in my Tbale in SQL server which is "ID-CRM" and I set this field to the Account Unique idetifier.

How it could be possible to set the ID if Look up field in SQL table and get it as a value in the Payment terms look up field in my Account record?

Does anybody have an idea?

THANKS!

All responses (3)

Answers (1)

Suggested answer

Wahaj Rashid 11,321 on at

Like (0)

Report
RE: Set value to Look up field from SQl server using Power Automate

Hi,

You cannot update a lookup field based on a Name.

To this you need to first get the GUID of new_paymentterms based on the name.

Here are high level steps in the Microsoft Flow:

Add a Common Data Service action "List Rows" with following properties (after you get data from SQL Server):

Table name: new_paymentterms

Select columns: new_paymentermsid (primary key column name)

Filter rows: new_name eq "<Name from SQL Server row data>" (choose the name value you got here form SQL Server data step)

Now, add "Update row" action for Account and use new_paymenttermsid returned in the previous step to update the Account's lookup field.

If you have a master table of PaymentTerms in your SQL Server DB, I would suggest to keep the Payment Terms CRM-ID as well their. It would simplify the flow and will be less expensive (in terms of performance). Of course, this depends on your architecture.
Elpibato 160 on at

Like (0)

Report

RE: Set value to Look up field from SQl server using Power Automate

Hi
Verified answer

Wahaj Rashid 11,321 on at

Like (1)

Report
RE: Set value to Look up field from SQl server using Power Automate

Hi,

Thank you for your query.

Your question is not clear, let me answer this as per my understanding.

- You need to set a lookup field based on ID ( you stored in SQL Server table) using flow:

Use the Update record action in flow and set the your lookup field value like:

datasetname(guid) | Example: new_paymentterms(<GUID of payment term>)

Here new_paymenterm is your custom entity name, see following for details:

Setting Lookup in a Flow CDS Connector: Classic vs. Current Environment connector | Power Automate Quick Tip | D365 Demystified

If I miss-understood our question, please feel free to ask.