How to subtract summary fields in two different related tables

[chrisknight55]

I have a parent table: CUSTOMERS

I have a related table: INVOICES

and

I have a related table: PAYMENTS

On an individual Customer detail layout I have portals for both Invoices and Payments

In the related table INVOICES I have a summary field SumInvoices

and

In the related table PAYMENTS I have a summary field SumPayments

both show on the Customer detail

When the summary fields are in two different tables,

how can I find the balance due?

SumInvoices - SumPayments to get BalanceDue

Thanks

Or should I combine them into one table INVOICES_PAYMENTS?

[Aussie John]

I have a parent table: CUSTOMERS

I have a related table: INVOICES

and

I have a related table: PAYMENTS

On an individual Customer detail layout I have portals for both Invoices and Payments

In the related table INVOICES I have a summary field SumInvoices

and

In the related table PAYMENTS I have a summary field SumPayments

both show on the Customer detail

When the summary fields are in two different tables,

how can I find the balance due?

SumInvoices - SumPayments to get BalanceDue

Thanks

Or should I combine them into one table INVOICES_PAYMENTS?

You could use Sum(relatedtable::paymentfield) in invoices to calc the payments received and create a self join for invoices to do the same thing for the sum of the invoices

ie sum(selfjointable::invoiceamount).