Changing the values of a column based on a parent table form input field

[Henrique-C]

Hi,

I have a quote app which has a Discount input field in the Quote table and I need to distribute this discount among the quote items, but proportionally to the price of each item, as below:

There are 3 tables with this structure:

Products (Read-only) - ProductID (key), Price

Quote - QuoteID (Key), Discount, Totals

Items - QuoteID (Ref - Is a part of Quote), ProductID, Price, Discount

Is there any solution for that? Thanks in advance.

[Marc_Dillon]

I just solved almost this exact problem recently. You need to set up a reference action to run a data change action across all children. In the data change action, you'll set the discount value per item with a formula like:

{total-discount} * ( {this-item's-value} / {total_value_of_all_items} ).

Basically, multiply the total discount by each items percentage of the total.

The tricky part is if the distribution has to equal exactly the total discount, but the value doesn't split into the items evenly (or decimals are getting rounded). I'm not sure if this applies to you situation though, perhaps not.

[dbaum]

Conceptually, I think that either of the following approaches should work. You might need to fiddle with details like number/decimal type conversion.

([Quote ID].[Discount] / [Quote ID].[Total]) * [Price]

([Price] / [Quote ID].[Total]) * [Quote ID].[Discount]