Creating Dataverse View with values from Child tables

(0) Share

Report

Posted on by Dej

Hi There,

I am a newbie in building Power apps Canvas Applications.

I have Employee Table related to three other tables Salary , ID Card , LabourInfo Tables using polymorphism relationship built with the help of XRMToolbox

Each Employee can have multiple salary info , But a single record which is isActive=1 will be the correct salary version for the employee

I need to display all the employee with active salary record, active ID Card, active LabourInfo in a Table control in Canvas app

There is no way to create such kind of view in Dataverse builtin View creation setup. So I tried to use XRMToolbox's FetchXML and chose the entities.

in FetchXML tool, the query returns correct results with all columns from Employee ,Salary, ID card and LabourInfo tables.

using Save As System View option in FetchXML tool, I saved this view in Dataverse.

But in Dataverse, the view doesn't show any records.(while FetchXML shows the correct results).. Dataverse says "There was an error retrieving your data. Please check your filters and try again."

Anyone built this kind of combined view in Canvas ? kindly guide me!

thank you for any help!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

RyanAutomates 139 on at

Like (1)

Report
Hi there,

You can definitely do what you're asking for in PowerFx, for example if you want to show a field called Value on the Salary table for the selected Employee in a control in your Canvas App:

LookUp(Salary, EmployeeID = ThisItem.EmployeeID).Value

Also if you want to format it into using £ with commas between the thousands and to 2 d.p. this example might be helpful:

Text(Value(First(Gallery1.AllItems).'Current Salary'), "[$-en-US] £#,###,##0.00")

I think that if you've built a non-standard relationship, some of the basic functionality built into Dataverse solutions might not work with it. I'm not sure why you need a polymorphism relationship?

I'm assuming Employee is the parent record which has the three tables you mentioned as its child records, hopefully that's right.

Model-Driven App-wise:

I think there might be other ways to achieve what you're wanting to do, depending on how much information you want to show and how.

Do you want to show certain fields from 1 of each of the child records Salary, Active ID Card and Labour Info? Or do you want to show information from multiple records?

In a Model-Driven App, you can look through the lookup field, but not back down to the child records usually, so depending on how many fields you want to show, I would have built for example, a field on the Employee called "Current Salary" or something like this, then use a classic workflow to update in real time, when a salary record is created or changed (on field of active) with conditions of:

Status = Active

Employee = (this Employee)

Active = 1

...to set the Current Salary field on Employee to the value of the Salary record. You could create 3 workflows in this way for each of the tables to populate the required fields on the Employee and show them in a view as you like.

You could do this in Power Automate also but it's probably more work and it only runs asynchronously.

If this isn't plausible because there are lots of fields, You could technically make a lookup field to each of the three tables, and populate that with a workflow whenever your event of create or change is triggered on each of those tables, which allows you to then build a view with the related records tables, and it will bring out the active child record's info as long as that lookup is populated.

Hope this helps! :)

Was this reply helpful? Yes No
Dej 6 on at

Like (0)

Report

Thank you for helping @RyanAutomates,

Employee Table can have multiple records in ID Card, Labour Card and Salary Info. But only one record from these three tables will be active.

in a Canvas App, I need to display all employee info in Table Format. Like (Name, Address, Basic(from Salary table), HRA (from salary table), CardNo(from ID card table, LabourId (from LabourCard table ) etc

I have many fields in Salary and Card tables. Creating fields for all these and updating those based on isActive, in employee table is a tedious job

Lookup can be useful for a showing a single record. But for displaying all 800+ employees in tabular format , lookup may be time consuming. So i chose fetchXML type of query using Xrmtoolbox. it displays the values correctly in XRMtoolbox , But if i save that as a dataverse view , the same view not working in Dataverse.

I tried using Power automate with fetchXML Query also, no success. Kindly suggest any possibilities.

Thank you

Was this reply helpful? Yes No
RyanAutomates 139 on at

Like (1)

Report

Hi Dej,

I get what you're saying, but the way you're trying to approach this will not work in Dataverse. You cannot query child records and bring out their fields into a Dataverse View.

The only way to "cheat" and achieve this is to create a setup as I mentioned by either automating duplicate fields on the Employee or bringing the required related record fields out to build a view through lookup fields pointing at the active child records.

You can use the lookup field in a gallery to bring it out for all records. In a Canvas App, you can create a gallery for Employees (rows), and create field controls (each column) where their item/value uses the lookup function for the related record field you want to display there and create a table in the gallery like this. That way the gallery can show your 800+ employees and their related child record fields. This method is only as time consuming as the time it takes to build how you'd want a single row to look like.

Hope this helps! :)

Was this reply helpful? Yes No
Dej 6 on at

Like (0)

Report

Hi Ryan,

Thanks for your time.

in Dataverse. You cannot query child records and bring out their fields into a Dataverse View.

it is really disappointing, Expecting Advanced View Building Feature in Dataverse.

bringing the required related record fields out to build a view through lookup fields pointing at the active child records.

Can you kindly guide about creating lookup fields for displaying in tabular format in Modern Table control in canvas app ?

Thanks again

Was this reply helpful? Yes No