Re filter widget, how to handle multiple values in one field per row/location?

[aurichat]

Hi all,

I'm building an Experience using data that starts in Excel and goes from XLSX > CSV > Pro (CSV to XYTabletoPoint to geodatabase) > Online > EB (so that I can add attachments and edit the pop-ups).

Each row represents a location/point on the map, and each column/field is an attribute (type of location, date created, whether it still exists, etc.).

The issue is with attributes that need to hold multiple values for a single location. For example, one attribute is political affiliation (this is unrelated to the US). A single location has multiple affiliations, plus their abbreviations. Initially, I wanted abbreviations to be filterable values on their own while also linked to their full names, so that filtering one would also return the other, but I couldn't get that working. For now, I've just put the abbreviations in parentheses. Off the top of my head, here's what one location might have in that field: National Party of Calico Cats (NPCC), Paws Raise Up (PRU), Cats United (CU).

I've tried separating the values in Excel with semicolons, commas, etc., but once I get to the filter widget in EB, it recognizes the entire list/text/string of each location as one filterable value. Instead, I'd like each affiliation/abbreviation of each location to be filterable. And do so for the other attributes that have more than one value in them.

I know I can create one column for every separate value, but then they aren’t filterable under the same attribute (I think?).

Is there a way to structure the data at any point in the process so that one attribute can store multiple values such that each value can be filtered individually? Or something in the filter widget that can get this done?

Edit, to clarify, filterable using the "is any of" option. So that one can select multiple values per attribute.

Thanks!

[JeffreyThompson2 · Accepted solution]

I think we can hack this out.

1. Set up a single column in your data with the multiple comma separated values. Include the comma on the last item in the list as well.
2. In the SQL Builder, select contains and Multiple (Predefined).
3. Click on Predefine Multiple Values.
4. Click Add.
5. Click on the box under Value. (You can type in there.)  And type the name of one of your parties with the comma.
6. Fix the Label as you want.
7. Repeat 4-6 for the other parties.

This should make a menu with multiple options of your parties.

[aurichat]

Pretty sure this is the part where I'm supposed to offer you my firstborn or something, because you absolutely came through. This works!

The only thing that seems off is if, say, locations A, B, and C are all affiliated with political group #1, and location D is affiliated with political groups #1 and #2. If I select both #1 and #2, the filter shows me locations A, B, C, and D, even though, since I selected #2 as well, shouldn't it only show me D?

[JeffreyThompson2]

Ok, let's set up this up a little differently.

1.  Start over with a fresh Filter Widget.
2. Add a New Filter and click on the SQL Expression Builder.
3. Use Contains, select User Input, type the name of your party with the comma.
4. Go to More Input Settings and select No User Input. Click Ok.
5. Change the Label to something more meaningful.
6. Click the Duplicate Button on the filter you just made.
7. Alter the SQL Expression and Label for your next party.
8. Repeat 6&7 for any other parties.
9. Use the Horizontal Arrangement Style and Button Activation Style for better UX.

Now the features should have to meet every button clicked on.

[TimWestern]

Yeah if its just a simple comma delimited list this would work great.  If you have more complex relationships, it might be time to consider how to setup relationships and tables that would link for that 1 to many type of lookup you might need.