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

[JCarlos]

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], [Price], [% discount], [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.

[dbaum]

In the SALES DETAIL table, add a (likely virtual) column to calculate the line item subtotal with tax. Conceptually: [Subtotal] - [% General discount] + [Tax]. To do so, you can access the [% General discount] column via a dereference expression. Then, in the SALES table you can sum that new column. For example: SUM([Related SALES DETAILSs][Subtotal with Tax]) (i.e., using a list dereference expression).

[JCarlos]

Thank you for your collaboration dbaum.
The problem is that if I change the [% General Discount] after adding the products, the data in the "SALES DETAILS" table is not updated and the calculation would not be correct.

[JCarlos]

I tried with an automation but it takes a long time to sync, I hope there is an easier way to do it

[dbaum]

Try using a virtual column.

[JCarlos]

I have created a virtual column in the table "SALES DETAILS" named [Total row]
with the calculation ([Subtotal]-[Sale ID].[%General Discount]+[% Tax]) and in the [Total] column of the "SALES" table in the application formula SUM([Related SALES DETAILS][ TOTAL row]).

When I change the [% General Discount], it does not do any calculation, I have to open the SALE DETAILS one by one and save them so that it can do the calculation.

[JCarlos]

Is there any other way to do this?

[dbaum]

Quick Update System - How to update records in you... - Google Cloud Community  

[JCarlos]

Hi, dbaum
I appreciate your dedicated time.

I have already tried something similar before, even running it from a bot, but this can be somewhat confusing when saving or canceling the sale from a button and from a bot it would be after updating or saving.

I will keep trying