'PowerApps -If LookUp() is NULL with external data source - Power APPS BREAKS
I have a PowerBi report with several data sources, SQL query and Excel connector.
The SQL data data on our Products whereas the Excel is living data on salesperson notes on said products.
The PowerBI has a PowerApp visualization. The Power App has 3 pages:
Page 1 is landing page that Gallery displays data from PowerBi
Page 2 is a form edit page to edit notes in the Excel spreadsheet that we know exists because we saw those notes in the PowerBi report
Page 3 is a form edit page to add new notes in the Excel spreadsheet that we know we have to add because we did not see them in the PBI report
On Page 1 are two buttons, "Edit" and "New" that navigates to the proper page.
Now here the WTF moment
IF the Product has notes and the user clicks on the NEW button, then PowerApps adds the line to the Excel spreadsheet, causing duplicates and thus the PowerBi report cannot refresh. It breaks.
If the Product does not have notes and the user clicks on the EDIT button then PowerApps does a LookUp(ExcelSpreadsheet, Column=Gallery1.Selected.PowerBiValue which results in a NULL value and thus breaks with a dreaded server response: expression "... eq null" is not supported.
If I try to add the 'IsEmpty()' to preemptive counter it, the 'Lookup()' is run first thus breaking before it gets to the 'IsEmpty()', same goes with 'IsBlank', 'IsBlankOrError', and 'IsError'.
I have Googled the living %(#$* out of this and there seems to be nothing on it, as this is in direct relation to 'LookUp' on the Excel Spreadsheet not a dropdown or in app text box that other people have issues with. How is it that you can't even use the error handler 'IsError()' as it breaks before the handler can even have a chance to do anything.
If I use LookUp(ExcelSpreadsheet, Column=Gallery1.Selected.PowerBiValue) in any way, shape, or form, with or without If clauses, because it will eventually produce a NULL result, Power Apps breaks
How do I read null values from a data source?
Solution 1:[1]
SOLUTION:
Since the "living data" Excel spreadsheet is also in the PowerBi report, I have PowerBi send the living data values along with the SQL queried product values to the PowerApp. Since the NULL is now a sent from PowerBi instead of queried from Excel within PowerApps, I can have 1 button that's OnSelect property reads:
IF(IsBlankorError(Gallery1.Selected.LivingDataNotes),Navigate(NewNotes),Navigate(EditNotes)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | T. Hughes |