Sql filter Date and Bit columns

(0) Share

Report

Posted on by Nicci

194

Hi all,

I need some assistance! I have a sql get rows connector and I need to filter on these 2 columns:

ReminderDate = today and Activeflag = 1

ReminderDate is a date column and the ActiveFlag is a bit column in the database.

Here is what I tried but it's not working:

ReminderDate eq @{formatDateTime(utcNow(), 'yyyy-MM-dd')} and ActiveFlg eq true

I also tried combinations like activeflag eq '1' and many others and nothing is working for me. Its not throwing an error, just not returning any matches so I'm sure i'm doing something wrong as the database definitely has rows where its active and the reminderdate is today.

Thank you!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Sort by

wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

Direct Date Filters Don't Work on SQL Server Tables:

https://learn.microsoft.com/en-us/connectors/sql/#power-apps-functions-and-operations-delegable-to-sql-server

Binary() Type is Not Supported:

https://learn.microsoft.com/en-us/connectors/sql/#power-apps-data-type-mappings

Was this reply helpful? Yes No
Nicci 194 on at

Like (0)

Report
Copy link

Link copied!

This is the worst news I've ever read as we built this thing specifically so we can use automate to get email notifications based on reminder data users enter 😞 😞 😞

Was this reply helpful? Yes No
Nicci 194 on at

Like (0)

Report
Copy link

Link copied!

I made a mistake because looking at the database it IS a bit, not a binary. My mistake. I'm going to update my question to not cause confusion.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

Don't lose hope! You just have to figure out how to get what you want with the tools you are given.

The link even tries to give an example about date. For example, you can create ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])), and then filter on the calculated number column.

If you want it to work, you will need to figure out what you need to change in the SQL Server that will allow you to work with power automate.

Probably the strongest feature that does work is "Execute Stored Procedure" from power automate. This will allow you to set up whatever you want in SQL server, and make it work with a stored procedure. Then in power automate you trigger that stored procedure.

You could also just use a "Transform data using Power Query" which you could just set your filters there and run the flow off that.

Microsoft doesn't make using SQL Server easy from Power Automate because they want people using the Dataverse. However, if you can get creative like making other tables or automatic scripts in SQL Server, then you can use Power Automate much easier.

You can also use Dataverse for Teams for free, and that is enough to port over data from SQL Server to the Dataverse using Dataflows, and then run flows from there.

Keep Hope Alive!

Was this reply helpful? Yes No
Nicci 194 on at

Like (0)

Report
Copy link

Link copied!

Thank you! this is very helpful information. Its a bit complicated to update the database as the models in the web app would need to change if we change the type or add more columns. I did not know dataverse for teams is free and one thing we, as an organization, havent done yet is much dataverse experimenting due to being GCC and having sensitive data on the cloud rather than on prem isnt a place we're at just yet 🙂

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

That's where the stored procedure can come into place. You can make some tables in SQL Server to specifically hold these power automate data. Use the stored procedure to fill these sideline power automate tables, call the data with Get Rows, then wipe the tables with another stored procedure.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

The SQL action "Transform Data Using Power Query" is very simple. Have you tried using that?

Was this reply helpful? Yes No
Verified answer

Nicci 194 on at

Like (0)

Report
Copy link

Link copied!

I have not, but I just figured it out!!

This is my syntax for anyone out there struggling like I did!

The date column didnt need the format date that is typically out there in tutorials because my column is not a datetime, just date, so utcnow() just works and already formatted. Also bits are already bool so you dont need to do flag eq blah blah, just and flag for true. Yay!

Was this reply helpful? Yes No