Many-to-many table relationship to set unique pricing per customer

Topic Labels: Formulas
778 5
Showing results for 
Search instead for 
Did you mean: 
7 - App Architect
7 - App Architect

I have tables named “Inventory” and “Customer”. What is the best way to create unique price list per inventory record for each customer?

I want to have => inventory A is $10 for customer A and $5 for customer B

5 Replies 5

I would create a third table called “Inventory prices” which links to the inventory item from the Inventory table, links to the customer and has the unique price. Would that work for you?

7 - App Architect
7 - App Architect

What i get is something like this

Can i make it like this?

Use case: it is easier to upload the latter format to my ERP system

The excel sheet is what you’re working with currently? Please explain again (with Airtable exmaple) what you’re trying to achieve :slightly_smiling_face:

Do you have a junction table?

If so, do you want to transform that junction table into a pivot table?

However, you cannot convert the results of the Pivot Table app to an actual table/tab in your base.

If your list of customers is small enough and does not change, you could use the junction table and a conditional rollup field for each customer in your Inventory table.

7 - App Architect
7 - App Architect

Yes i do have junction table named “Price List” and looks like thisimage

I dont understand enough about pivot table. But to be simple, i want to have something like this below picture where i can search the customer name easilyimage

Use case:

  1. I have to quickly search the price list per item for particular customer
  2. I want to upload to my ERP system where the structure as shown in second picture is preferable