cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
radtadBrad
Helper I
Helper I

Access Web Apps lookup values in PowerApps

I want to use Access Web Apps to input and view data, but I also want to be able to view and edit the same data from PowerApps.  Connecting to the Access SQL server works fine, however the lookup values in the WebApp only show up as numbers in PowerApps.  I like the options and forms that are available in Access Web Apps, but I really want to be able to easily integrate the same data with multiple PowerApps.  I'm new to both, so maybe there is a simple fix that I don't know about.  Any feedback would be greatly appreciated. Thanks!  

6 REPLIES 6
Meneghino
Community Champion
Community Champion

Hi @radtadBrad

Let's say that in AWA (Access Web App) you have TableCities that has a lookup column to TableCountries.

Let's call this column CountryOfCity.  When seen from PowerApps this column will contain just an integer which is the ID of TableCountries.

Once you understand this, then everything else is relatively simple.

 

Let's say you display your cities in a gallery, then add two text boxes:

one with Text = ThisItem.CountryOfCity

one with Text = LookUp(TableCountries, ID =ThisItem.CountryOfCity, CityName)  (where CityName is a column in TableCountries)

 

The first will show you the number that you are seeing now, the second will show you the name or any other field you want to show from TableCountries.

 

Depending on the size of your tables and connections, this may be slow, but please try it first to see if you get it to work, then we will worry about making if faster by storing TableCountries in a local collection.

Hi Meneghino,

 

Depending on the size of your tables and connections, this may be slow, but please try it first to see if you get it to work, then we will worry about making if faster by storing TableCountries in a local collection.

 

That's sound interesting,as I am developing an app with many lookup fields (backend is SQL server) and also I am using Access web app.

Can you give more details about local collecction?

Thanks.

Lucian

Meneghino
Community Champion
Community Champion

Hi @LucianB

Yes, in PowerApps it is quite simple.  In the example above I would cache the Countries table in local memory and then do the lookup to this local table.

 

I am sure there are many ways of doing this, but this is what I now use:

  • Do not have any functionality on the landing screen (i.e. the first screen) except welcoming the user
  • Set the OnHidden property of the landing screen to ClearCollect(CachedCountries, TableCountries)
  • Substitute CachedCountries for TableCountries in all the LookUp expressions

The limitation is that TableCountries should have a maximum of 500 rows, otherwise only the first 500 will be cached.  But for lookups this is normally the case.  Also, the first bullet point above is important because the ClearCollect will not work with the OnVisible property of the landing screen, or at least not reliably, because it takes a few seconds for the data sources to become available.  As an extra precaution, I set the disabled property for the control to navigate away from the landing screen to: IsEmpty(TableCountries).  This basically makes the app wait until the connection to the data source is established, otherwise you risk collecting nothing.

 

Of course, if your app actually modifies TableCountries (i.e. you have a screen for adding new countires) then you need to recache the edited table with the same ClearCollect expression after each edit operation.

 

The above approach makes the apps run >10 times faster in my experience.

 

Please let me know your experience with this.

 

Reference: https://powerapps.microsoft.com/en-us/tutorials/function-clear-collect-clearcollect/

Hi Meneghino,

I will give it a try, thank you very much!

Looks ok for me. I definitely have more than 500 records, let's see how it will go.

 

Hi @LucianB,

 

What is your current situation?

 

If you need any further assistance on this, please feel free to post back.

Regards

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Did not tried yet 🙂

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

secondImage

Demo Extravaganza Winner Announcement

Please join us on Wednesday, July 21st at 8a PDT. We will be announcing the Winners of the Demo Extravaganza!

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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,701)