Reference function

[Krismar]

I keep struggling to understand how the reference function actually works.
I followed the tutorial here: https://support.google.com/appsheet/answer/10106510?hl=en&ref_topic=10101919#key-values

I have two tables with columns: "ID", "Product ID", "Quantity".
ID columns are both Key columns and Product ID is simply used to reference because they match with each other.

Table2 refers to table1 and ref column is "Product ID" and tried to pull the quantity value with this "[Product ID].[Quantity]" but nothing happened, the field is just blank. 

Another thing i do not understand is that i had yellow triangles when i first made the reference, table2 reference to column "product ID" in table1, after i switched the key column from "ID" to "product ID", the yellow triangles disappeared, why has the column ,which is being referred, to be KEY column?

[Marc_Dillon · Accepted solution]

---

@Krismar wrote:

I have two tables with columns: "ID", "Product ID", "Quantity".
ID columns are both Key columns and Product ID is simply used to reference because they match with each other.

---

This sounds like where you went wrong. One of your tables should be a "Product" table, and just have an [id] column that is the key, and not a [Product ID] column. Then your other table should have its own [id] key column, as well as the [Product ID] Ref column that references to the first table. The [Product ID] column will hold the same values that are in Product[id].

Your description of how the yellow triangle disappeared when you change the key to [Product ID] further reinforces that this is where you went wrong. You must have been putting the wrong values into the Ref column. Ref columns always hold key values of the referenced table.

[Krismar]

Ok It makes sense now. It cannot refer to random column, it needs the KEY to identify the row. 
Sound so easy and embarrassing now. 
Also i had another mistake, I had to create a virtual column with dereference expression to pull the value from the referred table but i had an actual column in the table which is why the reference did not work too.

Thank you!