Lookup on SQL Server

[jmwiplow]

Hi,

I'm new to this so bear with me please

I've got a SQL server, and there's say 2 related tables, 'Opportunities', and 'Owners'.

Opportunities has a lookup of an Owner. In Access Web App (the current tool), we look up using the Display Name field, although obviously it's the Owner ID which is the key.

When I connect to the Opportunities table in Power Apps, it's bringing through the Owner lookup field in as an integer, and showing the Owner ID field - fine. But how can I cange this to show the Owner Display Name field in both the details screen and in an edit screen? And do I have to bring in the Owner table also as a Data Source?

Thanks

Joe 

[v-micsh-msft]

Hi Joe,

Have you checked the article below regarding the lookup field?

Please follow the article and see if you could change the lookup display value:

Introducing support for lookups and a new sample app

Follow the part:

"Let's make the CurrentOwner field display Name rather than JobTitle."

Part.

Please post back if you need any further assistance.

Regards

[Meneghino · Accepted solution]

Hi @jmwiplow

Here is the best way to do what you want to do, assuming owners has 500 or fewer records:

1. Have an initial screen in the app to welcome the user, with no functionality except a button to proceed
   This is so that you can cache any lookup tables, this improves performance tremendously
2. Set the Disabled property of the button to be:
   

   IsEmpty('[Access].[Owners]')

   This is so that you give the PowerApp time to connect the data source
3. Set the OnSelect property of the button to be:
   

   ClearCollect(CachedOwners, '[Access].[Owners]'); Navigate(MyFirstScreen, None)

   This is so that you cache the lookup data, may take a few seconds but it is worth it
4. Then, anywhere you need to display the owner name, you can look up to the cached data instead of needing to call the server.  This is most effective in a gallery, where otherwise the server would be called for every line. For example a text box text property would be set to:
   

   LookUp(CachedOwners, ThisItem.OwnerLookupField, DisplayNameField)

I am a heavy user of Access Web Apps and with a few tricks PowerApps can do great things with AWA as a back-end.

Here are a few things you should keep in mind, which are true at the time of writing:

1) It is far more efficient to cache lookup tables locally with a collection

2) If the lookup table has > 500 records you can still cache it by using for example:

ClearCollect(CachedInstruments, Sort('[Access].[Instruments]', ID, Ascending));
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1000,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
)))

3) Date fields need to be of the DateTime sub-type otherwise if they are just date they will show no data in PowerApps.  You can change the data type with no loss of information

4) Date fields store data in server time so you need to take care with time zone conversion.  I find it much easier to create a calcualted colum in AWA that stores the date as an integer of the form yyyymmdd

These are just a few things from experience of using Access Web Apps in PowerApps, please let me know how you get on.

[GenoRock]

The solution points out that converting Date fields to DateTime format loses nothing... this isn't true if a date being converted is quite old... DateTime values don't go back as far as Date values.

As a long-time Access developer, I am looking for a more elegant solution to recordsets that are larger than 500 Records.  Access simply manages this perfectly along with total integration directly with SQL server.  That is one key reason that we spend a lot of money on these products.  Does webification automatically result in clunky code options?.. This "codeless development " is starting to feel like a lot of syntactical coding - which ultimately is fine when it is well thought out and robust such as the well-worn VBA.

Also, the provided solution appears to deal with recordsets in chunks pf 500?... need to add enough code to account for some upper limit? (1500 in the sample?) 

All IMO.

[Meneghino]

Hi @GenoRock, all valid points.  I particularly agree that calling PowerApps "no code" is misleading and could lead to poorly thought out code.  In any case, a lot of the issues are being ironed out, however not at lightning pace.

Now that AWA is being retired I am migrating to Azure SQL Database, and hopefully PowerApps will integrate well with this over time.  Currently there are a few major stumbling blocks, but hopefully they will be resolved:

-No way to patch null values (being resolved)

-No support for temporal tables

-Only tables, no views

-Date columns poorly supported (at least that was the case 3 weeks ago)

Best regards.