AnsweredAssumed Answered

Filter portal using multiple fields with multiple values

Question asked by andrek42 on Jan 3, 2019
Latest reply on Jan 8, 2019 by jbrown

Okay, this is a little tricky but I'll do my best to explain it. Please let me know if I missed something.

 

So, I have this database where my records can have fields with multiple values. For example:

NameCategoryCity
Site 1Archeological Site
Cave
City 1
Site 2CaveCity 2
Site 3

Cave

Archeological Site

City 3
Site 4WaterfallCity 4

 

What I want to do is to have a List Layout with one selection box for each field (in this case, "Category" and "City" - see image below) and I want the portal to filter and show me all records that meet at lest one of the criteria (from each field) I select on the selection boxes.

 

As for now, the following calculation is set for filtering the portal using the following calculation (using a Global Field):

 

(

PatternCount ( Sitios_Geodiversidade 2::Categoria_Tematica ; Menu::XBUSCA.CATEGORIA )

and

IsEmpty ( Menu::XBUSCA.MUNICIPIO )

) /*for when I select a Category but not a City */

or

(

PatternCount ( Sitios_Geodiversidade 2::Municipio ; Menu::XBUSCA.MUNICIPIO )

and

IsEmpty ( Menu::XBUSCA.CATEGORIA )

) /*for when I select a City but not a Category */

or

(

PatternCount ( Sitios_Geodiversidade 2::Categoria_Tematica ; Menu::XBUSCA.CATEGORIA )

and

PatternCount ( Sitios_Geodiversidade 2::Municipio ; Menu::XBUSCA.MUNICIPIO )

) /*for when I select both a Category and a city */

 

0.JPG

 

What I achieved so far and what the problems are:

  • When I select Archaeological Site and Cave (and don't select any city yet), I only get Site 1 but I would like Site 2 and Site 3 to appear, since they also belong to at least one of the selected criteria. [This also made me realize that the order in which the values were imputed at the field also matter and I don't want this, for me Archaeological Site + Cave should count like Cave + Archaeological Site]
  • When I select Cave as the 1st criteria and City 1 and City 2 as the 2nd criteria I don't get any result, but I would like for Site 1 and Site 2 to appear, since they both meet the 1st criteria and they both meet at least one of the 2 options selected on the 2nd criteria.
  • When I select Cave and Waterfall (without any city selected) I don't get any site, but I would like for all sites to appear, since all of them meet the Cave or the Waterfall criteria. If I then select City 2, I would like only Site 2 to appear, since it's the only site located at City 2 which meets any of the 1st criteria (Cave). If additionally to City 2, I also select City 4, I would like for Site 4 to also appear, because it meets at least one criteria from the 1st (Watterfall) and at least one criteria from the 2nd (City 4).

 

Can someone help me? This is a logical/programming problem that is melting my brain down hehe

Outcomes