Answered

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

(0) Share

Report

Posted on by noneother

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

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

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

AaronMoon 43 on at

Like (0)

Report

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)

Was this reply helpful? Yes No
noneother 53 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Verified answer

v-siky-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
AaronMoon 43 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
noneother 53 on at

Like (0)

Report

Thanks

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No