cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

I have a large catalog of products that is stored in SQL that is used throught the app. However, I need to store some of the data for these products in an Excel table to make changes easier and quicker for the support folks. 

 

I'm finding that only the top of the list (may be 500 or so items I would guess) can be referenced using the LookUp function. I'm using a unique id (not duplicated in the target Excel table to reference with the following formula: 

 

LookUp(Catalog_1,  ItemCode in Gallery.Selected.Item, ItemImage)

 

Does anyone have experience with this issue?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User II
Dual Super User II

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

@AFWright 

 

I haven't done extensive testing, but for a static table of information that doesn't require frequent edits Importing the static Info from Excel can be a good choice. It doesn't seem to have an issue with the 500 record maximum limit since you are importing the table into local storage.  I created a test Excel file  with almost 700 records.  When I imported it into PowerApps I was able to access all the records in the data source. I was also able to filter it on an Id field I included without getting a delegation warning.

 

So within size constraints I would say it can be considered a best practice to load static information like product catalogs into memory by importing an Excel sheet.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

5 REPLIES 5
Highlighted
Dual Super User
Dual Super User

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

Hi @AFWright 

 

This is related to the delegation with respect to excel as a data source

 

You can increase the limit to max 2k

 

Capture.PNG

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Highlighted
Helper II
Helper II

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

If I wanted to keep this data in an excel file, would the best option be to import the excel file as static data? Would that make a difference?

Highlighted
Dual Super User
Dual Super User

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

@AFWright 

 

Good question - lets check with @Pstork1 

Highlighted
Dual Super User II
Dual Super User II

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

@AFWright 

 

I haven't done extensive testing, but for a static table of information that doesn't require frequent edits Importing the static Info from Excel can be a good choice. It doesn't seem to have an issue with the 500 record maximum limit since you are importing the table into local storage.  I created a test Excel file  with almost 700 records.  When I imported it into PowerApps I was able to access all the records in the data source. I was also able to filter it on an Id field I included without getting a delegation warning.

 

So within size constraints I would say it can be considered a best practice to load static information like product catalogs into memory by importing an Excel sheet.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Helper II
Helper II

Re: Best Practices Question - Referencing an Excel Table from Powerapps using LookUp

I tested it and it works perfectly and is a lot faster than pinging onedrive using LookUp.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,016)