Solved: Ref column doesn't work with SELECT FORMULA

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.

See also:

View solution in original post

a_can

#open

Steve

Please explain in greater detail–I don’t understand what you mean. If there is an error message, please share a screenshot.

a_can

Hi Steve thank you for your response,

I have an inspector column which type is text, I made it in purpose and the column app formula is below
SELECT(STAFF[INSPECTOR], [EMAIL] = USEREMAIL(), TRUE)

with this forumula, I can capture users mail adress and refer it to inspector name column directly. So when user signed in they are not allowed to enter their names, they just take photos ant fill the forms.

But when the inspector name column is a test and I can’t make reference or any relation between inspections table and staff table. Therefore I can’t view the inspection that made by this user. (reverse Reference)

İs there any other way to create reverse references.

Thank you so much…

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.