How to change the Enum's values based on a set of other data?

Hi,
I’m trying to get an Enum column’s values to not appear if already a value has been selected in a previous row.
In my app, the user will fill in the following details to fix a theatre session for a patient.

  1. [OT Date]
  2. [OT Room] -> This is an Enum where values are [OT - 1, OT - 2, OT - 3, OT - 4]
  3. [Time] -> This is also an Enum where values are [Morning, Evening]
  4. [Surgery Order] -> This is also an Enum where values are [1, 2, 3, 4, 5]
    These columns are present in a table called “Full DB”.

Let’s say the app user selects “March 17th, 2020” as the [OT Date], “OT - 1” as the [OT Room], “Morning” as the [Time] and “1” as the [Surgery Order] in order to fix a theatre session for patient A.
If the user goes to fix a theatre session for patient B and proceeds to select the exact combination of [OT Date], [OT Room] and [Time], I would like the [Surgery Order]'s Enum values to figure the value “1” has already been selected, and therefore only 2,3,4 and 5 values should be available for the user to select so that user wouldn’t be fixing the identical theatre fixture of patient A for patient B as well.

Is this doable?

Your advice is much appreciated.
Thanks!

Try:

(
  LIST(1, 2, 3, 4, 5)
  -
  SELECT(
    Full DB[Surgery Order],
    AND(
      ([_THISROW].[RowKey] <> [RowKey]),
      ([_THISROW].[OT Date] = [OT Date]),
      ([_THISROW].[OT Room] = [OT Room]),
      ([_THISROW].[Time] = [Time])
    )
  )
)
  1. SELECT(Full FB[Surgery Order], ...) gathers the list of Surgery Order column values of the Full DB table from rows that match the given condition (...; see (2)).

  2. AND(..., ..., ..., ...) requires the row from the Full DB table match all of the given criteria (..., ..., ..., ...; see (3), (4), (5), and (6)).

  3. ([_THISROW].[RowKey] <> [RowKey]) (replace RowKey with the name of the table’s key column) matches only if the key column value of the row in the form does not match (<>) the key column value of the row of the Full DB table. We only want to look at other reservations.

  4. ([_THISROW].[OT Date] = [OT Date]) matches only if the OT Date column value of the row of the form matches the OT Date column value of the row of the Full DB table.

  5. ([_THISROW].[OT Room] = [OT Room]) matches only if the OT Room column value of the row of the form matches the OT Room column value of the row of the Full DB table.

  6. ([_THISROW].[Time] = [Time]) matches only if the Time column value of the row of the form matches the Time column value of the row of the Full DB table.

  7. LIST(1, 2, 3, 4, 5) is the list of possible Surgery Order values.

  8. LIST(...) - SELECT(...) removes the in-use Surgery Order values for the current row’s combination of OT Date, OT Room, and Time choices from the list of possible Surgery Order values, leaving only those that are unused.

See also:




5 Likes

@Steve
That blew my mind! Thank you so much! I was trying various things and thought this wasn’t doable! This is golden and works perfectly. And thank you so very much for the explanation! Understood every bit of it.

3 Likes