cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Complex filtering (includes search, user filter, and status filter)

Hi all

I'm trying to build a complex filter that filters by user, search term, status. I can get the user and search term however the status sits on a linked sharepoint list B (i.e. the user and search terms are from sharepoint list A which pulls 'status' from B) - hope that makes sense.

 

I basically tried to use the same IF formula presented here: https://www.youtube.com/watch?v=vyBsk7-1Mxk

 

forum1.JPG

 

I am having trouble incorporating a dropdown filter list that can filter by status (which sits on another sharepoint list)

 

My formula is:

If(Dropdown1.Selected.Value = "All",

 

//below is filter if true:

Filter('Consumer Sample for Survey FINAL', StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text))),

 

//below is filter if false:

Filter('Consumer Sample for Survey FINAL', StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text)) &&

LookUp(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'.ACMPS_CARE_RECIPIENT_ID, Status).Value = Dropdown1.Selected.Value)

)

)

The text in red is the part that's giving me issues. The error is invalid argument. I understand LookUp gives a scalar result but the overall filter returns a table.

 

I'm lost as to how I can solve this. 
Does anyone have any suggestions?

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Complex filtering (includes search, user filter, and status filter)

Hi @noneother ,

 

What is the Data type of ACMPS_CARE_RECIPIENT_ID column in two lists? LookUp or Text/Number?

If they are all Text/Number type, please try this:

Filter('Consumer Sample for Survey FINAL', StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text)) &&
LookUp(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'[@ACMPS_CARE_RECIPIENT_ID]).Status.Value = Dropdown1.Selected.Value)

OR

Filter(AddColumns('Consumer Sample for Survey FINAL',"TheStatus", LookUp(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'[@ACMPS_CARE_RECIPIENT_ID]).Status.Value), StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text)) && TheStatus = Dropdown1.Selected.Value)

Hope this helps.

Sik 

View solution in original post

6 REPLIES 6
Highlighted
Frequent Visitor

Re: Complex filtering (includes search, user filter, and status filter)

Have you tried this? 
First(Filter(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'.ACMPS_CARE_RECIPIENT_ID, Status).Value = Dropdown1.Selected.Value))

 

Another option would be to save your filter into a collection and use AddColumns to add the status as a column. then you could filter the collection again by the status. 

ClearCollect(colCustomerSamples, AddColumns(Filter('Consumer Sample for Survey FINAL', StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text)), "STATUS", LookUp(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'.ACMPS_CARE_RECIPIENT_ID, Status).Value))

Then Filter(colCustomerSamples, STATUS = Dropdown1.Selected.Value)

Highlighted
Helper I
Helper I

Re: Complex filtering (includes search, user filter, and status filter)

@AMoon thanks.

Using First(Filter( doesn't seem to work unfortunately, it still gives invalid argument type.

Issue with using collections is that I need to write extra formulas to write from collection to sharepoint list so it's actually more work.

Highlighted
Community Support
Community Support

Re: Complex filtering (includes search, user filter, and status filter)

Hi @noneother ,

 

What is the Data type of ACMPS_CARE_RECIPIENT_ID column in two lists? LookUp or Text/Number?

If they are all Text/Number type, please try this:

Filter('Consumer Sample for Survey FINAL', StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text)) &&
LookUp(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'[@ACMPS_CARE_RECIPIENT_ID]).Status.Value = Dropdown1.Selected.Value)

OR

Filter(AddColumns('Consumer Sample for Survey FINAL',"TheStatus", LookUp(Rep_SurveyResults, ACMPS_CARE_RECIPIENT_ID = 'Consumer Sample for Survey FINAL'[@ACMPS_CARE_RECIPIENT_ID]).Status.Value), StartsWith(Caller,varUserEmail), Or(StartsWith(CLIENT_FIRST_NAME, SearchBox.Text),StartsWith(ACMPS_CARE_RECIPIENT_ID, SearchBox.Text)) && TheStatus = Dropdown1.Selected.Value)

Hope this helps.

Sik 

View solution in original post

Highlighted
Frequent Visitor

Re: Complex filtering (includes search, user filter, and status filter)

Can you can hover over the error in the formula bar or look at the App Checker to see if you can get a little more information about the invalid argument type? It does seems that somewhere in your formula you have two different data types being compared. You need to first identify which formula condition is causing the problem. Then make sure that you are comparing text to text, number to number, etc. 

Highlighted
Helper I
Helper I

Re: Complex filtering (includes search, user filter, and status filter)

Thanks
Highlighted
Community Support
Community Support

Re: Complex filtering (includes search, user filter, and status filter)

Hi @noneother ,

 

Have your issue been resolved?

If so, please mark the answer helped you as the answer, in order to help more members find it quickly.

Sik

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,324)