How to add another Filter option when you already have a Date Picker

[JEVANSAP]

Hi All

I have a gallery in which i have already got a Date Picker that i am able to Filter by using the Formula in the Items in the gallery. 

If(IsBlank(DatePicker1.SelectedDate),'Harbour House Key History',Filter('Harbour House Key History', 'Date Out' = DatePicker1.SelectedDate))

I now want to add a Text input search function that will be able to filter on another column. also with the function that if the text input is blank i can see all results. 

Data Source: 'Harbour House Keys'

Column: 'Name in'

Text Input: TextInput1

Kind Regards

Justin 

[JEVANSAP]

Hi @TylerPA 

I tried this formula "Search(collectHistory,TextInput4.Text,"NameIn")" 

And instead of getting all the errors i am just getting it in that 1 column. ( i don't know why this is) 

I am still getting this message: The Function Search 'Search' Has some invalid Arguments. 

My Date Colum is a date picker and my Name in and Name out is a person Picker. 

Thanks for looking into this 

Justin

[JEVANSAP]

I don't know if this means anything, but when i put this formula in 

Filter(
collectHistory,
If(IsBlank(DatePicker1.SelectedDate), true, 'Date Out'=DatePicker1.SelectedDate),
If(IsBlank(TextInput4.Text), true, 'Name'=TextInput4.Text)
)

I get no errors but when i try the app and type in the name nothing is coming up. That 'Name' isnt a colum i have. 

[NandiniBhagya20]

Hi @JEVANSAP ,
Hopefully the below formula should work -
If(IsBlank(DatePicker1.SelectedDate), 'Harbour House Key History', Search(Filter(AddColumns('Harbour House Key History', "NameInFinal", NameIn.Email, "NameOutFinal", NameOut.Email), 'Date Out' = DatePicker1.SelectedDate),TextInput3.Text,"NameInFinal","NameOutFinal"))

[JEVANSAP]

This works but i get the delegation warning and i when i try to search nothing comes up. 

Thanks

Justin

[JEVANSAP]

Ignore this!

[JEVANSAP]

I tell a lie 

I get no errors when i use this formula however when i try and search using the text input it doesn't search anything. 

I had to add some "" and change the name slightly on the text input so the formula is now

f(IsBlank(DatePicker1.SelectedDate), 'Harbour House Key History', Search(Filter(AddColumns('Harbour House Key History', "NameInFinal", "NameIn.Email", "NameOutFinal", "NameOut.Email"), 'Date Out' = DatePicker1.SelectedDate),TextInput4.Text,"NameInFinal","NameOutFinal"))

But when i put the name into the text input nothing happens. 

Thanks 

Justin

[TylerPA · Accepted solution]

For the code above,

If(
    IsBlank(DatePicker1.SelectedDate),
    testList,
    Search(
        Filter(
            AddColumns(
                testList,
                "NameInFinal",
                nameIn.Email,
                "NameOutFinal",
                nameOut.Email
            ),
            dateCol = DatePicker1.SelectedDate
        ),
        TextInput1.Text,
        "NameInFinal",
        "NameOutFinal"
    )
)

I was able to get it working (tested using nameIn.Email and nameIn.DisplayName). Please check if displayname works instead? I noticed in my dataset, some names didn't have emails. Also, above had typo (assuming it's a cut/paste issue - missing 'i' of 'if')

For this small filter, that should work. If you are looking to filter with more parameters, I would recommend going back to the following format. I think all that was missing earlier, was the .DisplayName to access the properties of the person column. I was able to get both of below working

///
with delegation issue (if)
///

Filter(
    testList,
    If(
        IsBlank(DatePicker1),
        true,
        dateCol = DatePicker1.SelectedDate
    ),
    If(
        IsBlank(TextInput1),
        true,
        Or(
            TextInput1.Text in nameIn.DisplayName,
            TextInput1.Text in nameOut.DisplayName
        )
    )
)



///////
using collection
///////

Filter(
    collectTest,
    If(
        IsBlank(DatePicker1),
        true,
        dateCol = DatePicker1.SelectedDate
    ),
    If(
        IsBlank(TextInput1),
        true,
        Or(
            TextInput1.Text in nameIn.DisplayName,
            TextInput1.Text in nameOut.DisplayName
        )
    )
)

[JEVANSAP]

Hi @TylerPA 

Thank you for looking into this i have tried both, if i try the collection formula i get the error : "Incompatible Types for comparison, These Types cannot be compared: Error,Date"

If i try connecting to the Data Source i get the delegation issue using this formula you gave me.

Filter(
    'Harbour House Key History',
    If(
        IsBlank(DatePicker1),
        true,
        dateCol = DatePicker1.SelectedDate
    ),
    If(
        IsBlank(TextInput4),
        true,
        Or(
            TextInput4.Text in 'Name In'.DisplayName,
            TextInput4.Text in 'Name Out'.DisplayName
        )
    )
)

I don't know if this could be causing an issue however also on my gallery i have another formula on "onselect" which is:

Set(varKeyEdit2,ThisItem);EditForm('Harbour House Key History Edit');Navigate('Harbour House Key History Edit',ScreenTransition.Fade)

I don't have much data in the list at the moment so i am not sure why i am getting this delegation issue. 

Thanks again for looking 

Kind Regards

Justin

[TylerPA]

I don't think that 'onselect' is causing anything, based on that information

Just to double check - for the 'error, date' message when trying a collection, can you confirm 'dateCol' was changed to the correct column name? 

The delegation issue is based on using 'if' and 'in', within our filter parameters. So formats 1 and 3 (search, or collection) would be your best bets to avoid

I took another stab at formatting, to cut back on stacked if statements. I think these match your source names, but I would double check

// collection version 

Filter(
    collectHistory,
    Or(
        DatePicker1.SelectedDate = 'Date Out',
        DatePicker1.SelectedDate = Blank()
    ),
    Or(
        TextInput1.Text in 'Name In'.DisplayName,
        TextInput1.Text in 'Name Out'.DisplayName,
        TextInput1.Text = Blank()
    )
)


// search version 

Search(
    Filter(
        AddColumns(
            'Harbour House Key History',
            "NameInFinal",
            'Name In'.DisplayName,
            "NameOutFinal",
            'Name Out'.DisplayName
        ),
        Or(
            'Date Out' = DatePicker1.SelectedDate,
            DatePicker1.SelectedDate = Blank()
        )
    ),
    TextInput1.Text,
    "NameInFinal",
    "NameOutFinal"
)

[JEVANSAP]

Hi @TylerPA 

IT WORKED! I didnt change the "DateCol" to the correct collum, once i did that and change it to get the data from the collection it worked, i can now filter by date and name!

Thank you so much for all your help and patience. 

Kind Regards

Justin