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

Using Show Columns with CDS LookUp type fields

I have a CDS based App and I am trying to fetch the results of a single row into a variable (so I don't need multiple lookups for each of the relevant fields as that may be causing performance issues).

I have 'Explicit Column Selection' turned ON. This bit me before back in November when the behaviour was changed (for existing published Apps!) such that it stopped returning fields that are only referenced as a dot property of a variable assigned via a LookUp. However, I want it turned on as in January a change was made to the CDS Appointment entity (MS deprecated some fields) that broke our Apps and the quickest fix was to turn on 'Explicit Column Selection' so the App stopped trying to fetch these never referenced, no long existing fields.

So, my issue is, I have 'Explicit Column Selection' turned ON so the fields I need are not being returned from the LookUp:

 

Set(
    varPreInspection1,
    LookUp(
        'Pre Inspections',
        Job.Job = varJob.Job && Appointment.Appointment = varAppointment.Appointment
    )
);

 

 To try to fix this, I thought I would use a First/Filter combination and use ShowColumns to make it clear the columns that should be returned, like so:

 

Set(
    varPreInspection1,
    First(
        
        Filter(
            ShowColumns('Pre Inspections',"dsl_job", "ds_appointment", "dsl_apillars" ),
            dsl_job = varJob.Job && ds_appointment = varAppointment.Appointment
    )
);

 

So, the first issue is I have to use the internal field names (I really hate how inconsistent the field naming is with CDS), the second issue is that PowerApps rejects any reference to a LookUp field type (Job and Appointment in this case) and says the fields do not exist.

Is this yet another limitation within CDS - ShowColumns cannot return LookUp fields? 

2 REPLIES 2
jlindstrom
Super User
Super User

so one of the reasons to use the schema name is because CDS is a multi-language system--so the same field might have a different label depending on the user's language setting. where the schema name never changes

 

edit--Show columns and none of the time shaping expressions are delegatable. and even worse, you won't get the delegation warning. So I would not use ShowColumns

 

 

Hi @jlindstrom 

 

>>so one of the reasons to use the schema name is because CDS is a multi-language system--so the same field might have a different label depending on the user's language setting. where the schema name never changes

I can see that, but it is just really inconsistent that in some places the friendly-name is used (and suggested by the intellisense) and in others the schema-name must be used - and that is not suggested/listed so you have to delve into the entity to find it.

 

It seems that recently, ShowColumns will delegate with CDS *if* explicit column selection is turned on (according to some tests I made in Performance Monitor).

 

My issue here was that Explicit Column selection (without a ShowColumns) was excluding columns I needed. When you assign the result of a data-call to a collection or variable, PowerApps makes a bad 'guess' at which columns you want, so I was trying to enforce the return of columns I needed, only to discover that you cannot specify a CDS lookup column using ShowColumns 😞 

IIRC we abandoned trying to assign records/tables to variables and took a different approach.

 

 

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (57,767)