Access Web Apps lookup values in PowerApps

[radtadBrad]

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!  

[Meneghino]

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]

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]

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]

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.

[v-micsh-msft]

Hi @LucianB,

What is your current situation?

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

Regards

[LucianB]

Did not tried yet 🙂