Retrieving polymorphic records using 'In' operator

[pluthra]

For my custom entity record, I'm trying to fetch related Contact records linked through Connections entity.

CustomEntity <--> Connections <--> Contacts

Currently using ForAll to fetch data which works, but I want to use the 'In' operator to improve performance (PowerApps experimental feature, currently at version 3.20051.17).

Here's the code for the currently working approach:

//Fetch connections for my selectedRecord

ClearCollect(
      ConnectionsForEng,
      Filter(
               Connections,
               'Connected From' = selectedRecord && Status = 'Status (Connections)'.Active
              )
      );

//then fetch Contacts using ForAll:

ClearCollect(
         ContactsForEng,
                 ForAll(
                           ConnectionsForEng,
                           AsType(
                                        'Connected To',
                                        [@Contacts]
                                       )
                             )
                 );

Have been playing around with disambiguation operator and ShowColumns for hours now, but the field "_record2id_value" in Connections does not give me a straight up collection of GUIDs for this record2id column.

[AMoon]

Hi @pluthra, I tried quickly copying what you are trying to do. I did not use the IN operator, but I'm not seeing any delegation issues with this formula at the moment. Check out my formula below - I combine your two steps into one. If all you need is the GUIDs then for simplicity you can just add .Contact to the end of your AsType() to simply return a table of GUID values.  Let me know if this fits what you are looking for? 

ClearCollect(ContactsForEng, ForAll(Filter(Connections, 'Connected From' = selectedRecord, Status = 'Status (Connections)'.Active), AsType('Connected To', Contacts).Contact))
This formula returns a one column table with the GUIDs. 

[pluthra]

Hi @AMoon thanks for the quick response, the query you have still uses the ForAll operator, causing one CDS call per Connection record to get the corresponding Contact record. This causes performance issues when the count of connections is say 15+ as the app fires 15 calls to populate the list, my connection count can go up to a higher number.

My goal is to get all contacts for the Connections in my collection in a single CDS API call from my powerapp, hence looking for a way by using the In operator which is now delegable as an experimental feature.

[AMoon]

Hi @pluthra, I believe that due to the fact that the linked Connected From and To records are stored as reference objects by Canvas Apps - You will still have to make a separate call for each connection record in order to get the GUID value of the Contact. So even if you switch to using the In operator you have to first fix that issue. Let me know if this makes sense or if you know something I don't! Also - would like to see what you have tried with the In operator if you have tried something there.

[pluthra]

@AMoon  yes that's precisely right, the Connected To field seems to be stored as an inaccessible object (in the app maker) , even though I took a fiddler trace and saw that the actual GUID values were being retrieved in the first API call that goes out

Shouldn't the GUIDs be available to use as they are already retrieved?

Is my best option to continue using ForAll? 

[Anonymous]

Connections is one of those Out of the Box options that is very, very, very specific to it's design.

In Enterprise engagements rhen we really want to pivot we tend to use a Configured Many to Many relationship where we get to manage and (report on easily) the intersecting entity. 

It is also a critical variable when working with 10s or 100s of integrations where data has to be passed to systems that are configured differently. 

I suspect this doesn't answer your question 😞 , but if you get the chance to do more architecture and design moving forward consider additional options in architecting the system. 

@microsoft Note: Connections Fits/Gaps is most likely needed documentation to add to the huge and wonderful DOCS library.