How to make reference to more than one table in a form

[oscarm]

Hi there, 

I am trying to make an app that has suppliers and deliveries listed in tables. Below is an idea of the schema:

Deliveries Table  : delivery_id (primary key) | delivery information columns | the supplier_id (ref column) | supplier_name. A delivery can have one supplier_id and supplier_name.

Suppliers Table : supplier_id (primary key) | supplier_name (usually unique although this is not enforced) | other supplier details columns.

When entering a new delivery to the Deliveries table via a form I am trying to make it so that there is a drop down which offers an enum list of supplier names taken from the Suppliers table. Once a choice is made I want:

1) the Deliveries table to automatically fill in its ref column supplier_id based on a search of the Suppliers table for the user provided supplier_name value. 

2) If the user wants a supplier_name that does not exist, for a prompt to come up asking that they create a new supplier entry.

--- 

The approach I took so far to tackle problem 1) is to use a LOOKUP in the column definition of supplier_id in the Deliveries table, however this seems to be returning the supplier_name rather than the supplier_id:

LOOKUP([_THISROW].[supplier_name], "Suppliers", "supplier_name", "supplier_id")

Appreciate any assistance that can be offered with this! Thanks!

[Steve]

---

@oscarm wrote:

1) the Deliveries table to automatically fill in its ref column supplier_id based on a search of the Suppliers table for the user provided supplier_name value.

---

Why not have the supplier_id column be the dropdown from which the user chooses the supplier? This would be a much simpler approach.

Further reading:
The "lookup/dropdown" data pattern - AppSheet Help

[oscarm]

Hi Steve,

Thanks for the suggestion.

The trouble is, supplier_id is a meaningless random string sequence from the user's perspective. If I could populate such a drop down with something meaningful i.e. supplier names which somehow then corresponds to entering the unique supplier_id then it might make sense, although I'm not sure how to do so (?) 

[Steve]

Add row labels - AppSheet Help

[oscarm]

Thanks again, Steve. That's a useful functionality for having labels at the ready in forms. I've tried it and now I do have a form with a supplier_id entry which shows a bunch of supplier_names in a dropdown. Trouble is, selecting one of those populates the supplier_id field with the supplier_name string... I'd like to be choosing a supplier_name but have it populate with a supplier_id... Am I missing something here? Thank you for getting me thus far! Already feels close to a solution! 🙏

[wilneedheart · Accepted solution]

maybe try the following,

In the Suppliers Table Definition,  supplier_name is label

In the Deliveries Table Definition, supplier_id is label (Ref type), and supplier_name formula is [supplier_id].[supplier_name] 

[oscarm]

Thanks so much Wilneedheart (and Steve for the set up and useful resources), that was exactly what I needed!