Data validity, help with expression

[tcanelli]

I need to prevent users from changing an assigned location without making sure status is correct.

Two fields: evac center location and appstatus.

I want to say that if the location is choice a, choice b, or choice c then app status should equal “complete - accepted hospital” and if location is choice d or choice e then app status should equal “complete - accepted evac center”.

Now there are other app statuses for archived and deceased where location no longer matters.

1. not sure which of the two fields the valid if expression goes and

2. not sure how to write the expression.

I tried and bunch of IF statements but they didnt work.

Help is appreciated. Thanks

[Bahbus]

An expression in a Valid If must either produce a TRUE/FALSE or a list of acceptable options. So this won’t work as a Valid If no matter where you put it.

You want to put that expression in the column’s app formula.

[tcanelli]

I can got an IFS expression to work in the app formula but I dont think this will work for us. Not sure how to handle pending or archived records if the app formula is tying the status to the assigned location.

When the expression is in the app formula it becomes read only so how could we change status if needed?

[Bahbus]

Well, you can either write more complicated expressions to handle more possible cases, or you could use actions and workflows to set that columns value. I have no idea what the overall intent of your app is, so I can’t give too much advice. There are many different ways to accomplish various things.

[Bellave_Jayaram]

I agree with @Bahbus. Best option is to set that column always using an action (form save or data change workflow).

[Jonathan_S]

Maybe a switch statement would suit you better? Depending on how many differant choices you have there may be more optimal routes, but this should work,

SWITCH([Choice],
a, "complete - accepted hospital",
b, "complete - accepted hospital",
c, "complete - accepted hospital",
d, "complete - accepted evac center",
e, "complete - accepted evac center",
"Your Default value, this would be for those that dont matter I guess"
)

Hope that helps,

[Bahbus]

If it helps to think of it this way, Valid Ifs are great ways to ensure that only THESE things can exist in this column. But they don’t define which should be there.

[Steve]

Can the user modify appstatus, or is it set by the app?

[tcanelli]

Yes and I need it to stay editable so records can be archived. We had an issue this week when 2 records had their locations changed from a reg evac center to a hospital but their status didnt get updated and it through off a report. So now I’m just trying to prevent this oversight in the future but cant have a formula because status does need to be changed outside of location assigned also.

[Steve]

Try this as the Valid If expression for the appstatus column:

LIST(
  "Archived",
  "Deceased",
  "Pending
)
+
IFS(
  IN(
    [EvacCenterLocal],
    LIST(
      "Bayfront (Venice)",
      "Doctors Hospital",
      "Sarasota Memorial"
    )
  ),
    LIST("Complete - Accepted (Hospital)"),
  IN(
    [EvacCenterLocal],
    LIST(
      "Lamarque",
      "Tatum Ridge"
    )
  ),
    LIST("Complete - Accepted (Evac Center)")
)

This builds a list of valid appstatus values. Only values in the completed list will be considered valid values for the appstatus column.

1. LIST("Archived", ...) always includes the given values, so these values will always be valid for appstatus regardless of any other condition.

2. + IFS(...) conditionally adds more valid appstatus values.

3. IN([EvacCenterLocal], LIST(...)) returns TRUE is the current EvacCenterLocal column value matches (as with =) one of the values in the given list.

4. IN(...), LIST(...) adds the contents of the list (which may include more than one item) to the list of valid appstatus values if the IN() expression evaluates as TRUE.