cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
radtadBrad
Level: Powered On

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
Level 10

Re: Access Web Apps lookup values in PowerApps

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.

LucianB
Level: Powered On

Re: Access Web Apps lookup values in PowerApps

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

Highlighted
Meneghino
Level 10

Re: Access Web Apps lookup values in PowerApps

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/

LucianB
Level: Powered On

Re: Access Web Apps lookup values in PowerApps

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.

 

Community Support Team
Community Support Team

Re: Access Web Apps lookup values in PowerApps

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.
LucianB
Level: Powered On

Re: Access Web Apps lookup values in PowerApps

Did not tried yet Smiley Happy

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 49 members 4,582 guests
Please welcome our newest community members: