cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnCz
Advocate I
Advocate I

How to filter/query to get row id D365 Business Central?

How do we use "Business Central Get item" action or an alternative action to lookup table row id based on filter/query?  Example: creating sales order Business Central requires a customer ID and we only wish to create a new customer if their email address or phone number doesn't already exist .. otherwise reference existing id.

6 REPLIES 6
v-yuazh-msft
Community Support
Community Support

Hi @JohnCz,

 

Could you please explain more details about your requirement so we would try to provide a proper workaround for you.

 

Best regards,

Alice

I believe he's asking (and I would also ask): what's the best way to find the "Row id" necessary to use the "Get record" action in the Business Central connector?

 

2019-12-10 14_08_18-Window.png

 

There is no search/filter/list action in the connector, and guessing the row ID based on info available in the Business Central UI is hopeless. For example, I looked for the row ID for a customer record in the customer record's page in the Business Central UI, in the HTML code of the page, and in the URL. Guessed at many things, none worked.

walterashields
New Member

This is still a very valid question anyone know the answer?

This is exactly where I'm stumbling as well. Anyone figured this out?

AidanH91
New Member

AFAIK there isn't a way (yet) to filter and search for items from Power Automate to Business Central, however it appears your biggest issue is finding the ["Row id"] field relevant to your record.

This is the $systemId field (field 2000000000) for a record, it is a GUID but will be read fine as text.

You can find this value for a given Business Central record by inspecting the page (Ctrl+Alt+F1) and scrolling down the fields until you see the $systemId field. It is a non-editible field that is unique to every record in Business Central.

 

When you create a BC record in Power Automate you can retrieve this value (the ["id"] field) from the created object. The best practice is to make note of it in your source system that calls the Flow, for example in Sharepoint having a field in your list called ["BC System ID"] or something.

You can then use this field to get your BC record.

I know its an old thread but I came across this exact issue today and this thread whilst searching. Just to let you know I used an alternative workaround which may be of interest for future - an HTTP call to one of the standard web services. I added a GET request with a filter based on the value I'd pulled out from the standard BC connector, so an HTTP GET call returns all the item table or customer table, but the filter limits this to 1 result where number = "value from earlier step". Now the body result of that HTTP request contains the item/customer/vendor etc you wanted and all the related info. 

Then just parse the JSON to retrieve the values and use them in the flow. E.g to retrieve the ID needed for actions like "Update record" etc. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,445)