cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Serviced2000
Helper III
Helper III

Building Collection with Polymorphic Fields

Hi Everyone,

 

I've tried almost everything I can think of to get my app working. I need to bring together data from various CDS / Dataverse tables to be able to display and filter a gallery based on those fields. I have two tables and a choice that I need to bring together somehow, but the tables are related by a polymorphic field called 'Regarding'.

 

Table1

Placement Approvals

 

Table2

Placement

 

Choice 

Job Type

 

If I use the following function on a text label in the gallery 

 

LookUp('Placements', AsType(ThisItem.Regarding, Placements).Placement = Placement, 'Job Type').Name

 

I can bring the text from the Job Type choice into the Gallery, but I can't filter on it (or can I??).

 

So I wanted to create a collection that would bring all the data items together. However as the two tables are joined using 'Regarding'  I need to traverse the relationship. This seems easy enough, but if I do this :

 

Collect('Placement Approvals', "PlacementType", LookUp('Placements', Placement = AsType('Placement Approvals'.Regarding, Placements).Placement, 'Job Type').Name)

 

I'm getting 'AsType' errors

Serviced2000_0-1628042479213.png

I must be doing something wrong here. It's hard to believe I can't bring this data together due to the architecture of Dynamics?

1 ACCEPTED SOLUTION

Accepted Solutions
Serviced2000
Helper III
Helper III

Thanks @PaulD1,

 

That last bit makes me feel a bit better about all the time I've invested in trying to get this working. I may have resolved the issue by approaching it from the opposite direction, that is I created a view of placements with outstanding approvals in CDS and used that to filter the initial table I use in the Canvas app. Then, I found the most recent Approval and brought data in from that table as I only need one or two items. Then I can Patch that approval record to complete any user actions. I didn't go this way initially as I thought it would be much harder to do, but it only took me 2 hours to more or less complete the app after that - go figure! 🙂

View solution in original post

6 REPLIES 6
v-xiaochen-msft
Community Support
Community Support

Hi @Serviced2000 ,

 

Could you tell me:

1\ What's 'Placement Approvals'?

Is it a table in dataverse or a collection?

 

2\ In your case, how many tables did you use? What are their names? 

Could you provide the relationship between table and table? 

 

Best Regards,
Wearsky

Serviced2000
Helper III
Helper III

Hi Wearsky,

 

Placement Approvals is a dataverse table. There are 2 tables involved - Placement Approvals and Placements - there is a field called 'Regarding' which is a polymorphic field that connects the two. One placement can have many approvals.

v-xiaochen-msft
Community Support
Community Support

Hi @Serviced2000 ,

 

Does 'Placement Approvals' table has a 'Regarding' field ? 

vxiaochenmsft_0-1628060711680.png

Is my assumption correct? 

 

Best Regards,

Wearsky

Serviced2000
Helper III
Helper III

Almost - the Placement Approvals table does have a 'Regarding' which governs the relationship between it and the Placements table, but it doesn't relate to itself.

PaulD1
Super User
Super User

Just a thought, could you try this in two steps - Collect the 'base data' then use ForAll to iterate over the rows and use IsType and AsType to fetch the relevant related data?

AsType and IsType functions in Power Apps - Power Apps | Microsoft Docs

Not very efficient so hopefully you can keep the number of records in the collection small (in the tens rather than hundreds).

The other approach I have frequently seen used is to introduce redundant references, e.g. have some process in Dynamics to copy the data from the related tables into the parent table. Obviously this goes against the concept of normalisation and introduces fresh problems of trying to keep the data in sync, but that seems to be the price you pay for working with Dataverse/Dynamics.

 

>>I must be doing something wrong here. It's hard to believe I can't bring this data together due to the architecture of Dynamics?

Believe it - IMHO multi-value fields, polymporphic relationships and option sets all cause lots of problems for Canvas Power Apps as they are not valid relational concepts and are not well supported or easy to figure out. Things that would take me minutes to sort out with SQL take hours and many work-arounds with Dynamics/Dataverse.

Serviced2000
Helper III
Helper III

Thanks @PaulD1,

 

That last bit makes me feel a bit better about all the time I've invested in trying to get this working. I may have resolved the issue by approaching it from the opposite direction, that is I created a view of placements with outstanding approvals in CDS and used that to filter the initial table I use in the Canvas app. Then, I found the most recent Approval and brought data in from that table as I only need one or two items. Then I can Patch that approval record to complete any user actions. I didn't go this way initially as I thought it would be much harder to do, but it only took me 2 hours to more or less complete the app after that - go figure! 🙂

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,490)