How to filter values in EnumList

AppSheet AppSheet Q&A
1

I have a CRM with 3 tables:

People (one row per person, with contact info)

Activities (one row per interaction, refers to People, records info about a specific interaction with that person)

D_Reason (lookup table used to populate the EnumList field in Activities to indicate one - or many - reasons for the interaction)

It all works fine.

Now the people using this want to hide some of the “reasons” because they are obsolete for new interactions. I added an Inactive_date column to the D_Reason table, and have been struggling with how to make it work. In the settings for the Reason field in the Activities table, I’ve tried a bunch of different expressions, in the Valid_if area and also the Suggested Values. What I am trying to do is filter the values that are displayed in the dropdown so that for a new entry OR an entry with an Activities.Created_at value that is after the Inactive_date in the D_Reasons table, only the “active” reasons are displayed, and for viewing and editing older records, all of them show.

No luck.

I’m pretty experienced with databases and spreadsheets. I know the logic of what I’m trying to do - it’s the syntax of AppSheet that has me baffled.

Help, please!

2

Hi @ak_in_la

Try using a Slice in the Appsheet table with the criteria you want, so that the data displayed matches the criteria.

3

The problem with that is that it needs to be dynamic - the filtering of the values in the D_Reasons table needs to be based on the value of the CreatedAt field in the current row of the Activities table.

4

Are you able to create a valid_if something like

SELECT(D_Reason[Reason], [Inactive_Date]>[Activities.Created_at])

It is not clear how the [Inactive_Date] column works for the active Reasons. I mean is the [Inactive-Date] populated for each and every reason? Or if the reason is active , the [Inactive_Date] field is blank for that reason? Based on such finer points, the expression suggested will need modifications.

5

Try this for Valid if:

(
  FILTER(
    "D_Reason",
    OR(
      ISBLANK([Inactive_date]),
      ([Inactive_date] < TODAY())
    )
  )
  + [_THISROW_BEFORE].[Reasons]
  - LIST("")
)

FILTER(...) allows all reasons that either have no inactive date (ISBLANK([Inactive_date])) or whose inactive date hasn’t yet arrived (([Inactive_date] < TODAY())). See also: FILTER()

+ [_THISROW_BEFORE].[Reasons] expands the list of allowed reasons to include those reasons that were present already, before the user opened the form. Change Reasons to whatever the name of the column containing the user-chosen reasons is. See also: Access column values before and after an update

- LIST("") removes any blank reasons (which should never occur here, but why not make sure?) and. due to a side-effect of lisy subtraction, removes any duplicate reasons. You could instead use UNIQUE(FILTER(...) + [_THISROW_BEFORE].[Reasons]) to remove duplicates. See also: Subtract values from a list, UNIQUE()

6

@1minManager’s approach is the right shape (filter the list in `Valid_if` and add back the already-selected values so old records still validate), but heads up — there’s an inverted comparison in that expression that will give you the opposite of what you want.

`[Inactive_date] < TODAY()` matches reasons whose inactive date is *in the past* — i.e. the **expired** ones. An *active* reason is one that hasn’t been deactivated yet, so its inactive date should be **blank or in the future**. So the condition should be `>`, not `<`:

```

(

FILTER(“D_Reason”,

OR(

  ISBLANK(\[Inactive_date\]),

  (\[Inactive_date\] > TODAY())

)

)

  • \_THISROW_BEFORE\].\[Reason
  • LIST(“”)

)

```

Now it shows reasons that are blank-dated (always active) or whose deactivation date hasn’t arrived yet, plus whatever was already saved on the row.

One more thing about your exact requirement: you said new entries (or ones created after a reason’s Inactive_date) should show only active reasons, but when **viewing/editing older records all reasons should show**. `Valid_if` only controls what’s offered in the dropdown and what passes validation — the `+ [_THISROW_BEFORE].[Reason]` piece already handles the “old record keeps its now-inactive values” case, so editing won’t error out. But if you want an old record to *also re-offer* all the reasons that were active back at its `Created_at` time, base the cutoff on the row’s own date instead of TODAY():

```

(

FILTER(“D_Reason”,

OR(

  ISBLANK(\[Inactive_date\]),

  (\[Inactive_date\] > \[\_THISROW\].\[Created_at\])

)

)

  • \_THISROW_BEFORE\].\[Reason
  • LIST(“”)

)

```

That makes the dropdown reflect “what was active at the time this activity was created” — which sounds like exactly the behavior you described. For brand-new rows `[Created_at]` will be ~now, so they get the current active set automatically.

If you want it to *display* nicely (not just validate) in the form, make sure this is on the **Valid_if** of the Reason column, and confirm the EnumList’s base type / referenced table is D_Reason so the suggested values come from the same filtered list.

7

Thanks to those who responded - and extra thanks for explaining the options for tweaking the details of how I implement this. Much appreciated!

8

I can tell you that I have more luck in finding my answers in this community than I do with relying on AI itself :slight_smile: I’m glad this form is available for all of us Googlerss