cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Axal
Post Prodigy
Post Prodigy

Does Lookup function return all fields ?

Hello all,

 

Asking for some confirmation. When we use Lookup function, for example like this :

Axal_0-1628063404537.png

That is my custom table in CDS, I want to put the result to a variable named CurrentExpense, and the search value is coming from ModelDrivenFormIntegration. But that integration is not the problem, at least I guess so.

 

Problem is if I check the quick result, it looks like the variable only contain 2 field while I have more than 2 fields in that table ->

Axal_1-1628063727043.png

 

Anyone can help me to understand more on this ?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

Hi @Axal 

The cause of this behaviour is 'explicit column selection' - a performance optimisation feature where Power Apps retrieves only the field values that it thinks it needs (which is a good thing).

There are more details in my post here.

http://powerappsguide.com/blog/post/how-does-the-explicit-column-selection-setting-work

 

Power Apps retrieves the record from your CDS/Dataverse data when you call Set. At this point, it can't project that later on, you'll want to retrieve the description/'Next Linenum' fields so it doesn't retrieve these values.

If you were to set your label text to the following instead, this should return the 'Next Linenum' field:

With({rec:
      LookUp(Expenses, 'Journal ID' = [@ModelDrivenFormIntegration].Item.'Journal ID')
      },
      rec.'Journal ID' & rec.'Next Linenum'
)

Returning to your initial formula, a hack I sometimes use is to trick Power Apps into retrieving the required fields by adding dummy conditions to Lookup. For example, this should make the 'Next Linenum' and Description fields available in your CurrentExpense variable.

 

Set(CurrentExpense,
    LookUp(Expenses,
          'Journal ID' = [@ModelDrivenFormIntegration].Item.'Journal ID'
          And 'Next Linenum' = 'Next Linenum'
          And Description = Description
    )
)

The alternate fix is instead of calling LookUp, you would call First/Filter and explicitly specify the columns that you want to return with ShowColumns.

View solution in original post

10 REPLIES 10
Nogueira1306
Resident Rockstar
Resident Rockstar

Yes, it returns the field. It is like a GalleryItem.
LookUp returns a item.

So, if you write: CurrenteExpense.Name it will return the name of item that you filtered

Hi thanks,

 

Then I wondering why it is only showing 2 fields while there are more.

I've tried by calling explicitly to a label.text like this ->

Axal_0-1628065073593.png

It is no error though, the formula validated, the intellisense at first didn't give me the selection of other fields but I can type the field name and then it will appear in the selection, like for example that "company" field.

And other field like this :

Axal_1-1628065280791.png

It is just another field on that table that not appear in the selection, but I can still type, no error for the formula, but then also will only give me blank while actually there is a value in database.

 

Btw, for the label I tested, for those fields, which exist but not appeared in my table variable, it will return blank in that text. So only those 2 fields will display the correct value.

 

Is my CDS table corrupted ? I should create a new one ?

 

Thanks

 

 

Nogueira1306
Resident Rockstar
Resident Rockstar

I think that "Next Lineum" is blank. Because, if it that field did not exist, that would give you a error and it did not give you a error...

 

Please check this link and check if your lookUp is correct

https://docs.microsoft.com/pt-pt/powerapps/maker/canvas-apps/functions/function-filter-lookup 

 

After, if it does not work, you can always try with the filter. You just have to make sure that you are looking for unique values

Hi @Nogueira1306 

 

No, it's no blank. As the example on my picture, it is Journal id 1079, and here is the data ->

Axal_2-1628066756203.png

 

I took another example, from that raw data grid, we can see "Description", this too not appeared in the quick view of the variable, and so it will also return blank when I tested it in a label.text ->

Axal_1-1628066707553.png

 

However let me take a look on the docs also try to use filter instead.

 

Thanks

Axal
Post Prodigy
Post Prodigy

Yeah, it is worse if using filter, the quick view will show only 2 fields has value :

Axal_0-1628068884769.png

 

Nogueira1306
Resident Rockstar
Resident Rockstar

It is not worth.. That proves that you dont have any value in those fiels, that they are blank

Hi,

but, the raw data from the table tells the opposites. This is what confuse me, since I know there are value on most of the fields, not to mentioned again we did see those values in Data tab.

 

What's getting more weird is, when I run this app, and make sure the tmpTable syntax executed, then back in PowerAppStudio editor, I'm trying to add label to display what inside the tmpTable variable, but it give me error ->

Axal_0-1628069357680.png

Even though it is referring to field that all this time has value. It looks like the variable has no field recognized (but the intellisense this time show correctly with all the field selection)

It's getting weirder and weirder.

 

 

Nogueira1306
Resident Rockstar
Resident Rockstar

For taht, you need to convert it to text or value

But I agrre. If you are telling me that you have values and the dont show up, yes, it is weird

timl
Super User
Super User

Hi @Axal 

The cause of this behaviour is 'explicit column selection' - a performance optimisation feature where Power Apps retrieves only the field values that it thinks it needs (which is a good thing).

There are more details in my post here.

http://powerappsguide.com/blog/post/how-does-the-explicit-column-selection-setting-work

 

Power Apps retrieves the record from your CDS/Dataverse data when you call Set. At this point, it can't project that later on, you'll want to retrieve the description/'Next Linenum' fields so it doesn't retrieve these values.

If you were to set your label text to the following instead, this should return the 'Next Linenum' field:

With({rec:
      LookUp(Expenses, 'Journal ID' = [@ModelDrivenFormIntegration].Item.'Journal ID')
      },
      rec.'Journal ID' & rec.'Next Linenum'
)

Returning to your initial formula, a hack I sometimes use is to trick Power Apps into retrieving the required fields by adding dummy conditions to Lookup. For example, this should make the 'Next Linenum' and Description fields available in your CurrentExpense variable.

 

Set(CurrentExpense,
    LookUp(Expenses,
          'Journal ID' = [@ModelDrivenFormIntegration].Item.'Journal ID'
          And 'Next Linenum' = 'Next Linenum'
          And Description = Description
    )
)

The alternate fix is instead of calling LookUp, you would call First/Filter and explicitly specify the columns that you want to return with ShowColumns.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Top Kudoed Authors
Users online (1,047)