Answered

Power Automate - No Match Query with Two Dataverse Tables

(0) Share

Report

Posted on by robcaretta

I am really struggling with dataverse keys and relationships. For what I can gather it doesn't work the same as SQL Server. When creating a relationship in dataverse it doesn't appear you can explicitly declare one column relates directly to another. The controls are not intuitive at all.

What I would like to be able to do is create a no match query between 2 tables. In table #1 I have a full set of records, then in table #2 I have a very small number of records that I would like to use as a filter, which would prevent records from table #1 from displaying. So it would be "no match" query.

1) Does anyone have an example of how to set up a relationship between 2 tables that have the same named field? The content would be a GUID.

2) How would this be implemented inside of Power Automate? I've been attempting to use the List Rows action.

3) Can this matching be performed using a table view?

4) Can power automate leverage table views?

5) Is it possible to use T-SQL inside of power automate to query the tables directly? I know this is possible with SSMS as I have tested it already.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @robcaretta,

1. Consider there are two tables available one Named Parent and Another Named Child, for Relationship in Child entity Create a Lookup field with Related Entity as Parent. So while creating if we map that lookup automatically it will create a relation.
2. Consider Parent Entity logical name (new_parent) and Child Logical name (new_child) then while using in Powerapps for setting a field try using (/new_parent(guidofParentRecord)) using List Row you can pass Guid of Parent Record
3. In table level you cannot Filter but you can use Advance Find to Filter the Record
4. For Views, Power Automate does not support you need to do it manually only in tables-->Entity-->Views
5. No we can use FetchXML via Advance Find.

Let me know if you need more clarification.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Hi @robcaretta, relationships are automatically created with lookup fields and they manage the foreign key and integrity automatically. Quick answers to your questions to supplement @rampprakash responses:

Lookup columns: https://docs.microsoft.com/powerapps/maker/data-platform/data-platform-entity-lookup#add-a-lookup-column-many-to-one-relationship

Power Automate Dataverse connector uses OData format to specific filters, expands, etc. The format for filters is: <entity schema name plural>(“guid”). Beware that it's case-senstive and plural name!

Filtering yes, but can only survace columns on 1 level down.

If you mean retrieve records with the same columns and filters define in a view no, but you can also define the same columns and filters in the List Rows action.

Unfortunately no, but that would be awesome, suggest raise that feature request at: https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas and I'll vote!

Hope this helps!

Was this reply helpful? Yes No
robcaretta 75 on at

Like (0)

Report

Hi @EricRegnier -
I am not yet wrapping my head around how the system is automatically creating the foreign keys and relationships. How does the system know which columns have the same data? Is it based off of column names being the same in both tables? How can I verify what has been created?

1. This is where things start breaking down for me. When creating the relationship you are merely stating that 2 tables are related. The process is creating a new magical lookup field, but what is it doing under the hood? I have no idea. Again is it based off of column names?

2-4. Here is my ERD design for the 2 tables. The column I would like to target for filtering is "flow_templateid". If the GUID exists in the table "exception_flow" I would like it to be filtered OUT from "inventory_flow"? This would be a negative match, and there will be multiple records in table "exception_flow". How would I go about doing that inside of power automate? Sorry, I am not understanding the syntax.

5. Here is my new idea "Power Automate - SQL Action for Dataverse"
https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Power-Automate-SQL-Action-for-Dataverse/idi-p/1358919

I would also recommend upvoting this existing idea.
https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Support-Views-in-the-quot-List-Rows-quot-action-of-dataverse/idc-p/1358922#M27174

Was this reply helpful? Yes No
robcaretta 75 on at

Like (0)

Report
Here is a no match SQL query example. This is what I would like to pull off in power automate.

SELECT UIF.flow_flowid, UIF.flow_templateid FROM inventory_flow as UIF LEFT JOIN exception_flow as UEF ON UIF.flow_templateid = UEF.flow_templateid WHERE UEF.flow_templateid IS NULL

Was this reply helpful? Yes No
Verified answer

robcaretta 75 on at

Like (0)

Report

This was basically what I was looking for. A left outer join no match query.
https://docs.microsoft.com/en-us/powerapps/developer/data-platform/use-fetchxml-left-outer-join-query-records-not-in

I have to say, using FetchXML to simulate SQL to return JSON seems insane to me. The dataverse and tooling here regarding writing complex joins and queries needs some serious attention. Then you have the dataverse handling boolean values one way, and power automate handling it another way. It feels like layers added over antiquated technology.

Was this reply helpful? Yes No