Filter Gallery by Date Control

[AtlantisFury]

I am trying to filter my gallery using a list, which all works fine except the option where I want the user to select a date from a DatePicker control. I keep getting the error that "The values compared have mismatched types". I am very new to this so i'm sure it's something simple im not seeing. This is the "OnSelect" function for my list.

If(ThisItem.TicketTypes="All orders",UpdateContext({type:"All"}),If(ThisItem.TicketTypes="New orders",UpdateContext({type:"Requested"}),If(ThisItem.TicketTypes="Tickets in progress",UpdateContext({type:"In progress"}),If(ThisItem.TicketTypes="Completed Orders",UpdateContext({type:"Fulfilled"}),If(ThisItem.TicketTypes="Orders on hold",UpdateContext({type:"On hold"}))))));If(ThisItem.TicketTypes="Orders for:",Filter(OrderForm, Date_x0020_for_x0020_the_x0020_Cake = Text(DatePicker1.SelectedDate,ShortDate)),UpdateContext({datetype:Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate),type:"Orders for:"}));If(ThisItem.TicketTypes="Orders filled today",UpdateContext({datetype:Text(Today()),type:"Orders filled today"}));If(ThisItem.TicketTypes="Orders submitted today",UpdateContext({datetype:Text(Today()),type:"Orders submitted today"})))

My Gallery items function is as below.

If(type="All",OrderForm,If(FilterGallery.Selected.TextBox1.Text="Orders For:",Filter(OrderForm,datetype in Date_x0020_for_x0020_the_x0020_Cake),If( FilterGallery.Selected.TextBox1.Text="Orders submitted today",Filter(OrderForm,datetype in DateCreated),If(FilterGallery.Selected.TextBox1.Text="Orders filled today",Filter(OrderForm,datetype in DateCreated),Filter(OrderForm,type in Status)))))

Any help would ge greatly appreciated! 

[Anonymous]

Hi @AtlantisFury

The error basically means that you are trying to compare two uncomparable variables. In your case I think you are comapring Text to Date which does not work.

You also do not need to use that much IF statements as one IF statment can support multiple logic questions.

Example:

Your Gallery Items function:

If(type="All",OrderForm,If(FilterGallery.Selected.TextBox1.Text="Orders For:",Filter(OrderForm,datetype in Date_x0020_for_x0020_the_x0020_Cake),If( FilterGallery.Selected.TextBox1.Text="Orders submitted today",Filter(OrderForm,datetype in DateCreated),If(FilterGallery.Selected.TextBox1.Text="Orders filled today",Filter(OrderForm,datetype in DateCreated),Filter(OrderForm,type in Status)))))

Is same as this simplified version:

If(
    type="All",
    OrderForm,
    FilterGallery.Selected.TextBox1.Text="Orders For:",
    Filter(OrderForm,datetype in Date_x0020_for_x0020_the_x0020_Cake),
    FilterGallery.Selected.TextBox1.Text="Orders submitted today",
    Filter(OrderForm,datetype in DateCreated),
    FilterGallery.Selected.TextBox1.Text="Orders filled today",
    Filter(OrderForm,datetype in DateCreated),
    Filter(OrderForm,type in Status)
)

Basically when you add more than Three arguments to IF function instead of IF(Condition,True,False) it will become IF(Condition,True,SecondCondition,True,ThirdCOndition,True,......,LastCondition,True,False).

Also While analyzing your OnSelect function:

If(
    ThisItem.TicketTypes="All orders",
    UpdateContext({type:"All"}),
    If(ThisItem.TicketTypes="New orders",
    UpdateContext({type:"Requested"}),
    If(ThisItem.TicketTypes="Tickets in progress",
    UpdateContext({type:"In progress"}),
    If(ThisItem.TicketTypes="Completed Orders",
    UpdateContext({type:"Fulfilled"}),
    If(ThisItem.TicketTypes="Orders on hold",
    UpdateContext({type:"On hold"})))))
);
If(
    ThisItem.TicketTypes="Orders for:",
    Filter(OrderForm, Date_x0020_for_x0020_the_x0020_Cake = Text(DatePicker1.SelectedDate,ShortDate)),
    UpdateContext({datetype:Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate),type:"Orders for:"})
);
If(
    ThisItem.TicketTypes="Orders filled today",
    UpdateContext({datetype:Text(Today()),type:"Orders filled today"})
);
If(
    ThisItem.TicketTypes="Orders submitted today",
    UpdateContext({datetype:Text(Today()),type:"Orders submitted today"})
)
)

First the Filter if this is OnSelect the FIlter function is not valid here what are you tryign to achieve?

Also it seems to have one extra ) than needed at the end.

Here is simplified function:

If(
    ThisItem.TicketTypes="All orders",
    UpdateContext({type:"All"}),
    ThisItem.TicketTypes="New orders",
    UpdateContext({type:"Requested"}),
    ThisItem.TicketTypes="Tickets in progress",
    UpdateContext({type:"In progress"}),
    ThisItem.TicketTypes="Completed Orders",
    UpdateContext({type:"Fulfilled"}),
    ThisItem.TicketTypes="Orders on hold",
    UpdateContext({type:"On hold"}),
    ThisItem.TicketTypes="Orders for:",
    UpdateContext({datetype:Text(DatePicker1.SelectedDate,DateTimeFormat.ShortDate),type:"Orders for:"}),
    ThisItem.TicketTypes="Orders filled today",
    UpdateContext({datetype:Text(Today()),type:"Orders filled today"}),
    ThisItem.TicketTypes="Orders submitted today",
    UpdateContext({datetype:Text(Today()),type:"Orders submitted today"})
)

This basically just assigns Type based on ThisItem.TicketTypes and datetype for few possibilities.

Try to remove the Text() from datetypes and see if it solves your problem. You are comparing to Date_x0020_for_x0020_the_x0020_Cake and CreatedDate what type of columns are they? Datetime?

[AtlantisFury]

Hi @Anonymous

Thanks! I am using the "Service Desk" sample app as a starting point and editing it from there and subbing in my own data. That is where the format of the functions comes from. I appreciate your help very much.Your simplified versions fixed the errors I was getting. 

I am trying to filter what comes up in my gallery, I want the user to select "Orders for:" from the Filter List, then select a date in the DatePicker1, so that only entries from my data source "OrderForm", show up when the selected date in DatePicker1 matches the date in the column "Date for the Cake" in my excel file. I still can't figure out how to get that filter to work.

If( type="All", 
OrderForm, 
FilterGallery.Selected.TextBox1.Text="Orders for:", 
Filter(OrderForm, Date_x0020_for_x0020_the_x0020_Cake<=Date(Year(DatePicker2.SelectedDate),
Month(DatePicker2.SelectedDate),
Day(DatePicker2.SelectedDate))), 
FilterGallery.Selected.TextBox1.Text="Orders submitted today", 
Filter(OrderForm,datetype in DateCreated), 
FilterGallery.Selected.TextBox1.Text="Orders filled today", 
Filter(OrderForm,datetype in DateCreated), 
Filter(OrderForm,type in Status))

This function works as is, but when i take out the '<' from the equation, it stops working doesn't return any values to my gallery. I need the user to be able to filter by a specific day, not "less than or equal to" a certain day. Any suggestions?

Thanks so much.

-AtlantisFury 

[AtlantisFury · Accepted solution]

Update: I added a space to the formula and now it works, not sure wh ythat made a differnece but it worked. Now it looks like this and works great!

Thank you to this thread here as well, helped me a lot.

https://powerusers.microsoft.com/t5/PowerApps-Forum/Filter-SharePoint-list-date-column-using-date-pi...

If( type="All", 
OrderForm, 
FilterGallery.Selected.TextBox1.Text="Orders for:", 
Filter(OrderForm, Date_x0020_for_x0020_the_x0020_Cake = Date(Year(DatePicker2.SelectedDate),
Month(DatePicker2.SelectedDate),
Day(DatePicker2.SelectedDate))),
FilterGallery.Selected.TextBox1.Text="Orders submitted today", 
Filter(OrderForm,datetype in DateCreated), 
FilterGallery.Selected.TextBox1.Text="Orders filled today", 
Filter(OrderForm,datetype in DateCreated), 
Filter(OrderForm,type in Status))

-AtlantisFury

[Patrick2DOS]

I also tried it with an date control.... but didn´t made it.

So I tried it another way and made it.

Here the solution:

Sort(If(IsBlank(TextSearchBox1), SharePointList, Filter(SharePointList, TextSearchBox1.Text in Text(Created, DateTimeFormat.ShortDate, "en-US"))), Created, Descending)

All you need is your gallery and a TextField which you would rename as "TextSearchBox1"

In the TextField you must search know concording to your languge settinds of the Text-function (in this case US-English)

MM/DD/YYYY e.g. 03/24/2018 and you would get the items for this date.