If expresion in dashboard

[Rebeltjuh]

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 ....

[Aurelien]

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 🙂

[Rebeltjuh]

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 😉

[Aurelien]

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])
      )
    )
  ) 
)

[Rebeltjuh]

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

pffff i am still a beginner, sorry

[Aurelien]

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

[SkrOYC]

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

[SkrOYC]

---

@Aurelien wrote:

SPLIT(ANY(Search Form[Doelgroep]), " , ")

---

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

[Aurelien]

@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

[SkrOYC]

---

@Aurelien wrote:

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

---

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]
 ),
 " , "
)

[Rebeltjuh]

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)
)

[SkrOYC]

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.

---

@SkrOYC wrote:

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

---

[Aurelien · Accepted solution]

---

@SkrOYC wrote:

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

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.

[SkrOYC]

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 😀

[Rebeltjuh]

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

[MultiTech]

---

@SkrOYC wrote:

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

---

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"

[Rebeltjuh]

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!!!!

[SkrOYC]

---

@MultiTech wrote:

This is solved by combining IsNotBlank() & Intersect()
   - Which you've already seen inside the suggested formulas

---

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

---

@SkrOYC wrote:

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

---

---

---

@MultiTech wrote:

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"

---

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

[MultiTech]

@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.

[Aurelien]

I'm glad you eventually made it. Congrats ! 😁