I have a need to email a weekly list of new items added to a table in SQL. I have written a query in flow but some weeks there are no new items, which causes the flow the fail.
How can I add a condition which sends the query results if there are rows returned, but send a different email when the query result is null? I have tried length(body('Get_items')?['value']) greater than 0, but this fails when the query is null.
Hi @Graham
just tried with length(body('Get_items')?['value']) and it worked
can you post screenshot of output from sql query when flow fails
Hi @Graham ,
Could you provide screenshot of Flow configuration?
If you are using Get rows action, please refer to the following expression:
In the length() function we need to configure the name of the actual action and use an underscore instead of space.
Best Regards,
This is the error from flow;
This is the content;
The formula is:
Hi @Graham ,
Have you seen the execution results of Get List of Funds Requests action?
Best Regards,
The query results in no records being fetched, hence the 'Null'. That is what I expected, as this relates to a weekly flow that looks for records added during the previous weeks. Some weeks there will be no new records, so I need flow to do something else in the knowledge there are no results.
Hi @Graham ,
Could you provide a screenshot of Get List of Funds Requests action?
We need to provide a viable expression based on the specific execution results.
Best Regards,
Hi,
It is a simple SELECT query - I would rather not share the exact contents, for obvious reasons, but here is something similar;
SELECT
dbo.Incidents.IncidentID,
dbo.Incidents.Reference,
dbo.Funds.RequestID,
dbo.Funds.RequestAmount,
FORMAT(dbo.Funds.RequestDate, 'dd/MM/yyyy', 'en-us') as FundsReqDate
FROM
dbo.Incidents INNER JOIN dbo.Funds ON dbo.Incidents.IncidentID=dbo.Funds.IncidentNumber INNER JOIN dbo.Timelapse ON dbo.Funds.RequestDate>dbo.Timelapse.Weekago
Timelapse is a 'View' on SQL server and calculates the date of -7 days - It's something we use for a lot of queries.
So when no new records are found for the last 7 days, it returns 'Null' to Flow, which breaks the condition statement.
User | Count |
---|---|
89 | |
41 | |
22 | |
20 | |
16 |
User | Count |
---|---|
138 | |
56 | |
47 | |
36 | |
26 |