Valid_if filter on enum ref column confusion

AppSheet AppSheet Q&A
, ,
1

Hello,

I have an enum ref column used to pull in ‘Zone Id’ into a dropdown on a form. In the valid_if I have the following:

Zones[Zone ID]

On the form I first select a contact from the contact table dropdown (comes from the contact table/contact ID), then the next dropbox gives me the zones associated with that contact (from the Zones table/zone ID).

That all works well. However, I want to filter the Zones in the dropdown by only pulling in the Zones which have the term ‘Managed’ in the Zones table ‘Zone Status’ column.

When I have used a filter to do this, which does work, the associations between contact and zone are ignored and I instead see a long list of zones, of which some are and some aren’t associated with the contact in the first contact dropdown.

I assume the Filter needs to be more specific somehow, but It’s currently only one line to pull in the ‘managed’ zones.

Thoughts?

Thank you

2

Perhaps this works for you.

In the valid-if, set an expression similar to

FILTER(
 "Zones", 
 AND(
  [Zone ID] = [_THISROW].[Contact Dropdown].[Zone ID],
  CONTAINS([Zone Status],"Managed")
 )
)
1 Like
3

Thank you.

That doesn’t work but likely due to the poor design of my table relationships perhaps… very new to Appsheet.

However, this does work:

SELECT(
Zones[Zone ID],
AND(
[Contact ID] = [_THISROW].[Contact ID],
[Zone Status] = “Managed”
))

Is there any advantage to using FILTER?

4

I suggested a solution based on my guess of your architecture. So whatever works for you is perfectly fine.

I do not have any information on the efficiency of SELECT vs FILTER but my guess is that there is not much difference.

5

Thank you.

You pointed me in the right direction.

6 
[_THISROW].[Contact Dropdown].[Zone ID]

Is [Zone ID] here a LIST?

If it is then you cannot use the comparison the way you have written it. You should use

FILTER(
 "Zones", 
 AND(
  IN([Zone ID],[_THISROW].[Contact Dropdown].[Zone ID]),
  CONTAINS([Zone Status],"Managed")
 )
)
7

Hi,

Yes it is a list actually.

Thanks I’ll try that.