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
The formula for OrderLineItem::Price is:
not IsEmpty ( CustomerPrices::CustomerPrice ) ;
And here is the browse view for my "Inventory" table and "Order" table:
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:
not IsEmpty ( Customer Price_for OrderLineItem::CustomerPrice ) ;
Customer Price_for OrderLineItem::CustomerPrice ;
It will only show the standard price (the FOB).
Thus, I am kinda stuck here. Can anyone help me out with this? Thank you!!!!