Filter rows on Virutal tables

(0) Share

Report

Posted on by NativeN

Hi All

I have a problem concerning filtering rows using 'List Rows' connector in Dataverse on a Virtual Table.

The Virtual table runs on top of a SQL on premise database.

When I just list all of the rows there is no issue and it returns everything,

however when I try to filter based on dates I am constantly thrown with errors like;

I first wanted to use the 'When a row is added' as a Dataverse trigger but that seems impossible using Virtual tables.

I can filter on other fields then DateFields, but the dates filter just doesn't seem to work.

I have the following outputs from my 'List Rows' Dataverse table working with the poc_created column (virtual table based on SQL on prem)

"poc_created@OData.Community.Display.V1.FormattedValue":"6/22/2016 10:00 AM",
"poc_created@odata.type":"#DateTimeOffset",
"poc_created":"2016-06-22T10:00:02.423Z"

Can anybody please tell me how to fetch the rows of 1 day ago;

I tried a lot of thinks like;

poc_created ge 'addDays(utcNow(),-1)'

but nothing seems to work..

I can filter on other columns, it's just the date column that's being a real pain..

Thanks & kind regards

Categories:

General topics

All responses (4)

Answers (0)

Sort by

NativeN 51 on at

Like
a
(0)

Report
Copy link

Link copied!
I got my 'Get Rows' on my original SQL database to work;

here I had to specify the following Filter Query;
date(Created) ge date(formatDateTime(addDays(utcNow(),-7),'yyyy-MM-ddT00:00:00Z'))

Since we really need to specfify the date values here, could it be that the same is needed to be done for Dataverse virtual tables?
Tried the same but didn't work.
Can't seem to find anything in the documentation that references this..
Nao_ 121 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello everyone,

Any update about how to filter on a date column in a virtual table ?
I'm facing the same issue,
Thank you 🙂
NativeN 51 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Nao_

I basically solved my issue by applying the filter on my SQL action in Power Automate. And I used that connector in flow.

Unable to use default Dataverse triggers & stuff like that in Power Automate looks like a big flaw at the moment in the virtual table scenario's
prashtest 12 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello everyone,

Any update about how to filter on a date column in a virtual table ?
I'm facing the same issue, I want to get the latest records

Thank you