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 II
Super User II

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

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