Filter second gallery with search text box based on selected value from first gallery

[SAlston77]

Hello everyone,

I have created an app that uses a sharepoint list to that can search and populate a gallery. I have a second gallery that displays notes and the date those notes were taken that are stored in an excel file. Both data sources have a unique id that they share. I am trying to use a second search text box to filter the second gallery for specific dates.

The first gallery search feature works just fine and the second gallery populates with the appropriate notes when the first gallery object is selected. 

I have tried to use this:

If(IsBlank(TextInput1.Text),SortByColumns(Filter(Table1,StartsWith(UID,Subtitle2.Text)), "Notes", Ascending), SortByColumns(Filter('Table1', StartsWith(Date, TextInput1.Text)),"Date", "UID"))

The second     ***SortByColumns(Filter()***     is what seems to to not work correctly

When I run this, the second gallery filters the date but not correctly, when I select another entity from the first gallery the date and notes remain the same unless I remove the date from the search box then the dates and notes correspond correctly for each entity. 

The problem I believe I am having is trying to filter in the selected value from the first gallery so I can search the second gallery for the Date.

[RandyHayes]

@SAlston77 

At a glance - could you be running into an issue with the Date column being a date type?

Perhaps adjust to the following:

If(IsBlank(TextInput1.Text),
   SortByColumns(Filter(Table1,StartsWith(UID,Subtitle2.Text)), "Notes", Ascending), 
   SortByColumns(Filter('Table1', Date=DateValue(TextInput1.Text)), "Date", "UID")
)

See if that helps, if not, post back.

[SAlston77]

Unfortunately that did not work, I get the squiggly blue line under the equal sign stating that one side is text the other is a date.

[RandyHayes]

@SAlston77 

So then your Date column in your SharePoint list is a text column, not a Date column?

If that is the case, what format are your dates in?

If you are sorting text versions of dates, the only format that will sort properly will be yyyymmdd.

As for the second part of your issue (the date and notes remain the same) - this is because your filter is based on the value of TextInput1.  If the user selects another item in the Gallery, the TextInput1 will still retain its original value.  In this case, try putting a Reset(TextInput1) on the OnSelect action of the Gallery.

[SAlston77]

@RandyHayes 

Thanks for the help, I will give this a try early tomorrow morning.

Other projects are pressing for my attention

 Will let you know how it goes.

[SAlston77]

@RandyHayes,

I wanted to let you know that I got the filter to work properly. Using your advice that the filter was based off just the TextInput, I add a second condition to check 

If(IsBlank(TextInput1.Text),SortByColumns(Filter(Table1,StartsWith(UID,Subtitle2.Text)), "Notes", Ascending), SortByColumns(Filter(Table1, UID, Subtitle2.Text && StartsWith(Date, TextInput1.Text)), "Notes", Ascending))

As you can see,  I needed to check that the first gallery UID  was being included in the second Filter. 

The above line works and I wanted to update you of that and I greatly appreciate the help.   Cheers!!

[v-xida-msft · Accepted solution]

HI @SAlston77 ,

Could you please share a bit more about your scenario?

Do you want to add second filter condition within the SortByColumns formula?

Based on the formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please take a try to modify your formula as below:

If(
   IsBlank(TextInput1.Text),
   SortByColumns(
                 Filter(Table1,StartsWith(UID,Subtitle2.Text)), 
                 "Notes", 
                 Ascending
   ), 
   SortByColumns(
                  Filter(      /* <-- Modify formula here */
                          Table1, 
                          Subtitle2.Text in UID && StartsWith(Date, TextInput1.Text)
                  ), 
                  "Notes", 
                  Ascending
   )
)

Or

If(
   IsBlank(TextInput1.Text),
   SortByColumns(
                 Filter(Table1,StartsWith(UID,Subtitle2.Text)), 
                 "Notes", 
                 Ascending
   ), 
   SortByColumns(
                  Filter(      /* <-- Modify formula here */
                          Table1, 
                          StartsWith(UID, Subtitle2.Text),
                          StartsWith(Date, TextInput1.Text)
                  ), 
                  "Notes", 
                  Ascending
   )
)

More details about Filter function in PowerApps, please check the following article:

Filter function

Best regards,