cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kosenurm
Post Partisan
Post Partisan

Filter on IF statement

Hi there,

 

I have some code whereby I want to show an error message when Dataverse row count on a selected date is already in the table 5 times.

 

I have this so far but it is always appearing as TRUE as the boolean result, when there are in fact zero entries already in the Dataverse for this amount, so it should be false...

 

The column which holds the date in Dataverse is actually TEXT, if that helps.

 

 

If(CountRows(Filter('DataTable'.'Column', DatePicker.SelectedDate > 5)), Notify("This date is not currently available. Please choose a different date.", NotificationType.Error,10000));

 

 

Any ideas?

 

Thanks

K

1 ACCEPTED SOLUTION

Accepted Solutions

You're going to get a delegation warning. If the table has thousands of records this could be a problem. If every text date in DateColumn will be identical you could filter it as text, i.e.

If(CountRows(Filter('DataTable', 'DateColumn' = Text(DatePicker.SelectedDate,"dd-mm-yyyy")) > 5, Notify("This date is not currently available. Please choose a different date.", NotificationType.Error,10000));

 
I think that will avoid a delegation warning.

View solution in original post

16 REPLIES 16
BCLS776
Super User
Super User

You may have a parenthesis out of place. Try this one:

 

 

If(CountRows(Filter('DataTable'.'Column', DatePicker.SelectedDate)) > 5, Notify("This date is not currently available. Please choose a different date.", NotificationType.Error,10000));

// Also, 'DataTable'.'Column' should be a table, not a column reference

 

 

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Thanks for your reply.

 

I have tried your code and that comes up with "The function "Filter" has some invalid arguments"...

 

😞

 

K.

BCLS776
Super User
Super User

Yes, on further examination, this statement is missing a part of the condition for the Filter: 

Filter('DataTable'.'Column', DatePicker.SelectedDate)

DatePicker.SelectedDate is not a valid condition. It needs a logical operator, such as: DatePicker.SelectedDate = Today()

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Thanks for your reply.

 

All I need is for the OnSelect to display this error when there are more than 5 of the same "dates" (well, they are text in the Dataverse column) already in that column.

 

Where am I going wrong? I can't use a logical operator like Today(), as the condition is IF the selected date is already in Dataverse 5 or more times.

 

Thanks

K.

BCLS776
Super User
Super User

OK, we simply need to Filter() by your date column in your table. The Filter() will have a format like this:

Filter('DataTable', 'DateColumn' = DatePicker.SelectedDate)
// This returns a table of all records with a matching date to the date picker

Then your logic of performing a CountRows() on that filtered table will work.

 

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Thanks Bryan. 

 

How can I reference the Filter()'s 'table' in the CountRows() action?

 

Thanks

K.

Sorry, scratch that last reply.

 

I think I am nearly there, but will need to convert the Text format of the Dataverse column into "dd-mm-yyyy" Date format in order for the evaluation against DatePicker.SelectedDate to work - how could I do this?

 

Thanks

K

sopatte
Microsoft
Microsoft

If(CountRows(Filter('DataTable', 'DateColumn' = DatePicker.SelectedDate)) > 5, Notify("This date is not currently available. Please choose a different date.", NotificationType.Error,10000));

Thanks, but the DateColumn is actually a text column in Dataverse and this can't be changed now, so I get the "Incompatible types for comparison error". How can I 'force' the Dataverse column to be Date in the app?

 

Thanks

K.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,689)