References between tables (foreign keys) in MySQL database

[Nilson_Cain]

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

[Steve]

help.appsheet.com

Row Labels

How to determine which columns are prominently displayed in a view.

[Nilson_Cain]

I’m not understanding how the row label can be assigned in the Ref popup. I also have multiple fields that are Refs in some tables, such as a requisition_type and requisition_status which reference tables defining names for those things, related by a numeric id. The row label description is worded to seem like only one is possible per table – and no way to assign the label to which field it belongs to.

[Nilson_Cain]

I can do a Valid If statement, which will put the item types in the Ref dropdown, but I need the numeric ID to be stored in the database, not the associated type name.

[WillowMobileSys · Accepted solution]

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

1. the column to be used as the Key (or Row ID)
2. 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.

[Nilson_Cain]

That’s what I needed. Thanks guys!

[Nilson_Cain]

I have a followup on this question. There’s somewhere in our app where this is not working. In this case, I have a table view listing rows in a table, but AppSheet won’t show the corresponding Display Names when you go to edit a row.

It works just fine when adding a new row from a form view. Is this a bug?

(This is showing the column configuration and it not working when editing existing row)

(Versus it working fine when adding a new row)

These are obviously looking at a different column, but they’re configured the exact same way.

Thanks.

[Nilson_Cain]

Okay, so I hadn’t fully understood what I was doing. This is what made it work properly:

[WillowMobileSys]

Awesome!! That is exactly right. If you don’t have a Label column specified, it will use the Key column by default. Sometimes that might be want is needed!