cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (41,814)