Reference between tables (Product x Customer)

[Eduardo_Sampaio]

Hi folks.
I’m new in Appsheet.

I’m having difficulties relating the product and customer tables. Each product has a unique id and I want to list many products for each unique customer.

Table Product (Keg)
1_RowNumber
2 id_keg (key & label)
3 keg volume
4 Status
5 Location

Table Customer
1_RowNumber
2 id_customer (key)
3 Name (label)
4 Phone
5 City
6 Address

How to reference and see the related products in customer in line view? In this way can be possible check how many products per id are allocated to Customers.

I’d like your help.

[graham_howe]

You need to either have some sort of field on one of the tables linking to the other, for example Allocated_Product_ID as a field on customer with a type of EnumList to allow for multiple products to be assigned. Or better still have a third table which records the relationship between customer and product. This table would have a column for customer ID and a column for product ID.

[Eduardo_Sampaio]

Tks for help.
Cool! Do you think that I can put Location and status fields in this table too?
the only edit fields would be status and location in this case.

[graham_howe]

If Location and Status are items to be picked from a list, the yes I would recommend they go in separate tables. I’m not sure I would put them in the same table which relates Customer and Product though unless they are dependent upon that relationship.

From the table descriptions you provided, it would seem to me that a Product has a status, perhaps New, Obsolete, Restricted, Premium etc. If this status is an attribute of the Product, regardless of which Customer has it, then it should be in a separate table referenced from Product. The same with Location, if that relates to the Warehouse, Distribution Centre, Van, Store or other place that the Product is being supplied from or stored at before being provided to the Customer, then I would have it on a separate table.

What I’m really talking about here is data modelling and there is a good reference from AppSheet here Data: The Essentials | AppSheet Help Center