Using IFS() expressions to set Enum values based on a reference table with a related Enum column

[Star1010]

I have an app with two tables ("units" and "tenants") referencing each other. Each table has a column named "status". I'd like to set up an App formula to auto compute the value of unit table's "status" value based on the status values of the records in the referenced tenants table. The tenants table has a "unit_id" field which is used as the key between the tenant and unit records. 

I was thinking of using IFS(CONTAINS()) on the status column in the units, but that doesn't seem to apply the results correctly. In the case of the formula below it simply would result in all records defaulting to "Occupied" rather than resulting in a "Coming Soon" value if a referenced record includes the result "coming soon". 

Parent Table status column formula:

IFS(IN(Current Lessee,tenants[status]), "Occupied")
IFS(IN(coming soon,tenants[status]), "Coming Soon")

I've also tried this approach: 

IFS(
CONTAINS
(FILTER(
"tenants",
AND(
([status] = "coming soon"),
([unit_id] = id)
)
),"coming soon")
,"Coming Soon")

I'm not getting any results from the second approach.

The outcome I'm looking for is to have the following values auto computed:

IF a there is a Tenant with tenants[status]= "coming soon" THEN the units[status] = "Coming Soon"

IF a there is a Tenant with tenants[status]= "Current Lessee" THEN the units[status] = "Occupied"

ELSE units[status] = "Vacant"

Is there a way to run this formula in the app, or should I be trying to set this up to run in the spreadsheet?

I've also considered that I need to make the status column of the Units table non-editable if it's updated by a formula.

Thanks for any help you can offer here. 

[dbaum]

See: 

• IN
• List dereference

IFS(
  IN("Coming Soon", [Related Tenants][Status]), "Coming Soon", 
  IN("Current Lessee", [Related Tenants][Status]), "Occupied", 
  true, "Vacant"
)

[Star1010]

Eureka, dbaum! Thank you 🙂 Have a great weekend.