Solved: Expression to show specific list of values by one ...

mymymy · 12-15-2021 04:47 AM

I have a table with multiple columns. Important columns for this topic are “Category” (Values = “Trails”, “Nature”, “Culture”, “Service”), “Trails” (Values = “Tread”, “Backslope”, “Jump”, “Feature”), “Nature” (Values = “Lake”, “Protected area”, “Wildlife reservat”), “Measures” (only one Value: “Record”), “M1” (Values = “A1”, “A2”, “A3”), “M2” (Values = “B1”, “B2”, “B3”) and “M3” (Values = “D1”, “D2”, “D3”). All these columns are EnumLists.
If these columns should be visible is defined by expression. However, there are multiple difficulties which do not work by now.

Measures: Show = OR([Category] = “Trails”, [Category] = “Nature”)
→ the Button Measures should only be visible if either Trails or Nature is selected

M1: Show = AND(IN([Trails], LIST(“Tread”, “Other”, “Other”)), [Measures] = “Record”)
→ M1 should only be visible, if Tread is selected (or Others)

M2: Show = AND(IN([Trails], LIST(“Jump”, “Feature”, “Other”)), [Measures] = “Record”)
→ M2 should only be visible, if “Jump” or Feature (or Others) is selected.
→ If both Tread and Jump is selected, M1 and M2 should be visible.

M3: Show = AND(OR(IN([Trails], LIST(“Backslope”, “Other”, “Other”)), IN([“Trails”], LIST(“Protected area”, “Other”, “Other”)), [Measures] = “Record”)
→ M3 should only be visible, if Backslope (or Others) or Protected area (or Others) is selected.

Rules (how they should work and how they do by now):
So, it is important, that it is shown as soon as at least one defined value is selected. If two are selected which refer to two different M’s, both M’s should be shown. By now it’s only shown if the two values are in the definition of the same M. Otherwise no M is shown.
At the moment it is also like that, that M3 (which should be visible either by Trails or Nature is always shown (even if no defined value is selected).

How can I change my expressions that above rules do work correctly?

SkrOYC

Change AND() for OR() where that happens.

This should be working

Post the whole thing to help you with the expression. In general, it seems OK. M1 should be shown if the value inside [Trails] is one of the values from the LIST() you made and the value inside [Measures] is “Record” (btw, why you have this column if only one value is available? It’s defined by an expression?)

This also seems ok.

Ok also.

This all boils down to this I think:

So if [Trails] is a list, you are comparing a list with another. IN() was not meant for this kind of things.
See:

Try replacing your IN() with this: (M1 for example)

ISNOTBLANK(
  INTERSECT(
    [Trails],
    LIST("Tread" , "Other", "Other")
  )
)

View solution in original post

SkrOYC

I don’t get why you are using AND() to wrap most of your stuff. IN() doesn’t need that.
Also, always write textual values "Like this" instead of Like this.
Try that.

Koichi_Tsuji

Very much true.

mymymy

Sorry, yes, I do wrap text in “text” normally. I was confused because of the * for bold text etc. And I also forgot to add [Measures] = “Record”) which is the reason why I use AND()

SkrOYC

So, post here the actual config for us to see and help

mymymy

I changed it in the first post!

SkrOYC

Change AND() for OR() where that happens.

This should be working

Post the whole thing to help you with the expression. In general, it seems OK. M1 should be shown if the value inside [Trails] is one of the values from the LIST() you made and the value inside [Measures] is “Record” (btw, why you have this column if only one value is available? It’s defined by an expression?)

This also seems ok.

Ok also.

This all boils down to this I think:

So if [Trails] is a list, you are comparing a list with another. IN() was not meant for this kind of things.
See:

Try replacing your IN() with this: (M1 for example)

ISNOTBLANK(
  INTERSECT(
    [Trails],
    LIST("Tread" , "Other", "Other")
  )
)

SkrOYC

Hi @Phil!
Any comments about this?
Is IN() V2 in the works?

mymymy

Thanks a lot, you’re great! It was really only because IN() can’t compare lists with another.
Using following code instead of IN() does what I want and solved all my problems.

ISNOTBLANK(
  INTERSECT(
    [Trails],
    LIST("Tread" , "Other", "Other")
  )
)

THANKS!!!

FYI: I use the column Measures because I’m not always using the Measures and it looks better if you have to press the button Measures in case you want to add some measures

SkrOYC

Nice!

Expression to show specific list of values by one or multiple other values from different columns