Help with filtering data table by date and within text string

[brian4]

HI all,

I'm new to power apps and am struggling to come up with a filter that works correctly.  I am trying to filter 1 column by date (which I believe is working correctly) and also filter by a second column which contains a string of text.  I would like to use a dropdown to determine the text that will be searched within the string.  The dropdown options come from a choice column.

This is what I have:

Filter(ListName, (Column1 <=DatePicker1.SelectedDate && FinalRequestingDay >= DatePicker1.SelectedDate) && (Column2.Value = Dropdown1.Selected.Value || Dropdown1.Selected.Value = Blank()))

this doesn't give me any errors, but the list is blank when it shouldn't be.  If I remove the Column2 section, the date piker stuff works fine.  How do I go about searching the column2 for "yyyy" for example when column 2 contains "xxxx, yyyy, zzzz"

Not sure if its relevant, but column2 is a choice column, I used concat to get the choice column working in a data table: Concat(ThisItem.Column2, ThisRecord.Value, ", ")

Any help would be greatly appreciated!!!

[WarrenBelz]

Hi @brian4 ,

To test I theory here (I do not use Choice columns except for multi-select storage), try this

With(
   {
      wList:
      Filter(
         ListName, 
         (
            Column1 <= DatePicker1.SelectedDate && 
            FinalRequestingDay >= DatePicker1.SelectedDate
         ) 
      )
   }, 
   Filter(
      wList,
      Column2.Value = Dropdown1.Selected.Value || 
	  Dropdown1.Selected.Value = Blank()
   )
)

NOTE - the top filter needs to output record numbers less than your Delegation limit.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[brian4]

Thank you for your help!

on the line "column2.value = dropdown1.selected.value" from the second filter, I am getting an error with the equals sign stating that i can't compare the types table and text

[WarrenBelz]

@brian4 ,

I was using your code assuming it to be valid (you should have received an error as well - it is the same filter). What type of column is column2 and what are the Items of Dropdown1 ?

[brian4]

yeah, i'm not quite sure what is going on with that error.  I initially got that error so I was playing around with trying dropdown.selectedtext which didn't work.  I then when back to your originial code and the error went away, but the code still did not produce the correct result.  I fiddled around again, and now the error is back.  I'm not sure what I did to make it go away, but it seems like its here to stay this time.

as for the columns.  column 2 is a choice column which lists the places at my company where someone can work overtime.  the items in dropdown 1 are Choices(ListName.column2).  not sure if its relevant, but as I stated before, on the column2 section of the datatable, i have Concat(thisitem.column2, thisrecord.value, ", ") which works in showing the values within the table.

The goal is to use this as an overtime assigning tool.  If I want to assign overtime for today, I put the date in the date picker and the overtime code in column 2 and the datatable shows me everyone available for that day.  When they employees make themselves available they can select a date range, and multiple areas that they would like to be available to work.  Like I said, the coding for displaying the date selected, even when someone chooses a range works perfectly, its trying to display the multiple places to work that is giving me the issues.

For example if Joe wants to work everyday from 06/01/2022 through 06/20/2022 and he wants to work any overtime at the cash register, table bussing, and stocking shelves; When I search 06/10/2022 in datepicker and select stocking shelves from dropdown1, I'd like for Joe's row to appear.  I had it working fine with just 1 work location, but as soon as I changed the choice column to allow multiple selections, the whole thing just broke... driving me batty 🙂

[WarrenBelz · Accepted solution]

Hi @brian4 ,

That is the bit you did not include - it is multiple selection

With(
   {
      wList:
      Filter(
         ListName, 
         (
            Column1 <= DatePicker1.SelectedDate && 
            FinalRequestingDay >= DatePicker1.SelectedDate
         ) 
      )
   }, 
   Filter(
      wList,
      Dropdown1.SelectedItems in Column2.Value || 
      Dropdown1.Selected.Value = Blank()
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[WarrenBelz]

Hi @brian4 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[brian4]

sorry, just noticed I never gave you credit for helping me out!  Thanks again for the help!!!!