How to build a database of product SKUs, each customer assigned specific SKUs?

[Kevinrcup]

I've tried searching but cant figure out the best setup. Any help would be appreciated.

Lets say I have 5 product SKUs. And I have 10 customers. Each customer is assigned specific SKUs. Example, customer 1 is assigned SKUs A and B. Customer 2 only assigned SKU A. Customer 3 assigned all SKUs A to E. An order form would display only their assigned SKUs.

I understand creating tables for customers and table for SKUs. But how would I assign the SKUs to the customer and create an order form that displays those assigned?

[RedVox]

Many-to-Many relationships require a junction table.   I suggest you create a third table, let's call it customer_sku_assignments.  Each record in the new table holds two columns: a customer reference and a sku reference.  With this in place, once an order form has a customer value, the list of skus can be constrained to the relevant skus with a formula in the suggested values for your sku enumlist field.  It would be something like: SELECT([sku]customer_sku_assignment, [customer] = [_THIS_ROW].[customer]).  

[Kevinrcup]

Thanks, RedVox. I'm struggling with a couple things. First, when I create a new customer, I dont see the option to assign SKUs to them. I have to do that separately.

As I figure this out, I started with a template from AppSheets demo order app. Here is my demo app. How can I get it when adding a new customer I have the ability to assign products?

[Rifad]

There are two ways to do it.

1. Create a child table ‘Product SKU’ and reference it to SKU table. Have a column to choose the customer. Add new rows to child table from SKU table. Use ‘Product SKU’ table to do this below using a VALID IF select expression.

---

@Kevinrcup wrote:

An order form would display only their assigned SKUs.

---

2. Create another column as ENUMLIST base type Ref to Customer table. In the order form validif you can use select() and an IN() with your conditions in table ‘SKU’ directly.

Both ways you can achieve the results.

[Kevinrcup]

Thanks, @Rifad . Both solutions make sense. However, is it possible to have the order form auto display any assigned SKUs and show an input field?

For example, my order form currently has a column for each SKU and after a customer is selected, those columns are displayed based on assignment. The number of SKUs and columns is becoming too much so I'm trying to change my format. However, I can't replicate this "display input field if" order form entry

Before selecting a customer (in this case venue) - no SKUs shown:

after selecting customer - only assigned SKUs shown:

[RedVox]

Yes, I don’t think you want to put yourself in a situation where every time
there’s a new sky, you would need to add another column!

If you use the suggestion I suggested, the junction records would be
children of the client record. The will make it very easy to associate new
customer/sku records with the customer. Whenever a new sku comes
available, it will simply be a new option on the list of options for a new
customer/sku junction record.

[Rifad]

I would never ever suggest using this method.

---

@Kevinrcup wrote:

---