cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bunly11
Helper V
Helper V

Help adding Search to a Sort/Filter

Hi,

 

I would like to add a filter box to search a gallery that is sorted/and filtered by version year. 

I tried to add to @WarrenBelz code but was not able to get it to work.

 

With(
   {TBL:UpdatesTBL},
   Search(
    Sort(
      Filter(
         TBL,
         Year(VersionDate) = Year(Today())
      ),
      If(Sort(VersionDate,
      Descending)
   )
), Searchbox1, "DrugName"))

 

 How do I modify code to all the user to search for updates by "DrugName"?

Bunly11_0-1627018490076.png

 

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions

@Bunly11 ,

As per the PM you sent me - firstly you had an extra bracket in the middle (which was the syntax error - hint for you - always parse your code like the below you would have picked it up). Also, you need to seriously consider throwing away your lookups columns unless you use them directly in SharePoint (please have a read of this blog of mine). However to solve your immediate issue, if you want to search in a Lookup column, you need to use AddColumns()

With(
   {TBL:UpdatesTBL},
   Search(
      Sort(
         AddColumns(
            Filter(
               TBL,
               Year(VersionDate) = Year(Today())
            ),
            "Drug",
            DrugName.Value
         ),
         VersionDate, 
         Descending
      ),
      Searchbox1.Text, 
      "Drug"
   )
)

If you are only using one search column, I would also suggest this

With(
   {TBL:UpdatesTBL},
   Sort(
      Filter(
         AddColumns(
            TBL,
            "Drug",
            DrugName.Value
         ),
         Year(VersionDate) = Year(Today()) &&
         (IsBlank(Searchbox1.Text) || Searchbox1.Text in Drug)
      ),
      VersionDate, 
      Descending
   )
)

 

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.

 

View solution in original post

6 REPLIES 6
CNT
Super User
Super User

@Bunly11 Try this,

With(
   {TBL:UpdatesTBL},
   Search(
    Sort(
      Filter(
         TBL,
         Year(VersionDate) = Year(Today())
      ),
      VersionDate, Descending
   )
), Searchbox1.Text, "DrugName"))

Hi @CNT - I tried above but I still got the same error. The error message I get is "Invalid number of agruements: received 4, expected 2".

 

Bunly11_0-1627057014173.png

 

Is it because the data is a lookup field?

Bunly11_2-1627058007667.png

 

Bunly11_1-1627057976356.png

 

@Bunly11 Well, you didn't mention earlier that DrugName was a LookUp Column. Then you have to do like this, DrugName.ColumnName where ColumnName is the Column which is of type Text in the other Table.

@CNT Hi - I tried DrugName.DrugName since that is the lookup column name and the name of the other column but no luck. I tried to add a new column that is text named "DrugNameNew". That seems to work but now I have a delegation warning and I do not know how to add the sort/filter by year to it.

Bunly11_0-1627076447397.png

I still got an error when I tried to add to the code

With(
   {TBL:UpdatesTBL},
   Sort(
      Filter(
         TBL,
         Year(VersionDate) = Year(Today())
      ),
      VersionDate,
      Descending
   )
), Searchbox1.text, "DrugNameNew"))

 

Bunly11_1-1627077509546.png

 

Anyone have suggestion how to fix this? Thank you

@Bunly11 ,

As per the PM you sent me - firstly you had an extra bracket in the middle (which was the syntax error - hint for you - always parse your code like the below you would have picked it up). Also, you need to seriously consider throwing away your lookups columns unless you use them directly in SharePoint (please have a read of this blog of mine). However to solve your immediate issue, if you want to search in a Lookup column, you need to use AddColumns()

With(
   {TBL:UpdatesTBL},
   Search(
      Sort(
         AddColumns(
            Filter(
               TBL,
               Year(VersionDate) = Year(Today())
            ),
            "Drug",
            DrugName.Value
         ),
         VersionDate, 
         Descending
      ),
      Searchbox1.Text, 
      "Drug"
   )
)

If you are only using one search column, I would also suggest this

With(
   {TBL:UpdatesTBL},
   Sort(
      Filter(
         AddColumns(
            TBL,
            "Drug",
            DrugName.Value
         ),
         Year(VersionDate) = Year(Today()) &&
         (IsBlank(Searchbox1.Text) || Searchbox1.Text in Drug)
      ),
      VersionDate, 
      Descending
   )
)

 

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.

 

View solution in original post

Bunly11
Helper V
Helper V

@WarrenBelz thank you! yes, I am trying to figure out how to not use lookup columns. but my data is from an Access database that is now tied to SharePoint. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,204)