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

[DonLimpio]

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,

[LinnZawWin]

If the field needs to be updated instantly and dynamically, you will have to implement an automation that runs synchronously. Based on the complexity of the relationships between the tables of yours, seems like a synchronous plug-in would be the solution for it.

[FPelliccia]

For query data trough parent you can use fetchXml adding linked-entity from child to the grand-parent. Also, fetchxml are supported both in power automates and plugins logic, so you can choose if you prefer perform logic low code or hardcode

[DonLimpio]

Hi Linn,

Thanks for your answer.

Are synchronous plugins the way to go?

We are looking at hundreds maybe thousands of plugins to make it work with the current structure of our tables.

• Is there a limit to the number of plugin calls per day?
• What if we relax the instant and dynamic constraint, is there a better solution than plugins?
• Should we review our current table structure, are there best practices for designing tables and relationships in Dataverse?
• Is it normal and everybody ends up using tons of plugins?

Best,

[Fubar · Accepted solution]

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) 

[LinnZawWin]

@DonLimpio 

I hope you got the answer to most of your questions from @Fubar's response. I also have some additional comments.

Should we review our current table structure?

Yes. You might probably have to review the way the users use the system too.

I do not know the whole database structure of your system, so I don't have the full background knowledge to make the best suggestion. (and maybe my assumption is wrong based on what I understand from the question)

When you mentioned about "the field needs to be updated instantly and dynamically" in the Invoice Header, does it mean
1. showing the address fields from the "get ship-to contact address" on the Invoice Header form?
2. Or updating the address fields from the "Invoice Header" get updated to the "get ship-to contact address"?

If it is the scenario 1, I believe [Invoice Header] should have a direct lookup to the [Address] table (auto-populated for 1:1 scenario and filtered by the Addresses of the Customer with JavaScript for 1:N scenario).
If the [Address] exists as a lookup on the [Invoice Header], you can show the address fields as the Quick View form. If the user needs to update the address, the user can click on the Address lookup value and update on the original record instead of cloning the data and updating it back.

[DonLimpio]

@Fubar Yes, workflow seems like a more manageable solution. One thing, I think I read that workflows were getting phased out, should it be something to worry about?

@LinnZawWin scenario #1 matches my example. We just read the value from address and populated the invoice header ship-to address fields with it. Initially with the customer's default ship-to address, but we need to be able to modify it if the client wants the products shipped to a different address.

In this scenario adding a lookup to address doesn't solve the problem, we still run into the issue of having to filter with data from 2 tables away (i.e. Contact(Ship-to))

[Invoice header] --> [Customer] --> [Contact(Ship-to)] --> [Address]

I still like the idea. Reviewing the database structure could probably make this solution applicable to more scenarios.

Could we apply the filtering at the table/data level or it can only be done on the form?

Thanks!

[Fubar]

Microsoft would prefer you to use Flow, at the moment they are not on the Deprecations list. Issue with Flow is that at the moment it can only do asynchronous for dataverse triggers, whereas workflows can be either.

If your scenario means a background process would work (could take a couple of minutes to complete processing) then use Flow, if it needs to be realtime then you need to do Plugin or Workflow.

[LinnZawWin]

@DonLimpio 

The filtering can only be done on the form via client scripting (JavaScript).

However, if you want to auto-populate the value from the customer's default ship-to address to the invoice header ship-to address fields with an option to allow modifying, scrap the idea of lookup to address.

You can populate it using JavaScript Xrm.WebApi.retrieveMultipleRecords on load of the new invoice header 

by retrieving the data and auto-populate it. (if the invoice header record is always going to be manually created).

I don't think workflow would be capable of querying the right data considering the complexity of your table structure.