Check if Previous Date exists in SQL Table and then check this in Power Automate

[Adam01]

Bit of a complex one, I am wanting Power Automate to check if sales data for YESTERDAY ONLY exists or is blank, and if blank then return a zero and if not blank then return a 1

So in this example as there was sales data relative to yesterdays date which is "2022-03-28" as today is the 29th. I want Power Automate to return a 1 and therefore the rest of the flow will run.

If there was no sale data for the 28th I would want Power Automate to return a 0 and therefore the rest of the flow would not run.

So far I have the following:

Would this work or is there a more functional/effective way of doing this?

[abm]

Hi @Adam01 

Could you please post another screenshot of your flow? The current image is very small to read.

Thanks

[Adam01]

The first screenshot shows a variable where I set the default day as today -1. I then use this variable in the SQL Get Rows action as a filter where contains(SQL_Table, Variable)

The 2nd screenshot is just the expression i used in the YesterdayDate variable

The 3rd screenshot returns any rows that passed the filter (Check Rows Variable) and then uses a condition action to check if the length of the returned rows equals 0 (equivalent to a blank record). If equal to zero return true and send me an email notification. If not equal to zero return false and continue with the flow

[abm]

Hi @Adam01 

Thanks for the screenshot. It looks ok to me. You could also set the Top count  to 1 so that the payload will return only one but it depending on what you doing with the Excel and Apply to each loop. If you not processing all the records from SQL query and just want to find out the record count is zero only then you could use the top count set to 1.  

[Adam01]

This sounds like a better solution, how would I go about setting a top count to 1? I am new to Power Automate so am unsure of all the actions/functionality

[abm]

Hi @Adam01 

Thanks for your reply.

In your Get rows action step see one of the property called Top Count. Here set to 1.

Thanks

[Adam01]

Unfortunately I am still getting Power Automate return a TRUE in the condition variable (Where if row is blank in SQL then send me an email notification) however once I checked the SQL database there is data available relative to yesterdaydate (2022-03-28)

I should only be receiving an email notification if this date was NOT in the SQL data

Please see screenshot below that shows the data in the SQL table 

Please see the below screenshot that shows the Filter Query and the Top Count in the "Get Rows" action

Please see the below screenshot that shows the IF TRUE/FALSE condition test results for when I tested the flow with this top count functionality, so here it is telling me that the SQL rows are blank

Please see the below screenshot that shows the IF TRUE/FALSE condition that checks the length of the returned rows (Sale Date)

[abm]

Hi @Adam01 

Why you using contains filter here?  You use equals operator.

[Adam01]

Hi @abm apologies for the late reply but unfortunately after changing the "Get Rows" function to an 'eq' I am still not getting the desired result

Could this be because of a different data types in Power Automate and SQL Server? The "Sale Date" column in SQL is a date whilst power automate is converting it to string (Im unsure how to make power automate look at a date column). However both data types are formatted in the same way (yyyy-mm-dd)

[abm]

Hi @Adam01 

Is this on-premise SQL? Are you getting any error or returning zero results?

[abm]

Hi @Adam01 

You not storing DateTime in SQL? Or Date only?