Dynamic Nested table - Product Prices based on customer

[Phil8055]

Hi everyone,

I run a bakery/pastry business. We sell to a variety of business such as restaurants who tend to buy our pastries as desserts or convenience stores who buy our bread and cookies…

Each client has its list of products that we sell to (varies from client to client), and a lot of client have negotiated prices.

I’ve created a sales app for my sales person. So far, I have been handling custom prices for clients in the following way:
In the CLIENTS table, each client is assigned one of three price profiles: B2B_std, B2B_custom#1, B2B_custom#1
In the PRODUCTS table, I have a price column for each price profile: [PriceProfile_B2B_std], [PriceProfile_B2B_custom#1] and [PriceProfile_B2B_custom#2]
In the sales app, I use a lookup function to dynamically choose which column to pull from in the 4th argument.

It works fine for a limited number of pricing profiles. But as we grow and negotiate different prices with different clients based on volume, I would like to

1. be able to customize the price of each producto for each client,
2. create a client “card” or “detail” view where I can select/deselect from all the products we have and assign a custom price if needed
3. In the sales app, in the sales ticket view, filter only the products we currently sell to the selected client in the sales app dropdown.

Any thoughts on how to go about this?

Many thanks in advance

[Marc_Dillon]

In addition to your Product and Client Tables, you should have a Table to hold 1 record per Product per Client, which would also include a price column which can be the standard price, or any other negotiated price.

[Phil8055]

Thanks @Marc_Dillon. That sounds great.

In your experience, is there any easy way to create 1 record per cliente/product? And is there a way to have that being dynamic? I.e. when I add new products, is there a way to have those new products appear directly for existing clients?

And similarly, if I create a new client, is there a way to automatically create all the product per client rows?

At this point, it’s still manageable manually…

[Marc_Dillon · Accepted solution]

You should search up on the following:

1. Running Actions as Form Saved Behavior
2. “Reference” Actions (Action type of "execute an action on a set of rows).
3. Action type “add new row to table”

[Phil8055]

Thanks Marc. As always super helpful.