Trouble filtering gallery from sharepoint list choice column

[tpalmer94]

I would have thought this would be a simple solution, however I have read countless forums and posts now to no avail.
I have a list 'Database' and choice column 'Area'. For example;

Name	Area
Company 1	Newcastle
Company 2	Newcastle, Sydney
Company 3	Sydney, Wollongong

I have a dropdown box that is working correctly with items "Choices(Database.Area)".

My problem arises with filtering a gallery. I have tried;

Filter(Database, Dropdown1.Selected in Database.Area)

^ This gives me "Invalid argument type. Expecting a Record value, but of a different schema."

and "Missing column. Your formula is missing a column 'Area' with a type of 'Table'."

Filter(Database, Database.Area = Dropdown1.Selected)

^ Which gives me "Invalid argument type" at "="

No addition of ".Value" anywhere has solved the problems, and futher, when typing "Database.Area." there is nothing in autocomplete at all. What am I missing? I need to ultimately be able to use a combobox with multiple selections, i.e. User selects "Newcastle, Sydney" and can see all companies, or "Sydney, Wollongong" and can only see the last 2 etc. If I can't even get a single dropdown working I'm not feeling optimistic!

[eka24]

Test any of these:

Filter(Database, Area.Value = Dropdown1.Selected.Value)

Or

Filter(Database, Area.Value = Dropdown1.Selected.Area)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[tpalmer94]

Thanks for the quick response, I hadn't realised I could remove the Database reference. Unfortunately still no solution.

Filter(Database, Area.Value = Dropdown1.Selected.Value)

^ gives me "Invalid argument type" at "=" again

and

Filter(Database, Area.Value = Dropdown1.Selected.Area)

^ gives me the same, but also "Name isn't valid. This identifier isn't recognized." on the ".Area"

[eka24]

Did you rename the column in your SharePoint list?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[tpalmer94]

I have (for troubleshooting purposes) renamed the column LGA2, refreshed the data in the powerapp, and all of the errors indicated it was pointing to LGA correctly the first time. I then renamed the column back to LGA and it solved all the errors. I have also tried with a new dummy column 'test' and the errors are the same.

I have also deleted the data connection and re-added to no avail.

[eka24]

I was asking whether there was an initial column that you renamed to Area or Area was the first name you created. 

Am suggesting you create a fresh choice column as a test. Then replace Area in your formula with the New choice column.

If there are errors, take a screenshot.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[v-albai-msft · Accepted solution]

Hi @tpalmer94 ，

Since your combo box allows multiple selections, and also your Area column is a multiple choice column, you can not directly compare their results. Because both their results return a table.

So for your requirement, you can try this formula on your gallery:

(Note: if you are using a dropdown, the code will be easier, since dropdown doesn’t allow multiple selection)

Filter(Database, ID in Ungroup(ForAll(ComboBox1.SelectedItems,Filter(Database,Value in Concat(Area,Value&","))),"Value").ID)

Best regards,

Allen

[tpalmer94]

Thank you for your help everyone. In the first instance, removing the 'Database.' prior to 'Area' (and later LGA because I was frustratingly inconsistent, my apologies) in the filter AS WELL AS creating a new column in the list with the same data did the trick. I then faced the challenge of changing to a combobox which Allen nailed in one. I am grateful for both solutions and apologise for being radio silent, work priorities had to shift drastically.

I am now faced with a new one, delegation of the filtering to Sharepoint, as my list has over 5000 rows. There are many columns I can filter on, but this particular solution from Allen seems to not be delegating, and I'm not entirely sure what it's doing. Would you mind sharing a breakdown of the code that worked with a combobox?