Limiting options in Enum field based on condition in a parent table

I am trying to figure out how to limit the options in an Enum field (Child table) based on the options chosen in an Enum field in the Parent table.

Example: The Parent table, CourtTbl I have a Enum field, ChargeType, with the following options: Class X, Class I, Class II, Class II, Class IV, Class A, Class B, Class C.
In the Child table, VPRAITbl, i have a Enum field, ChargeCategory with the following options: NonViolentMisdemeanor, DrivingUndertheInfulence, NonViolentFelony, ViolentMisdemeanor, and ViolentFelonyFirearm.

If the user for example makes the chooses Class X - Class IV, I would like to restrict the user from being able choose NonViolentMisdemeanor and ViolentMisdemeanor in the VPRAITbl, ChargeCategory. I would also like to do the reverse. Hopefully this helps to reduce mistakes. The two tables are connected via an ID field.

I have a number of other areas within the app that this would be helpful. If I can elicit help with this, I can implement it in other areas.

0 7 590
7 REPLIES 7

After thinking about this and doing some research on it, I should have explained this a bit better.

Basically, if a person is charged with a felony offense, i.e. Class X, Class I, Class II, Class II, Class IV in the CourtTbl, field โ€œChargeTypeโ€ which is a Enum type. The โ€œChargeCategoryโ€ field in the VPRAITbl which is a Enum type, can only be one of three options, i.e. DrivingUndertheInfulence, NonViolentFelony, or ViolentFelonyFirearm and cannot be, NonViolentMisdemeanor or ViolentMisdemeanor.

Conversely, if the person is charged with a misdemeanor offense, i.e. Class A, Class B or Class C in the CourtTbl, field, โ€œChargeTypeโ€ which is a Enum type. The โ€œChargeCategoryโ€ in the VPRAITbl which is a Enum type, can only be one of three options, i.e. DrivingUndertheInfulence, NonViolentMisdemeanor or ViolentMisdemeanor.

The CourtTbl and the VPRAITbl are connected via a common ID field, one to one relationship.

This is essentially a Dependent Dropdown between two tables and I am having trouble either understanding how to set it up or how to write the expression. I am thinking, โ€œValid Ifโ€, but again am lost.

Thank you for any assistance!

Hi @apocunningham Dependent dropdowns may be what you are after.

Try as the Valid If expression for the ChargeCategory column of the VPRAITbl table:

IFS(
  IN(
    [CourtTbl ID].[ChargeType],
    LIST(
      "Class I",
      "Class II",
      "Class II",
      "Class IV",
      "Class X"
    )
  ),
    LIST(
      "DrivingUndertheInfulence",
      "NonViolentFelony",
      "ViolentFelonyFirearm"
    ),
  IN(
    [CourtTbl ID].[ChargeType],
    LIST(
      "Class A",
      "Class B",
      "Class C"
    )
  ),
    LIST(
      "DrivingUndertheInfulence",
      "NonViolentMisdemeanor",
      "ViolentMisdemeanor"
    )
)

The IFS() function evaluates a sequence of (if-this-is-true, then-do-this) expression pairs until it finds an if-this-is-true expression that succeeds, then it evaluates the corresponding then-do-this expression and stops.

Each if-this-is-true expression attempts to match one condition you need to handle, like matching a ChargeType column value from a linked row in the CourtTbl table.

The corresponding then-do-this expression should then produce the appropriate result, such as the list of allowable ChargeCategory column values for the ChargeType value matched by the if-this-is-true expression.

An IN() expression tests whether a given value, such as the ChargeType column value from a linked row in the CourtTbl table, matches any value in a given list.

[CourtTbl ID].[ChargeType] is a dereference expression that locates the row in the CourtTbl table that has a key column value that matches the column value of this rowโ€™s CourtTbl ID column value. From that found CourtTbl row, the ChargeType column value is returned.

The result of this IFS() expression will be a list of values. When a Valid If expression produces a list of values like this, the form will display a drop-down menu allowing the user to select only one of the listed values for that column.

THANK YOU SO MUCH for providing the expression and rational as to how it works. I tested the expression as it is written and Iโ€™m getting an error: Error in expression โ€˜[CourtTbl ID].[ChargeType]โ€™ : Unable to find column 'CourtTbl IDโ€™

I think the error is a result of the column label. Based on my understanding of what you wrote, which is admittedly poor, I think you have the column label in the CourtTbl as โ€œCourtTbl IDโ€ which is incorrect and probably the result of my poor explanation. The primary key of both the CourtTbl and the VPRAITbl is โ€œIDโ€ and not โ€œCourtTbl IDโ€. Does this make sense? The label of the Field is โ€œIDโ€. with this being said should it be changed to:

[CourtTbl].[ID].[ChargeType],

or

[CourtTbl].[ChargeType],
[ID] . [ChargeType],

Again, thank you for your assistance!

Hello,

I figured it out, yay!

It should be as follows:

IFS(
IN(
[ID].[ChargeType],
LIST(
โ€œClass Iโ€,
โ€œClass IIโ€,
โ€œClass IIโ€,
โ€œClass IVโ€,
โ€œClass Xโ€
)
),
LIST(
โ€œDrivingUndertheInfulenceโ€,
โ€œNonViolentFelonyโ€,
โ€œViolentFelonyFirearmโ€
),
IN(
[ID].[ChargeType],
LIST(
โ€œClass Aโ€,
โ€œClass Bโ€,
โ€œClass Cโ€
)
),
LIST(
โ€œDrivingUndertheInfulenceโ€,
โ€œNonViolentMisdemeanorโ€,
โ€œViolentMisdemeanorโ€
)
)

I have tested it and everything seems to be working fine. My impression is that I needed to first match the ID field in the current table, i.e. VPRAITbl to the ID field in the table where the ChargeType is located, i.e. CourtTbl. Am I correct in my thinking? If so, this is a milestone for me

Again, Thank You So Much!

Awesome! Congratulations!

The ID column is a reference column: the column value itself is the key value that identifies a single row in another table. Imagine a key value a street address. The ID column is the note card you wrote the address on. [ID] is how you tell AppSheet to use the address written on the note card.

The construct, [ID].[ChargeType] is a dereference expression. It says:

  1. Look at address on the ID note card.
  2. Go to the address on the ID note card.
  3. Once there, find a note card labeled ChargeType.
  4. Come back and tell me what the ChargeType card said.

I donโ€™t know if this will help. References are one of the single most difficult concepts to understand.

Your Step by Step explanation of the dereference expression is a good example. I am going to hang on to this. As I mentioned, I have a number of fields in the app that have the potential to cause mistakes if the Probation Officer is not careful when moving from one table to the next. Basically, he needs to remember how he answered something two tables ago, which creates the potential for mistakes. I should be able to reduce a good deal of this with your explanation.

Again, Thank you so much for your assistance.

Top Labels in this Space