Fusing Sort By Columns with the Search Function

[Anonymous]

I have this piece of code and I want to add a Search funtion to it so that I can Search for a specific date in the gallery, but I don't know how to do this:

SortByColumns(Filter(Table1_2,DateValue(Text(DATA),"pt")>=HOJE.SelectedDate,DateValue(Text(DATA),"pt")<=FUTURO.SelectedDate And Estado="A"),"DATA",If(SortDescending1,Descending,Ascending))

[RandyHayes]

@Anonymous 

I'm not sure you are entirely referring to the Search function as opposed to simply saying you want to search records based on a filter.  If it is the search function....You can only use the Search function on Text columns.  So, directly using a date in that function will not work.

But, looking at your formula, you can certainly include a date comparison.  Consider the following:

SortByColumns(
      Filter(Table1_2, 
             yourDateColumn >= HOJE.SelectedDate &&
             yourDateColumn <= FUTURO.SelectedDate && 
             Estado="A"),
      "DATA",
      If(SortDescending1,Descending,Ascending)
)

I hope this is helpful for you.

[Anonymous]

@RandyHayes 
I see, so how do I resolve this problem with the Filter function?

[RandyHayes]

@Anonymous 

What problem are you having with the filter function?

[Anonymous]

@RandyHayes 
None, but I don't know how to use the SortByColumns on the gallery and "filter" it with a textbox according to a certain date or another thing I might want to filter ir.

[RandyHayes]

@Anonymous 

So, in your formula:

SortByColumns(
      Filter(Table1_2, 
             yourDateColumn >= HOJE.SelectedDate &&
             yourDateColumn <= FUTURO.SelectedDate && 
             Estado="A"),
      "DATA",
      If(SortDescending1,Descending,Ascending)
)

You are filtering on three things 1 & 2) a date range based on the dates selected in the two controls - HOJE and FUTURO and 3) the Estado column containing "A".   Then you are Sorting all of that by the DATA column.

So, you are combining a filter/search with sorting.

But, you are mentioning a TextBox and "according to a certain date"...I assumed that you have "a certain date" being chosen by the two datepicker controls (HOJE and FUTURO).   Where does TextBox fit into this?  And what do you want the results to be based on if there is something in the text box?

[Anonymous]

@RandyHayes 
My bad I explained poorly what I wanted to do.

In the file I attached I think you can see more or less what I want, The gallery is already filtered and sorted by that function, but I want to be able to write a certain day in the "Search Items" textbox and filter it again by that number/value, hence the "Search" function in the title which I thought was useful in this situation.

[RandyHayes]

@Anonymous 

So based on that picture and your explanation - you want the user to be able to type in a date in the search box to further refine the results?

If that is the case, then I would suggest that you consider using another datepicker for such an action.  Having a user type a date and filtering based on that can introduce issues with date format accuracy.  A datepicker control can solve that by providing a datevalue.

If you want to be able to use the search box to further refine your results based on text in other columns of your data, that is fairly easy to achieve.

Just let me know which is your deign choice and we can put it together from there.

[Anonymous]

Can you help me do it both ways? The textbox might be more useful in the future if I want to improve the app but for now a datepicker works.

[RandyHayes · Accepted solution]

@Anonymous 

Sure thing...If you are attempting to have them choose an actual date and then filter your list based on that date, then you can 1) add a datepicker to your screen (let's call it dpSearchDate) then 2) change the DefaultDate property to nothing  3) change your Items property to the following:

SortByColumns(
      Filter(Table1_2, 
             yourDateColumn >= HOJE.SelectedDate &&
             yourDateColumn <= FUTURO.SelectedDate && 
             Estado="A" &&
             If(!IsBlank(dpSearchDate.SelectedDate), yourDateColumn = dpSearchDate.SelectedDate, true)
             ),
      "DATA",
      If(SortDescending1,Descending,Ascending)
)

This will give you a list based on the existing filter and then, if the user selects a date in the datepicker, it will further refine it by the chosen date.