cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulD1
Super User
Super User

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? 

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. 

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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