Is this an unfiltered portal or are you using a portal filter to omit the paid invoices? Sum will compute a total based on the relationship but not any portal filter you might define as it does not refer to the design of your current layout. To use Sum would require using an unfiltered portal--an additional pair of match fields would have to filter the related records in the same fashion.
But ExecuteSQL can compute the same total and use a WHERE clause to filter out the paid invoices much like the portal filter.
And you can make a copy of your filtered portal, reduce it to a single row in size and put a summary field defined in the invoices table that computes a total and this value will reflect the filtering action to show a total based only on unpaid invoices.
Of course it would have to be a difficult fix =). Yes I am using filtering to give me paid invoices in one portal, and unpaid in the other portal. I am kind of a novice and by the sounds of it the fix is over my head..... How difficult would you say it is to re-organize and do the matching fields thing.....?
The first and third options are pretty simple. The second option is the challenge as it requires a working knowledge of SQL and a willingness to do battle with an implementation of SQL that is not as user friendly as we might want.
Let's say that you have a field that stores either "Paid" or "Unpaid" to show the status of that Invoice and we'll call that field Status for the purposes of this example.
Define this field in customers, named constPaid:
and select text as the result type.
Then this relationship:
Customers::__pkCustomerID = Invoices|Paid::_fkCustomerID AND
Customers::constPaid = Invoices|Pad::Status
will match only to Paid invoices with that customer's ID.
Note: Invoices|Paid is my name for a new Tutorial: What are Table Occurrences? of Invoices. You'd add another new occurrence of Invoices for linking up unpaid invoices in the same fashion but with a different calculation field that matches only to "Unpaid" instead of "Paid" invoices.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Ok, I am lost, I am not sure if I understand exactly what it is you are explaining to me. Any interest in solving this for me I can email the file to you? If you have any interest, how much do you think it would cost?
If you can apply what I am suggesting, you are then in good shape for generalizing the concept to other parts of this database and others.
I suggest that you ask follow up questions about the parts you don't understand.
What I am describing here requires creating two additional table occurrence "boxes" in Manage | Database | Relationships. If "table occurrence" is a new concept, please see this thread: Tutorial: What are Table Occurrences?
But if you'd rather I did the work for you, send me a private message. I don't provide quotes in public and never without first examining a copy of the file in question. Best guess is that this wouldn't take much more than 15 minutes to set up using the table occurrence method.
Did you get my private message? I asked for an email address to send the file because I didn't see a way to send it in the private message?
I spent yesterday transporting a family member to a medical specialist some 2 hours drive from here so I have not yet checked my PM's. You can upload a file to a sharing site such as Drop Box and then include the link in the private message.