If expresion in dashboard

Hello, I made a dashboard. the first view is a filter function for, for example, by name and age. That result should then appear in the second view. I know that with row filter conitions I have to do an IF expression …

Hi @Rebeltjuh

The table under the left-side should not have any filter.

The slice under the right-side may have something like this:

AND(
	OR(  ISBLANK(ANY(SearchForm[DoelGroep])),  
    [DoelGroep]=ANY(SearchForm[DoelGroep])  ),
        
	OR(  ISBLANK(ANY(SearchForm[Thema])),  
    [Thema]=ANY(SearchForm[Thema])  ),

  OR(  ISBLANK(ANY(SearchForm[Activiteit])),  
    [Activiteit]=ANY(SearchForm[Activiteit])  )
        
)

Note: this is a suggestion that you will need to adapt.

I notice you have some EnumList…this is for “simple entries” such as Text or Enum values :slightly_smiling_face:

2 Likes

Owww thank you so much…I checked it so many times and i never noticed that it has to be on the right side…

And yes i have Enumlist voor Activiteiten and Doelgroep and text by Thema. I hope that is okay???

This is what i made of it:

AND(
OR( ISBLANK((Search Form[Doelgroep])),
[Doelgroep]=(Search Form[Doelgroep]) ),

OR( ISBLANK(ANY(Search Form[Thema])),
[Thema]=ANY(Search Form[Thema]) ),

OR( ISBLANK((Search Form[Activiteit])),
[Activiteit]=(Search Form[Activiteit]) )

It is not giving an error. But when i fill in the form it does not work…

I hope you help me again :wink:

1 Like

Hi @Rebeltjuh

You may want to try this?

(I probably omitted one parenthesis…)

AND(
  OR( 
    ISBLANK(Search Form[Doelgroep]),
    ISNOTBLANK(
      INTERSECT(
        [Doelgroep],
        ANY(Search Form[Doelgroep])
      )
    )
  ),

  OR( 
    ISBLANK(Search Form[Thema]),
    [Thema]=ANY(Search Form[Thema]) 
  ),

  OR( 
    ISBLANK(Search Form[Activiteit]),
    ISNOTBLANK(
      INTERSECT(
        [Activiteit],
        ANY(Search Form[Activiteit])
      )
    )
  ) 
)
3 Likes

I tried but it says: INTERSECT takes two arguments of type List.

pffff i am still a beginner, sorry

When using EnumList on both sides you can’t completely filter the items because AppSheet won’t remove the results of the other items on a EnumList when just one of them matches.

EG: Searching for Items 1 and 3, and a row has 1, 2, 3, 4 and 5. AppSheet can include that row even if you are searching just for 1 and 3 or can ignore it completely if it doesn’t have exactly 1 and 3, but 2, 4 and 5 will be shown on the first one even if you didn’t search for it so I’m not sure what you want/expect

1 Like

My bad.

What about:

AND(
  OR( 
    ISBLANK(Search Form[Doelgroep]),
    ISNOTBLANK(
      INTERSECT(
        SPLIT([Doelgroep], " , "),
        SPLIT(ANY(Search Form[Doelgroep]), " , ")
      )
    )
  ),

  OR( 
    ISBLANK(Search Form[Thema]),
    [Thema]=ANY(Search Form[Thema]) 
  ),

  OR( 
    ISBLANK(Search Form[Activiteit]),
    ISNOTBLANK(
      INTERSECT(
        SPLIT([Activiteit], " , "),
        SPLIT(ANY(Search Form[Activiteit]), " , ")
      )
    )
  ) 
)

For reference:

SPLIT() - AppSheet Help

INTERSECT() - AppSheet Help

1 Like

Any already took just one value (arguably the first one)

@SkrOYC

Yes, but Doelgroep is an enumList

Search Form[Doelgroep]) ==> list of EnumList

ANY(Search Form[Doelgroep]) ==> ONE EnumList

SPLIT(ANY(Search Form[Doelgroep]), " , ") ==> List

1 Like

I understand the logit since you see it as a List of Lists, but AppSheet takes just one Value instead of just one List when using that formula.

You can do the following to convert it from List of Lists to a simple List:

SPLIT(
 CONCATENATE(
  SearchSlice[EnumList]
 ),
 " , "
)

2 Likes

I do not know if this is helpfull but i got my idea from https://www.youtube.com/watch?v=HWhtlzxtPss&list=PLgU2vka6tzhZ9X7PNFlIIHK8UWhRzbzIn&index=1&t=445s and he uses:

and(
if(IsNotBlank(Index(Current_User[FILTER_Construction_Type], 1)),
[Construction_Type] = Index(Current_User[FILTER_Construction_Type], 1),
true),

if(IsNotBlank(Index(Current_User[FILTER_Billable], 1)),
[TimeLog_Billable_Status] = Index(Current_User[FILTER_Billable], 1),
true),

if(IsNotBlank(Index(Current_User[FILTER_Start_Date], 1)),
[TimeLog_Timestamp] >= Index(Current_User[FILTER_Start_Date], 1),
true),

if(IsNotBlank(Index(Current_User[FILTER_End_Date], 1)),
[TimeLog_Timestamp] <= Index(Current_User[FILTER_End_Date], 1),
true)
)

Yes, it’s not a problem to recreate his setup, it’s a common and known thing @MultiTech calls “Enhanced dashboard”, some call it “Dynamic Dashboard” and it’s the fact that it uses a detail view to filter the slice.

The problem with your particular need is that you are filtering an EnumList with another EnumList.

Generally we filter Enum/Tex with EnumList or the other way around.

1 Like
SPLIT(
 CONCATENATE(
  SearchSlice[EnumList]
 ),
 " , "
)

I forgot about this point, thank you.

@Rebeltjuh Here is my expression revisited:

AND(
  OR( 
    ISBLANK(Search Form[Doelgroep]),
    ISNOTBLANK(
      INTERSECT(
        SPLIT(CONCATENATE([Doelgroep]), " , "),
        SPLIT(CONCATENATE(ANY(Search Form[Doelgroep])), " , ")
      )
    )
  ),

  OR( 
    ISBLANK(Search Form[Thema]),
    [Thema]=ANY(Search Form[Thema]) 
  ),

  OR( 
    ISBLANK(Search Form[Activiteit]),
    ISNOTBLANK(
      INTERSECT(
        SPLIT(CONCATENATE([Activiteit]), " , "),
        SPLIT(CONCATENATE(ANY(Search Form[Activiteit])), " , ")
      )
    )
  ) 
)

This will be my last contribution to this thread.

@SkrOYC , feel free to add further contribution.

2 Likes

Looks good.

Actually I learned something from you.

IF(
 ISBLANK([Blank]),
 TRUE,
 FilteringExpression
); or
IF(
 ISNOTBLANK([Blank]),
 FilteringExpression,
 TRUE
)

is not even needed since ISBLANK() gives TRUE already :grinning_face:

1 Like

Thank you very much for your time @Aurelien I learned a lot!!! it still does not work. i am going to try it without de enums

2 Likes

This is solved by combining IsNotBlank() & Intersect()

  • Which you’ve already seen inside the suggested formulas

  • Because if I intersect two lists… and there IS something common between them - then we should show that record.


Here’s what I’m thinking might work??

AND(
	if(isNOTblank(Search Form[Doelgroep]),
		ISNOTBLANK(
		  INTERSECT(
			SPLIT(CONCATENATE([Doelgroep]), " , "),
			SPLIT(CONCATENATE(Search Form[Doelgroep]), " , ")
		  )
		),
	true
	),
	
	if(IsNotBlank(Search Form[Thema]),
		[Thema] = INDEX(Search Form[Thema], 1),
	true
	),

	IF(IsNotBlank(Search Form[Activiteit]),
		ISNOTBLANK(
		  INTERSECT(
			SPLIT(CONCATENATE([Activiteit]), " , "),
			SPLIT(CONCATENATE(Search Form[Activiteit]), " , ")
		  )
		),
	true
	)
)

FYI: if [Doelgroep] is natively an EnumList (or VC that’s a List), then you don’t need to wrap it inside a Split(Concat…

  • You only need to do that when you’re dealing with a “list of lists”
2 Likes

Sure, but you are still dealing with the same thing I mentioned above:


But isn’t TableName[EnumList] a list of EnumList even if there is just one List?

1 Like

I couldn’t believe your solution didn’t work. So i spend all weekend to find my own mistake hahaha and i found it. It was something small that i forgotten. So your selution was te good one. Again thank you so much!!!

3 Likes

I’m glad you eventually made it. Congrats ! :grin:

2 Likes

@SkrOYC you are correct in the native sense of how AppSheet formulas work.

  • But in this case we are creating a formula inside of a slice, which is determining whether or not we should show a record.

So this idea of stacking is not blank and intersect together works, but only because we’re creating this formula inside of a slice.

And you’re probably right about still having to split out the list of lists. I’m not familiar with the intricacies of the system so I can’t really say either way.

1 Like