Angelus81

Question: Inventory prices varied with different customers

Discussion created by Angelus81 on Aug 24, 2016
Latest reply on Aug 25, 2016 by TKnTexas

My idea for my database is to have an inventory table that every products have their own standard price (FOB), but their selling price will be varied with different customers. So, when I create an order invoice, the product's price will be shown differently based on the customer that I selected.

 

Therefore, I have created a database with the following tables: Customers, Order, OrderLineItem, Inventory, and CustomerPrices. Please see below images for the fields and their relationships

 

Fileds.png

The formula for OrderLineItem::Price is:

 

Case (

not IsEmpty ( CustomerPrices::CustomerPrice ) ;

CustomerPrices::CustomerPrice ;

Inventory::FOB

)

Relationship charts.png

 

And here is the browse view for my "Inventory" table and "Order" table:

Inventory & Order broswe view.png

I am not sure where I did wrong, but no matter what customer that I selected in the "Order" table, the product's price will only show the customer's price on the first row.

 

If I changed the formula for OrderLineItem::Price to:

 

Case (

not IsEmpty ( Customer Price_for OrderLineItem::CustomerPrice ) ;

Customer Price_for OrderLineItem::CustomerPrice ;

Inventory::FOB

)

 

It will only show the standard price (the FOB).

 

Thus, I am kinda stuck here. Can anyone help me out with this? Thank you!!!!

Outcomes