How to change the Enum's values based on a set of ...

djmalaka · 03-16-2020 01:00 PM

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.

[OT Date]
[OT Room] -> This is an Enum where values are [OT - 1, OT - 2, OT - 3, OT - 4]
[Time] -> This is also an Enum where values are [Morning, Evening]
[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!

Steve

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])
    )
  )
)

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)).
AND(..., ..., ..., ...) requires the row from the Full DB table match all of the given criteria (..., ..., ..., ...; see (3), (4), (5), and (6)).
([_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.
([_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.
([_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.
([_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.
LIST(1, 2, 3, 4, 5) is the list of possible Surgery Order values.
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:

djmalaka

@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.

leonsteber

Hello - apologies for jumping into an old thread.

I have a similar use but much simpler and so want to use @Steve 's expression but I'm lost where it actually goes.

I have my column with type ENUM and base type TEXT (or should this now be LIST?).

I have only one value "Edit Pump DATE, START and STOP times?" which I use for CONTEXT("view")="PUMP FORM", but I have another form for "RAIN FORM" and instead of creating a new column/enum I'm hoping to simply change my value to "Edit Rain DATE?"

Cheers!

Leon

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