I am using an ID col as the Key, and the ref ...

Tammi_Canelli · 04-01-2018 05:00 PM

I am using an ID col as the Key, and the ref is the first name col, but then on the referenced sheets instead of first name in that col i get the ID.

Should the ID columns be the reference AND the key?

Phil

The Ref field always contains the key of the referenced record.

We always find records by using their key.

Tammi_Canelli

So if I need the first name to display what’s my best option? Add another col just for ref field?

Phil

See https://help.appsheet.com/data/columns/row-labels

tony1

@Tammi_Canelli Here’s an example app, too: https://www.appsheet.com/samples/This-app-shows-the-difference-between-keys-and-row-labels?appGuidSt...

Aleksi_Alkio

Basically it goes like this…

CustomerTable ID (Key column) Customer Name (Normally Label field)

Order Table ID (Key column) Customer ID (Ref type > Customer table)

Tammi_Canelli

makes no sense.

i can’t get the first name to appear no matter what i try.

in my tables the first and last name are separate columns.

last name appears just fine.

Tammi_Canelli

i need the name saved to the sheet not the id number

Phil

@Tammi_Canelli

The Ref must contain the key. That is how the referenced record is retrieved.

If you need to retrieve the name, or any other field value in the referenced record, you can always do so by using a de-reference expression.

For example, using Aleksi’s example, you could retrieve the customer name from the current Order record by writing: [Customer ID].[Customer Name]

If there was a Customer Address field in the customer record, you could retrieve it by writing: [Customer ID].[Customer Address]

You can use a virtual column to combine the first and last names to yield a label. This is described in this article https://help.appsheet.com/data/columns/row-labels

nyabynghi

I’m also struggling with this. I am testing our employee database on the app. I have an employee ID and employee name on one table. I have a leave table to track each leave taken by employees.

I want to make the employee name in the leave table the REF, but it is writing the ID to the sheet. I know this is the default behavior, but I can’t seem to find a solution to write the Employee name on the leave sheet that works for me.

Any suggestions? Thanks.

Steve

nyabynghi

Thank you for your time, I had actually been through those articles before posting. I am in the same predicament.

Steve

Alrighty! How about these?

eco_lbo

Hi
I have similar problems.
I want to make a drop down list to the products names not for theirs ID’s. According this link -

I only can use the KEY so it means the “name” needs to be the KEY. It make no sense… “Name” is not a key. In various examples you define the name as a key, which basically incorrect.

Steve

No, name needs to be the label.

eco_lbo

Steve
Thanks , It does helps, though , what if I need to use other column rather than the Name and I need it Sorted?
Thanks
Ben

Steve

If you’re using a Ref value, the only value that will be displayed in its place is the label column value.

To sort Ref values:

Mike_Venner

Did anyone get a straight answer for this? its very frustrating and makes no sense to me. My google sheets now display unique IDs instead of the company name which is the label. so when I go to analyse the data within something like Google Data Studio I get no useful information as its all letters and numbers and not English.

i really don’t see why I cant have the label displayed back in my spreadsheet as opposed to the unique ID. its like they are forcing you to use the app only to work within and not use any data from the sheet you used to create it within in the first place

Steve

Why not use the label column as your key column? There are good reasons not to, but if it bugs you so much and you aren’t able to work with separate key and label columns, you can redesign your app to make them the same.

Mike_Venner

Everyone says you should have a separate column for the unique IDs so that’s what i done. I have a unique ID and a column for company name as well as a load of other columns. the company name is the label and the unique ID column is the key. it all works fine in the app but the google sheet displays the unique ID in the company column instead of the company name.

Koichi_Tsuji

That s what it is.

Lynn

You could just add another column to your sheet for the name if you really need it and hide the one you dont need in the app.

DiegoSoares

There are things in the appsheet that generate rework. For example, in a table where there is an ID column “uniqueid ()”, and a Name column “Ref”. Where the “Ref” column refers to another table whose Name is defined as type “Name” and marked as LABEL, why not automatically transfer it to the spreadsheet in the correct way?
Even worse is when we set up a template for workflow, using the table parameters, in the case << [Product] >> and the app assembles the document with the ID instead of the name of the product.
It is frustrating, to see that something that could already be automated, has to be done a MacGyver to solve.

Marc_Dillon

It is transferring it the correct way, you just aren’t understanding the system correctly.

DiegoSoares

Marc, I understand that within the “programming logic” and databases, this can be on the right way. But we must remember that the AppSheet is a “No-Code” platform, in which it was proposed to “facilitate” the creation of Apps, both for more experienced users, as well as more lay users, like me. And users like me, expect to get an “obvious answer” regarding the information recorded in the spreadsheets. Exactly to facilitate the understanding of the data provided and to streamline processes.
My intention is not to create controversy, but to raise points that I believe that many users of the AppSheet have difficulties to understand. A good example is the creation of PDF documents, through Workflow, where we have to carry out a great process of “combination” of parameters and actions to be able to generate the PDF, and in my understanding, the AppSheet itself could provide a Simplest action to customize to generate a PDF.

Marc_Dillon

Just because some low/no-code platform is meant to be easier than typical coding development does not mean that anyone can just walk in with zero knowledge and build a complex app. There will always be knowledge barriers. Just like with using any other software.

Yes, you are correct, the issue with keys vs labels and what is or is not actually saved in the backend is a major knowledge barrier for many new Appsheet users. Myself included. You are also correct that these barriers should be talked about and brought to light. But just because the barrier is there, doesn’t necessarily mean there is any better way around it.

As someone who had the same confusions when I first started, I can now say that I understand how and why it works that way, and understand why it actually is the best way, and why it couldn’t/shouldn’t/wouldn’t work the other ways that people say they want it to work. In other words, this knowledge barrier is here to stay.

DiegoSoares

Ok Marc. To conclude this healthy “discussion”, just as you were a layman, and I am, please explain to me, why should it be the way it is, for me to accept and adapt to it? If you want to answer, of course.

Steve

It is the way it is because it serves a useful purpose. It’s not going to change, so you have no choice but to adapt to it.

DiegoSoares

Steve, I just want to know this. I don’t want to change anything, brother. Do not understand how arrogant on my part. OK?
As I said, I am a layman and asked a sincere question, being the person I am. I will be happy to learn more about why things work this way.

Marc_Dillon

Do I want to answer? Sure. Is it feasible for me to take the time to write a multi-page essay that would be required to fully answer that? Nope…

So, basically what Steve just said. And also I recommend you continue learning the platform, reading the help articles, and even learning how private keys and foreign keys work for relational databases. This should enlighten you as to why it is the way it is.

Steve

Our responses were tempered by your initial accusation that the current way is not the “correct” way. Perhaps you should adopt a less arrogant tone when wading into something you don’t understand.

(To the long-time community members, yes, I recognize the irony of my statement here.)

DiegoSoares

Dude, you, and Marc, are responding from the perspective of a programmer, brother. I’m raising a question from the user’s perspective! Is it possible to understand this? I think you were offended that I said it was the wrong way. But try to understand: As a user looking to see data collected in a form, I hope to see responses in a “viable” way, not in codes. I believe that this is the reason why this topic was created.
But it’s okay if you got it wrong, as if I put the whole development of the AppSheet in question.

DiegoSoares

But if you don’t want to give a basic explanation about it, since it can be complex, that’s fine. We are all good!
I thought that this community served a purpose of helping users, lay or not, of the AppSheet, answering questions, accepting suggestions and listening to our difficulties. But I realize that some members here, because they have more experience, put themselves in a conclusive position on certain questions. Anyway, thanks for your attention.

DiegoSoares

How can I solve this? For the document to show the customer’s name instead of the ID, as well as the other data.
This template was generated automatically by the AppSheet. I have to format it yet.

Steve

<<[Cliente].[Nombre]>>

assuming the column in the clients table that contains the client’s name is Nombre.