So on an invoice, you pick the type (government, retail, non-profit), and you want to only have those available when making a selection. IOW, when government is selected, only the products/pricing for government will be available.
I would say the standard method of Invoice, LineItems, Products, with work. I would make the dropdown list to select products for line items a conditional value list that filters by type. Inserting would be either auto enter by calculation or a lookup.
Thank you Steve. however this does not answer my question. I was thinking perhaps some type of logical calculation, something along a case () or choose () that would auto-enter the appropriate price value, but no sure of syntax or how to do it. I am hoping to avoid a nested if () situation. what i would really like to use is some type of toggle to point to the correct field on the source table.
Case ( IndicatorField = "retail" ; Products::retailPrice ;
IndicatorField = "Government" ; Products::GorvernmentPrice ;
IndicatorField = "NonProfit" ; Products::NonProfitPrice
Will work with your current set up once you substitute your names and values for mine. (I only used 3 values for the indicator. You mention 4 but I only spotted examples for 3 in your post.)
What steve is suggesting is a good approach, but would require a separate pricing record for each price for each product. I think that's the more flexible approach, but it would require restructuring your data.
Thank you very much - that did it nicely. and yes you are correct that Steve's method would require the restructuring of data. very basic stuff here, but very grateful for your help. have a good day.