Related Table Conditinal Adds

[RodolfoLima]

Hi,

I'm creating this app for financial management and I built these connections:

Table - Order Details (Products, Price Per Unit, Quantity, Total per product)

Table - Sales Order (Client, Date etc)  Parent Table has Details and Method REFs

Table - Order Payment Method (Total Order Amount(sum of total per product), payment method etc.)

Everything is working fine, but I wish to create a condition where the sum of payment method amounts couldn't be bigger than the Total Order Amount, or even I can't add more payment methods, after the sum of Payment Method Amounts is equal to the Total Order Amount.

PS. Payment Method Amount will be a sum of Down Payment + Installment

[RodolfoLima]

I was thinking about creating a virtual column that checks the remaining amount of the selected order_id and use a Valid If, but it doesn't work, the column remains on zero:

[Total Order Amount] - 
(
SUM(
SELECT(
Expenditure Payment Method[Down Payment Amount],
([expenditure_id] = [_THISROW].[expenditure_id]), FALSE))
+
(
SUM(
SELECT(
Expenditure Payment Method[Installment Value],
([expenditure_id] = [_THISROW].[expenditure_id]), FALSE))
*
SUM(
SELECT(
Expenditure Payment Method[Installments Number],
([expenditure_id] = [_THISROW].[expenditure_id]), FALSE))
)
)

[RodolfoLima]

This is the formula that I need to put in table perspective, instead of row:

[Total Order Amount] - ([Down Payment Amount]+([Installments Number]*[Installment Value]))

[RodolfoLima]

So I finally did It, I added a new virtual column to calculate the total installment amount. The final result looks like this:

Total Installment Amount

[Installments Number]*[Installment Value]

 Remaining Amount

[Total Order Amount] - (SUM(
SELECT(
Expenditure Payment Method[Down Payment Amount],
([expenditure_id] = [_THISROW].[expenditure_id]), FALSE)
)
+
SUM(
SELECT(
Expenditure Payment Method[Installment Total Amount],
([expenditure_id] = [_THISROW].[expenditure_id]), FALSE)
))

Use a Valid IF 

[Remaaining Amount] >= 0

Configure an error message for the user:
"The sum of Payment Methods is Bigger than Total Order Amount"

and BOOM! It's totally working! yay!

[RodolfoLima · Accepted solution]

Let me just make another correction I have to consider the form being filled on the formula

[Total Order Amount] - (SUM(
SELECT(
Revenue Payment Method[Down Payment Amount],
([order_id] = [_THISROW].[order_id]), FALSE)
)
+
SUM(
SELECT(
Revenue Payment Method[Installment Total Amount],
([order_id] = [_THISROW].[order_id]), FALSE)
))
-
(
[Down Payment Amount]+
([Installments Number]*[Installment Value])
)