Solved: References between tables (foreign keys) in MySQL ... - Page 2

Nilson_Cain · 10-18-2019 07:00 AM

I am working on an inventory type app that I want to link up with my MySQL database. I have, for one example, a master list of possible items for the inventory – the items table has a primary key (item_number), and a field (type) which is a number, referencing item types defined in another table eg.:

item   
  item_number    type    name
  12345          4       Large Glove 100ct

item_type 
  id   name
  4    GLOVES
  5    MISCELLANEOUS
  6    OXYGEN
  7    TRACH SUPPLIES

How do I have a field in AppSheet which displays the name from the item_type table, but stores the associated id? I tried using a Ref field type, but it shows only the ID from the other table. Surely this is possible?

Thank you

WillowMobileSys

When you create an AppSheet table, i.e. add your SQL table into Appsheet, you can then specify in the column definitions section:

the column to be used as the Key (or Row ID)
the column to be used as the Label (or display value).

AppSheet will store in your rows, the value of the column specified as the key.

When it comes time to display that column value, AppSheet will automatically use the column specified as the Label. This is true for selected values in rows as well as list values in a popup list.

In your case, mark your name column as the Label. Add a new row. You should see the Names in the popup list. Save the row and go to your table. You will see the ID value saved on the row in the column being used for the item_type.

View solution in original post

References between tables (foreign keys) in MySQL database