Solved: Ref column doesn't work with SELECT FORMULA - Page 2

a_can · 06-05-2020 02:58 PM

So, I have an inspection app with three table INSPECTIONS, STAFF, AND LOCATION.
I assigned the user of the app as a default value to INSPECTOR column, column type is text and app formula is:

SELECT(STAFF[INSPECTOR], [EMAIL] = USEREMAIL(), TRUE)

By nature of the formula, it is working and the user of the app don’t need write his name to the INSPECTOR column.

The problem is ref column don’t accept app formula to evaluate the action above.

So I can not use reverse reference. Any idea is appreciated…

Steve

In order to dereference a value, the value must be of the Ref type. Based on what you’ve shared, I would suggest you replace your SELECT() expression with the following:

ANY(
  FILTER(
    "STAFF",
    (USEREMAIL() = [EMAIL])
  )
)

This will produce a Ref to the row in the STAFF table that corresponds to the current app user. Also change the column type for the INSPECTOR column to Ref (if the app editor doesn’t automatically change it) and set the column’s Source table to STAFF. Once the column type is properly set, you’ll be able to dereference the column value in expressions.