Dropdown on Ref Column

[Mark_Krug]

I have a list of keys (actual physical keys for locks). Each key has a unique identifier. I issue those keys to people. When issuing keys I have a dropdown on a ref column with a Valid_if constraint which only lists for me keys that have not already been issued; I do this by list subtraction (list of all keys minus list of used keys=list of unused keys). This all works peachy fine until I want to edit the record. Lets say a person returns their key and I want to enter a date to the “Date Returned” column I find I’m getting an invalid entry warning on the key ref column. The key is now already issued and so there’s nothing available in the dropdown and the entry fails on Valid_If. I’m not creating a new record, just updating an old one. Any suggestions?

[Rafael_ANEIC-PY]

Oh hi Mark (sorry couldn’t resist), you could use the CONTEXT() function inside an if() like this:

IF(
Context(“View”)=“The view where you edit your records”,
TRUE,
Your actual Valid_if formula)

What this should accomplish is to only allow you to select any keys when editing a record, you could use list addition to make it only allow you to choose between the unused keys + the key in the record you’re editing, but for the purpose of changing the “Date Returned” field this should work.

Edit: forgot the

[Mark_Krug]

Thank you for the help. I’ll play with that tonight when I’m back home. In the meantime I gave the solution a quick whirl and came up with the error as attached.

[Rafael_ANEIC-PY]

My bad, based on the columns on your screenshot i’ll correct the expression as follows:

IF(
Context(“View”)=“Issue_Form”,
IKey[KeyID],
IKey[KeyID]-Issue[KeyID])

[Steve]

List subtraction is the right way to go, you just need to not subtract the current row’s value:

(
  IKey[KeyID]
  - SELECT(
    Issue[KeyID],
    ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
  )
)

[Mark_Krug]

Beautiful. 100%.