key and label confusion

[AndGab]

I have a data table with the data of the workers ('Workers') and I have a table ('Works') in which the worker's name is shown in the dropdown ('Worker name'). A Related Works list (REF_ROWS("Works", "Worker name")) is created in the 'Workers' table. The initial value of the 'Worker name' field of the 'Works' table is the name of the logged-in user, retrieved from the workers table (LOOKUP(USEREMAIL(), "Workers", "Email", "Worker")). If the worker's name is both the key and the label in the 'Workers' table, everything works fine.

However, sometimes there would be a need to change the 'Worker name', but this is not possible with the key field. Therefore, the 'Workers' table received an 'ID' field (UNIQUEID). This became the key, 'Worker name' remained the label. If the initial value is unchanged, I get a yellow triangle warning, so I rewrote it to: LOOKUP(USEREMAIL(), "Workers", "Email", "ID"). That's fine, but when entering data, the 'ID' value is added to the table, not the 'Worker name', even though it's still the label.
I also tried creating a 'WorkerID' field in the 'Works' table and connecting the tables through it, but I got the same result.

I read a few posts on a similar topic. Maybe I'm too much of a beginner, but the solutions described there didn't work for me.

What should I do differently?

[WillowMobileSys · Accepted solution]

For Ref columns, the value stored in the table/sheet MUST be the Row Key,  When you change the key in that referenced table to a different column, you MUST manually update all existing Ref column values to that new key value.

The Label value is there for app display purposes of Ref Columns - app display only.  The column set as the Label is automatically displayed in the app views where it is shown.  Anywhere else, such as expressions or templates, where you want to use that Ref value to select or match by worker name, you would use a concept called "dereferencing" to access the name value.  So if your Ref column is called [Worker Name], you access the actual name like so:

[Worker Name].[Name]

Or whatever the actual Workers table name column is.

The above expression looks odd, right?  It might help to think of Ref columns as a pointer to the entire row and not some access to particular column on that row.  Meaning, through that Ref column you have access to ANY of the row columns not just the name.  So, I typically would name the Ref column as [Worker] instead of [Worker Name] giving an expression like [Worker].[Name], but then I could also have [Work].[Phone] or [Worker].[Address] or [Worker].[Email], etc

lastly, You have experienced the classic reason why NOT to use row data as the row key.  It is strongly recommended to use a dedicated dataless key - i.e. the column is used as a row key and a row key ONLY.   It is okay to use the Row Key as an identifier such as Report ID or Quote #.  But you do not want to use an ID created externally as the Row key such as an Employee ID,  SSN, Drivers License number, Passport number, etc.  These items are out of the apps control and can potentially change.  Though it may be rare, it's not worth when they do change, requiring manual updates, just to save a few columns in an app.

[AndGab]

Sorry for the question, but I'm using a translator and I'm a beginner, so it's hard for me to understand.
Where exactly should I write this: [Worker Name].[Name]? In the formula section of the 'Worker' column of the 'Works' table? Isn't it? Then the 'Worker name' dropdown would not work. Or in the 'Related Works' section of the 'Workers' table?
I am attaching two screenshots. The first is the 'Works' board, the second is the 'Workers' board.

[WillowMobileSys]

I see you updated the Ref Column name to [Worker].  I was informing you that if, in an expression for instance, you need to find Works rows by the workers name you would not use the [Worker] column by itself.  Instead you use [Worker].[Name] = "some name value to match".  

[AndGab]

But I don't want to find the name of the worker, but the user selects it from a dropdown, so Ref, which refers to the 'Workers' table. Sorry I'm having a hard time understanding you.

[AndGab]

I hope I made it clear. The name of the 'Worker' appears in the drop-down menu, the user can change this based on the list referenced from the 'Workers' table.

[WillowMobileSys]

I was giving extra information.  Are you still having any issues?

[AndGab]

As I mentioned, I don't understand in which part of which field of which table I should enter [Worker].[Name]
That's why I sent the screenshots so that the correct part can be named.

[WillowMobileSys]

[Name] was just an example column I made up because I didn't know what you were calling the column.  I see now you have named it [Worker].  Because you have also named the Ref column [Worker], the correct expression FOR YOU to get the name when needed for an expression in the "Works" table is [Worker].[Worker].

As I mentioned, this was extra information.  You do not need to put it anywhere.  I was only pointing out how you would get the Worker name value IF you needed to.

Going back to your original post - A Ref column DOES write the ID column value to the row.  The Label column is only used in views to display a human readable value instead of the ID value.  That is the correct way for it to work.

Are you currently facing any other issues you are trying to resolve? 

[AndGab]

Thanks for the clarification, I think I understand it now. As the Ref column could only write the ID column value to the row it makes it a bit harder to work with that data furthermore but I think I can do a workaround solution for that, so people can understand the data there instead of seeing some generated ID.

Thank you for your help!