How to using SELECT() function for ref column using 2 different tables?

[SumanKM]

I have 2 tables, namely: Employees and Roles.

In Employees Table, Role Name is a virtual column which is referenced by the Role column of the Employees Table.

When an employee signs up to use the app, automatically set values for these columns in the Employees table: 

For the Employee Role column, because it's a column of type Ref, set its initial value to be the Role ID of the row in the Role table that has a value of Staff in the Role Name column.

To implement this functionality, use the AppSheet SELECT() function.

I have given formula as:

SELECT([Role Name].[Role ID],([Role Name].[Role Name] = "Staff"))

Error Message: 

Column Name 'Role' in Schema 'Employees_Schema' of Column Type 'Ref' has an invalid Initial Value of '=SELECT([Role Name].[Role ID],([Role Name].[Role Name] = "Staff"))'. SELECT has invalid inputs

Role Table:

Role ID	Role Name	Description
1001	Admin	Administrator or Super user
1002	Manager	Store manager
1003	Staff	Store associate

Please check this select() function and help me.

Employees Table

Employee Table

Role Table

Role Table

Role Initial Value Formula

Role Initial Value Formula

[Tommy62]

Lesen Sie bitte diesen Thread: Basistyp-REF-zur-Dereferenzierung-Daten-Reduzieren und dieses tolle Erklärvideo: Zeigen Sie einem Benutzer nur seine Daten an 

[Joseph_Seddik]

And why don't you just put 1003 directly?

[Marc_Dillon]

I think you should more closely examine the examples in the SELECT article, which you linked in your post.

[Alex_K]

ANY(SELECT(Roles[Role ID], ([Role ID] = 1003)))

Hope it helps...