In my ProductsPriceLines table, I have the fields
PriceCode (each product has several entries for price, for historical purpose and because my client wants to be able to have various volume tresholds on top of a regular price. Price Code looks like this [Volume 10-50 2018 Q4] or
[Regular 2017 Q3]
TargetNetPrice, MaxSalePrice, CBTr (commission before taxes rate) and CBT. My CBT is unstored. CBTr is number type field calculated from the two prices the Product Manager will input in TargetNetPrice and MaxSalePrice I will put a trigger to recalculate it when/if these two fields get modified
RequiredDepositRate (the default deposit rate is in the Products table, if nothing is put in this field the default applies)
In my EstimateLines table, I have the fields
Name of Item (which is the match field with Item in the Products table)
USP Unit Sale Price (this corresponds to ProductPriceLines::TargetNetPrice * (1+ eCBTr e.g. 1+0,25) For the SelectedPriceCode
eCBTr Estimate's commission before taxes rate it takes the ProductPriceLines::CBTr by default but the sales rep can modify it (lower it). there is a validation that caps it at ProductPriceLines::CBTr value
eCBT Estimate's commission before taxes non stored recalculated if eCBTr changes
In my Products table, i have the fields
This is one of the sample solution (Estimates) that I considerably modified to build a tool for my client. At this time, as per the solution original design, the way the Estimates were generated was on a layout called Estimate Details, on which there is a portal where one would add lines to add items to the Estimate (or Purchase order if you want). Until now, I was able to modify it to add some complexity such as deposits, discounts .
However, now with the ProductsPriceLines, I don't know how to go about this. Can I put two selectors from different tables on one portal line? In the layout Estimate Details (Estimates) the Portal based on Estimate Lines table, on each row Name of Item is the field/selector for the product and SelectedPriceCode would be the field/selector for this product's related price the sales rep choose when building the estimate. ProductsPriceLines is not related to EstimateLines so I am not clear how to articulate EstimateLines::SelectedPriceCode and ProductPriceLines::PriceCode in the portal.