cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TerryHughes
Frequent Visitor

Power App If Lookup(ExternalDataSource) = NULL - 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 SQL Product does not have Excel 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 the SQL Product has Excel notes and the user clicks on the NEW button, then PowerApps adds the line to the Excel spreadsheet, causing duplicate Product ID lines in Excel and thus the PowerBi report cannot refresh. It breaks. Again, if I try to add a 
If(IsEmpty(LookUp(ExcelSpreadsheet, Column=Gallery1.Selected.PowerBiValue)),... to check to make sure that it is NOT in the Excel spreadsheet.... Once PowerApps hit's a NULL then it kicks the same "...eq null" is not supported Server Response.


I have tried IsEmpty(), IsBlank(), IsBlankOrError(), with or without preceding IF clause, in just about everything I can think of from the Button.Visible to keep it from showing, to the SubmitForm function to.... Since the Excel spreadsheet will eventually always throw a NULL since not every item in the SQL query is already in the Excel form and I need to be able to add to the Excel sheet without causing duplicate entries: POWER APPS BREAKS.

 

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 external data sources and 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 the "eq NULL" breaks PowerApps before the handler can even have a chance to do anything???

 

Is it possible to read a NULL value from an external data source without Power Apps breaking or not? 

IF not then why not?

 

1 ACCEPTED SOLUTION

Accepted Solutions
TerryHughes
Frequent Visitor

CREATIVE 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)

View solution in original post

1 REPLY 1
TerryHughes
Frequent Visitor

CREATIVE 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)

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (4,346)