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

[PaulD1]

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.

[Drrickryp]

Hi @PaulD1 

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

[PaulD1]

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).

[Drrickryp · Accepted solution]

@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.  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]

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.

[Drrickryp]

@PaulD1 

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