HI all,
I've created a powrapp with a flow that is being used to pass a SQL query to an Azure SQL database, this works fine and returns data, however if the query doesn't return any data I get an error, this occurs every time the query returns no results. Any thoughts on how I can stop this from popping up, as there is no issue, just no data to be shown
Hi @AlanCampbell,
Could you please share a bit more about your scenario?
Further, could you please show more details about the formula within your app?
If you want to stop this error message from popping up, I think the IfError() function could achieve your needs.
I have made a test on my side, please take a try with the following workaround:
Set the OnSelect property of the Button control to following formula:
IfError(
SQLQueryMemberActivity.Run(),
Notify("No data returned!",NotificationType.Warning)
)
Note: If you want to use IfError function within your app, you must turn on/enable the "Formula-leave error management" option within the Advanced Settings of App settings firstly (App settings-> Advanced Settings-> "Formula-leave error management").
More details about the IfError function in PowerApps, please check the following article:
Best regards,
Kris
I have the same problem. If the Flow/SQL is returning an empty result, a red error popup is shown on the mobile devices, however not in the browser of a desktop/laptop.
The IfError solution is working on the desktop\browser but not on the mobile apps.
The solution is to make a query that is always returning at least one record. You have to add a 'dummy' record and remove that record from the local collection. You can do this with T-SQL command: UNION ALL
Example:
This is the sql query that is put in the label LabelSQLQuery:
"SELECT z.Project as Project,sum(z.aantaluur) as toturen
FROM
(SELECT d.Project,d.Aantaluur FROM doorgeboekteurenperpersoonpowerapps as d WHERE (d.Jaar+d.Week/100) >= " & (TextJaarStartFlow.Text) & "." & Left(Text(100*Value(TextWeekStartFlow.Text)/100;"[$-nl-NL]0#");2) & " And (d.Jaar+d.Week/100) <= " & TextJaarEindFlow.Text & "." & Left(Text(100*Value(TextWeekEindFlow.Text)/100;"[$-nl-NL]0#");2) & " And d.Persoon = '" & DropdownPersoonPeriodeFlow.Selected.Persoon
& "' UNION ALL SELECT 'dummy project' ,0) as z"
& " group by z.Project order by z.Project"
The part UNION ALL SELECT 'dummy project' ,0 adds a “ dummy” record to the result. So there is always at least 1 record returned by the sql server.
The flow is called from a button with the code:
ClearCollect(queryResults2;GetSQLQuery.Run(LabelSQLQuery.Text));;Remove( queryResults2;First( Filter ( queryResults2; Project="dummy project" ) ) )
The dummy record is removed from the collection with :
Remove( queryResults2;First( Filter ( queryResults2; Project="dummy project" ) ) )
If the dummy record is the only record from the query you see the record for a moment in the gallery before it is deleted. If there are more records, you cannot notice this at all.
I hope this helps and if someone has a more elegant solution, please let me know
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
203 | |
171 | |
63 | |
32 | |
31 |
User | Count |
---|---|
338 | |
271 | |
105 | |
71 | |
56 |