Help setting USER_ID based on USEREMAIL() in an Action

[Lucinda_Mason]

I have an action to set values in three fields when the user clicks it. Two fields work, simply setting one field as “Yes” and one with “TODAY()”.

I have a USERS table with USER_ID and USER_EMAIL. I want to compare the logged in user’s email, USEREMAIL() to the matching/ referenced USER_EMAIL in the USERS table and pull out the USER_ID to put that value in the 3rd field.

I just can’t get it to work even after researching dereferencing, LookUP() and more.

This is my latest attempt for the action setting the value:
IF(Users.USER_EMAIL=USEREMAIL(),Users.USER_ID,"")

These are the relevant fields in my main table, the ones I want to set with the action.

Could it be because I’m trying to set the value of field with a REF field type? If so, if I change it to text, how can I access that related record email in other parts of the app?

Thanks in advance for helping me through this MAJOR BLOCK.

Lucinda

[Steve]

Users.USER_EMAIL isn’t a recognized form of expressing anything; AppSheet sees it as "Users.USER_EMAIL", a text value. To reference the value of a column, enclose the column’s name in square brackets: [USER_EMAIL].

help.appsheet.com

Column Value Expressions

Write an expression to get a value from a specific column and row.

[Lucinda_Mason]

Thanks Steve,

I’m still missing something. I tried IF(Users.[USER_EMAIL]=USEREMAIL(),Users.[USER_ID],"")

This action is not on the USERS table. It is on my main table, JF_PI_PRAYER_REQUESTS. Is that why it can’t find the USERS table? If so, how do I pull data from the USERS table to populate the field I am trying to?

[WillowMobileSys · Accepted solution]

In general the “dot” notation is used when you already have a row identified, such as when a Ref column is set - it points to a specific row. Then you can do [Ref Column].[Other row column]

In this case for you, I think you need to use the LOOKUP() function:

LOOKUP(USEREMAIL(), "Users", "USER_EMAIL", "User ID")

[Lucinda_Mason]

You did it John. I made one minor field name correction, LOOKUP(USEREMAIL(), “Users”, “USER_EMAIL”, "User_ID”)

Thank you so very much.