cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulD1
Community Champion
Community Champion

CDS + Canvas: Is it possible to use ShowColumns with columns of data type 'Lookup'?

Just doing a bit of experimenting and discovered that ShowColumns (not normally 'delegable') appears to 'delegate' in CDS when Explicit Column Selection is turned on (i.e. instead of PowerApps pulling back all columns from CDS and then discarding what you don't want, CDS appears to return only the desired columns). This could be used to boost App performance significantly in some scenarios, especially given that the base CDS entities have so many columns (CDS not being a proper relational model).

With Explicit column selection turned on, the following statement (where Jobs is an entity):

 

UpdateContext({conTest1: FirstN(ShowColumns(Jobs,"dsl_claimnumber"),100)});

 

operates 23x faster (according to Performance Monitor) than

 

UpdateContext({conTest2: FirstN(Jobs,100)});

 

I would like to use ShowColumns to speed up my Apps - however, ShowColumns does not appear to accept as an argument any column which is of the CDS datatype 'Lookup'.

Anyone know if ShowColumns can be used to limit the columns returned for a CDS entity where one (or more) of the columns is of type Lookup?

For anyone trying this out, keep in mind that you have to specify the 'internal' column names as the arguments for ShowColumns, the 'friendly' names are not supported - e.g. you must use "dsl_claimnumber" and not 'Claim Number', and it appears there is no intellisense to help out.

 

1 ACCEPTED SOLUTION

Accepted Solutions

@PaulD1 

Maybe I'm not understanding the problem but in CDS you can select an entity and then create as many views as you want.Annotation 2020-06-29 074844.pngviews.png  Then in the Canvas app, you have the option to select that view.  It's beneath the Data source choice on the right side column and it automatically creates the filter.  The view created from the entity can contain only the columns you want, with complex filters and sorted..  Some of the columns can be from related entities as well. 

View solution in original post

5 REPLIES 5
Drrickryp
Super User
Super User

Hi @PaulD1 

Could you use a View to pre-filter and pre-select your columns? 

PaulD1
Community Champion
Community Champion

Hi @Drrickryp 

That is what I do when I'm using SQL, but for CDS the only thing I appear to be able to connect to is the entity - not a view of the entity (restricted to just the useful columns).

@PaulD1 

Maybe I'm not understanding the problem but in CDS you can select an entity and then create as many views as you want.Annotation 2020-06-29 074844.pngviews.png  Then in the Canvas app, you have the option to select that view.  It's beneath the Data source choice on the right side column and it automatically creates the filter.  The view created from the entity can contain only the columns you want, with complex filters and sorted..  Some of the columns can be from related entities as well. 

PaulD1
Community Champion
Community Champion

Thanks @Drrickryp 

Wasn't aware of that 'Views' option.

Not as handy as just being able to specify the columns you want to retrieve from within the App (with ShowColumns if it worked for lookup fields) but should help with improving performance by creating some views that limit the number of columns returned.

@PaulD1 

Could you test it and see how it compares in your performance analysis please?

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,912)