Solved: Dataverse: query data further than 1 parent table away (Lookup to Grandparent, etc.)

Dataverse: query data further than 1 parent table away (Lookup to Grandparent, etc.)

In Dataverse how can we query data from a table that is further than 1 table away?

Lookup works well to query data 1 table away (parent table).

What is the solution to go further up this chain (Child --> Parent --> Grandparent --> Great-Grandparent --> .. --> Great..Great-Grandparent)

Ex: Populate the ship-to address in the invoice header, if we have table relationships looking like that:

[Invoice Header] --N:1--> [Customer] --1:N (reduce to 1:1: get ship-to contact)--> [Contact] --N:1--> [Address]

I was initially thinking about using Power-Automate, but the field needs to be updated instantly and dynamically (ex: changing the default customer ship-to to another contact) when the user populates the form and not after it's saved.

Also, if possible, I want to avoid mapping the parent field to the child table, which seems pretty heavy to implement, maintain, and operate.

It seems like a pretty basic requirement, but I haven't found a solution for it yet. I would appreciate any advice on the best way to implement this behavior in Dataverse.

Cheers,

Categories:

Microsoft Dataverse

Plugins and Workflows are considered internal calls and do not count towards API counts (but Power automate Flows do)

Plugins and Workflows can be set to be either synchronous (real-time) or asynchronous (background). Flow for Dataverse is currently only asynchronous. Configuration is generally better than custom code, so where appropriate use Workflows or Flow and do Plugins where the logic is beyond what Workflows or Flow (e.g. if you need to compare a value pre and post update operation etc)

A very complex system may end up with several hundred plugin steps a simple system may have none.

Should you review your current Table structure = yes, particularly if you have relationships that are really 1:1 and not true 1:N

Your example, based on the relationships, to save the value you could probably do it with a Workflow making use of a Action to retrieve the Address from the Contact (push contact to the Action as an input and out put the Address)

Quick Links

Subscribe to this forum!