Calculate total when updating field according to different percentages of child table.

[JCarlos]

Hello, I'm new to the community.

First of all I want to say thank you.

I can not find the solution to what I expose below, I hope you can help me.

I have a table "SALES" with the columns:

[ID], [Customer], [Date], [% General discount], [Total]

On the other hand I have the "SALES DETAIL" table related to the "SALES" table with the columns:

[ID], [Sales ID], [Product], [Units], [% discount], [Price], [Subtotal], [Tax].

In the column [Tax] the tax of each product is collected, these can be different as 21%, 10%, 4%... and there can be multiple rows with the same [Tax] and [Sales ID].

The [Subtotal] column calculates ([Units]*[Price]-[%Discount]) but not the [Tax], this must be calculated after the [%General Discount] of the "SALES" table.

2 discounts can be applied: [% Discount] for each row of the "SALES DETAIL" table and [% General Discount] for the set of rows of the same SALE

I want to make a function on the [Total] column,

so that in the SALES form I can calculate the [Total] of the sale in real time, how do I update the [% General Discount].

([Total] must calculate the sum of [Subtotal] - [% General Discount]+(the different [Taxes] that may exist))

Thanks for your support.

[RedVox]

Assuming the subtotal field is already applying the related discount for that row, then a  calculation in your total field is all that's needed.  I think you would use a sum() function with dereferencing to the child records, like:

Sum([Related Sales Details][Subtotal]) * [% General Discount].  If you put this in the App Formula field, it should automatically recalculate as you change the general discount.

Cheers!

[JCarlos]

Thanks for your support RedVox.

The Subtotal field calculates the (Units*Price -% Discount), but not the Tax, this must be calculated after the % General Discount