Using id as key. With ref. that id gets back to sheet. Don´t want that! Getaround?

[Christian_Nere1]

Using id column as key. With ref. that id gets back to sheet. I want exe. “Name” from another column to get back in sheet. So I can change the name later the name cannot be key then…How to get around this.
Thanks in advance:-)

[Aleksi]

If you would like to see another column’s value in your sheet, there is no workaround how you could do that with the Ref. field. It always records the key column’s value. How about if you copy the name column as well into your spreadsheet with a app formula? Then you would have them both. Little bit difficult to see the big picture here.

[Christian_Nere1]

Thanks Aleksi!
Might be I am trying something that has to be done in another way. Working with foreigners often the name gets spelled wrong. Then it has to be changed, but that’s no go if its the key column. Hope it helps to understand the problem?

[Aleksi]

May I ask why do you want to record the name instead of the Key in your spreadsheet? If the name is a label field, it will work in your app and you can still change the name.

[Christian_Nere1]

Ask me anything:-)
When I made id key and name label it returns the key to the sheet. So I made name key and label…and then the prob. is that the name can not be changed.
As you mentioned it could be done with a formula. I read somewhere someone with the same prob. had that answer to. Like [id].[name] but didnt work at all. might give that a try again or? Looked like it didn’t work for the other person as for me, and the conversation ended there!

[Aleksi]

Still I would like to know why the key value in spreadsheet would not work for you.

[Heino_Matthee]

Not sure how this would affect your calculations and I’m not quite sure if I remember correctly, but I think
I solved that once with a slice of the table with the reference with only the field I want and referenced that slice.

[Steve · Accepted solution]

I’m guessing you’ve presented the user with a dropdown menu populated with references. The label value is displayed to the user, but the key value is what is written to your table. You don’t want the key value, you want some other value from the row referenced by the chosen key. For instance, you may want to store the value of the row’s name column. You want the value stored in a way you can modify it.

I would recommend keeping what you have currently, allowing the user to select from a dropdown menu that displays label values and stores key values as references. Then, add another column to receive the additional value you want from the referenced row. Set the Initial value property of this additional column to an expression that copies the desired column value from the referenced row: [id].[name]. This sets the value of the additional column to a copy of the desired column of the referenced row. Because Initial value is used rather than App formula to copy the value, the copied value can be modified.

By storing the reference, you can easily copy any number of values from the referenced row. You will also be able to compare the copied value to the original should you care to detect changes.

[Christian_Nere1]

Thanks Everybody for your input and Steve it works just fine:-)

[Madel]

Hi Steve, probably in this case it will not be a huge difference in performance if the formula is written directly in the sheet, or in the app, but in general, when you have the option what is better (app formula or sheet formula)?