Map View with Filter not showing all LatLong points

[Linus_Grupp]

Hello Appsheet community,

I want to have a dashboard with one map view and an additional detail view that acts as a filter for the map view.
Therefore I created two slices: one for the map view and another for the filter table.
The filter table consist of multiple columns, which are all of type EnumList with base type Ref.

For this table, there is only one row for the whole table, and you can only edit this row.

The data slice for the map has a row filter condition, so that only points should be displayed that match the data in the filter table.

The row filter condition is: IN([REC_LOC_ID],LOOKUP(“85n2043”,“Maps_Filter_Slice”,“MAP_ID”,“MAP_LOC_ID”))
This is just a filter for one of the columns (LOC_ID), which is meant to be used as a pattern for all other columns, as soon as it works. It should filter out all rows, where the REC_LOC_ID of the data slice is not in the EnumList of the filter table in column MAP_LOC_ID. The key column of the filter table is MAP_ID. Since there is only one row with the key “85n2043”, it should return one list.
I already tried several other expressions/approaches (SELECT, FILTER, INDEX, …), but nothing worked yet.

The problem is, as soon as I have more than one entry in the EnumList of the filter table, it only shows points on the map for one of them, not for both. Even more confusing is, that in the results pane for the row filter condition, it shows the correct results.

In this example, the blue marked column is for the LOC_ID, and there are two entries in the filter table (3, 108). In the results pane, as marked in green, all rows are indicated true that have a LOC_ID of 3 or 108. Unfortunately, this is not reflected in the map view and I have no idea why.

As you can see in the last screenshot, the green marked points are displayed for the one filter entry (Wismar, Rothentor). In the red area, the points for the other filter entry (Mittenwald, Am Horn) are missing.

Any help would be appreciated.
Best regards,
Linus

[Steve]

Wrap LOOKUP() with SPLIT():

SPLIT(LOOKUP(...), " , ")

[Linus_Grupp]

I wrapped Lookup with your proposition. Did not help.

IN([REC_LOC_ID],SPLIT(LOOKUP(“85n2043”,“Maps_Filter_Slice”,“MAP_ID”,“MAP_LOC_ID”), " , "))

[SkrOYC]

Linus_Grupp:

IN([REC_LOC_ID],LOOKUP(“85n2043”,“Maps_Filter_Slice”,“MAP_ID”,“MAP_LOC_ID”))

This is a really good an advance way of doing it. I have a dynamic chart that works like this and listens to some filters.

Please notice that IN requires the Value you are looking for first and then a List of values to compare the first one.
Also, LOOKUP just returns one value from one row, so this is not the best expression (IMHO)

Could you please show us your maps table schema including the column type for each one and which columns should match the filters columns? Just to have a better idea and give some advice on which expression to use.

PS: Have you seen this?

[Linus_Grupp]

I know that LOOKUP only returns one value from one row. But I built the filter table in a way that there is only one row and since the column to be returned has the type EnumList I thought that should not make any problems. In the end the value of REC_LOC_ID (Ref) is looked for in the EnumList MAP_LOC_ID (Ref). As already stated, in the results pane it seems to be correct.

Both columns (REC_LOC_ID and MAP_LOC_ID) reference on the same column of type Number:

Here is a part of the map table schema:

The yellow columns are the ones where the filter should be applied. The green one is the one for the map dots.

I will take a look at the video, thank you!

[SkrOYC]

Linus_Grupp:

I know that LOOKUP only returns one value from one row.

That means that IN() only has 1 value instead of a List of values to check the [REC_LOC_ID]

Since you are going to be applying more than just 1 filter, I think the row filter condition should be something like:

AND(
	IF(
  	ISBLANK(
    	BAU_MAPS_FILTER[MAP_LOC_ID]),
    TRUE,
    IN(
   	  [REC_LOC_ID],
    	BAU_MAPS_FILTER[MAP_LOC_ID]),
  ),
 	IF(
  	ISBLANK(
    	BAU_MAPS_FILTER[MAP_COM_ID]),
    TRUE,
    IN(
   	  [REC_COM_ID],
    	BAU_MAPS_FILTER[MAP_COM_ID]),
  ),
ETC, ETC, ETC, ETC, ETC, ETC, ETC, ETC
)

[Linus_Grupp · Accepted solution]

I managed to make it work, but only with a workaround.
I had to create a virtual column for each filter column in the map data table. The expressions for the virtual columns are like this:

SPLIT(LOOKUP(“85n2043”,“BAU_MAPS_FILTER”,“MAP_ID”,“MAP_LOC_ID”)," , ")

Appsheet recognizes the virtual columns as type List correctly. The base type however was always Text per default. I guess this is where the problem lies. As you cannot set the base-base type of a column of type EnumList with base type Ref, Appsheet probably worked with the filter table data as type Text.

The referenced Id’s are all of type Number. So I had to set the base type for each virtual column manually on Number.

I still don’t understand why it worked partially with my previous setup and showed at least the data for one entry in the EnumList of the filter table, even if there were other entries that were not shown in the map, as already stated in my first post.

The row filter condition for the map data slice is now as follows:

AND(
IF(ISBLANK(BAU_MAPS_FILTER[MAP_LOC_ID]),TRUE,IN([REC_LOC_ID],[REC_FILTER_LOC_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_COM_ID]),TRUE,IN([REC_COM_ID],[REC_FILTER_COM_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_CPT_ID]),TRUE,IN([REC_COMPETITOR],[REC_FILTER_CPT_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_FRK_ID]),TRUE,IN([REC_FRAKTION],[REC_FILTER_FRK_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_PRI_ID]),TRUE,IN([REC_PRIORITY],[REC_FILTER_PRI_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_CON_ID]),TRUE,IN([REC_CONTAINER_TYPE],[REC_FILTER_CON_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_STS_ID]),TRUE,IN([REC_STATUS],[REC_FILTER_STS_ID])),
IF(ISBLANK(BAU_MAPS_FILTER[MAP_REC_ASSIGNMENT_TYPE]),TRUE,IN([REC_ASSIGNMENT_TYPE],[REC_FILTER_ASSIGNMENT_TYPE]))
)

Thank you for your input, guys!

[SkrOYC]

Great!

I encourage you to debug it anyway.

I mean, if you have no idea why it works, it’s a sign that it needs attention.
It seems that you solved the problem creating another one (not really, but doing it on a way that’s not the best).
This is not criticism, I’m trying to help you since I have had this same kind of problems where I have no idea why and ended solving them creating new ones. Debug it from the beginning will save you a headache later on the road. (I have one right now with my biggest app because I didn’t payed attention to this kind of things when I started)

[Linus_Grupp]

I tried to solve it in a better way than with the virtual columns. One problem with the virtual columns is, that the filter is applied 5 - 10 s delayed to the Geo View. This is because the virtual column “looks” on the BAU_MAPS_FILTER table and if you change the data in this table you have to wait until the app synchronized the Google Sheet.

I managed to make it work without virtual columns under certain circumstances:

• the data column to be filtered is not of type List
• the source column IDs of the filtered Ref columns are of type Text (not Number, like before)

Then the row filter condition for the slice is e.g. like this:

IF(ISBLANK(BAU_MAPS_FILTER[MAP_LOC_ID]),TRUE,IN(TEXT([REC_LOC_ID]),BAU_MAPS_FILTER[MAP_LOC_ID]))

However, if the data column to be filtered is of type List, I need a row filter condition like this:

IF(ISBLANK(BAU_MAPS_FILTER[MAP_CPT_ID]),TRUE,COUNT(INTERSECT([REC_COMPETITOR],SPLIT(LOOKUP(“85n2043”,“BAU_MAPS_FILTER”,“MAP_ID”,“MAP_CPT_ID”)," , ")))>0)

And here I have the same problems as before. In the results panel for the Expression it shows the correct results. But those results are not matching the display in the Geo View. If I want to filter on multiple values (IDs) in a List column, it only shows the map pins for one of the filter values, not all of them.
This seems like a bug to me, especially since the results panel shows my expected results but in the App it does not match these results.