AnsweredAssumed Answered

How to relate and integrate child of related records?

Question asked by Cécile on Oct 12, 2018
Latest reply on Oct 13, 2018 by Cécile

In my ProductsPriceLines table, I have the fields

ProductIDMatchField

PriceID

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)

RequiredDeposit

etc.

 

In my EstimateLines table, I have the fields

Name of Item (which is the match field with Item in the Products table)

SelectedPriceCode

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

Product ID

Item

PriceMatchField

 

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.agraph.png

Outcomes