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