So I have the table LINES (of the estimate) and
the table PRODUCTS (lets consider the fields ItemName, Category, Solution).
In my layout Estimate details (table Estimates) I have the fields related to the Estimate (number, date, client, etc) and
a portal from the related table LINES.
On each line a product will be selected by the user.
Assuming that the product is going to be selected in a popover button dropdown control overlaying the primary key to hide it. I am trying to assess the best way to populate the line other fields. While some fields define certains options of the purchase (Qty, discount, volume pricing, etc) and therefore are going to be selectors, other fields are purely informational. Product description or Category, for instance. Those are the ones I am not sure which strategy - and consequently the fields definitions - is best. I believe there are the following options:
put the products::category field on the line; Pro: it will update everytime I change the product selection on that line. Pro: ::category is a calculated field that changes value according to some client attribute, any change to that attribute will cause recalculation of ::category so the update will be instantaneous in every layout where it is displayed.Con: the field cannot be used to select a product category to narrow the product search (but the disabled edit box could be used to display the value and a dropdown selector could be put on top and when keyed in, the product id field would be set to "" and the selection would narrow the product search) a bit convoluted...
I have noticed that you can store (index) calculated fields. What is the difference between the two options (stored or unstored)?
add a field "category" to the table LINES and do a lookup; Pros: if the client ever change the names of the categories, the name of the category on past estimates will be as it was at the time of creation. Cons: it doesn't auto-update so if the user change the product selection (trigger), a script must do a relookup. In that scenario, the fields definitions would both be text with the LINES::category being auto-entered with a lookup; Con: the field cannot be used to select a product category to narrow the product search (the change would change the record). However, as for above, a selector could be overlayed to achieve that or be in the product search popover
Are there other possibilities?