I have really gone all around google, for 4 hours now..pleeeeease help.

I have a Clients(client_id, name) table related to an Invoice(invoice_id, _client_id), which is related to Documents(_invoice_id, status, price)

.

Documents can have different status (paid, unpaid, etc)

I need to add the price of all unpaid documents for a client...as simple as that... but I can't!! How would you do it??

You need a relationship from Clients to Invoices to Documents that includes the status. So, add a global calc field in Invoices that equals the status that you use for unpaid. Is it the word "unpaid" perhaps? Then create a new relationship from Invoices to Documents. This relationship has invoiceid=invoiceid and gStatus = status.

Now, create a calc field in clients=

sum ( clients_invoices_Documents_unpaid::Price).

Another way is to define a calculation field in Documents =

Case ( status = "unpaid" ; price )

Then you can sum this field from Clients as =

Sum ( Documents::cUnpaidPrice )

This saves having to create another relationship just to pull the sum() because it can be pulled through the existing relationship! A field in the child table is much more practical than a field in parent (to use in new relationship) AND a new table occurrence.

I like this very much!

Absolutely. Use comments' method.

Each method has its pros and cons. Using an extra relationship based on a global filter field enables user to switch quickly from paid to unpaid (or other types, when there are more). The calc field is cheaper to implement, but it is hard-coded to a type.

Ah yes. But I confess that I have static calcs with only a word in the parent just to filter a relationship. So use filtered relationship when you need to change the filtered results; use field in child when you don't. But DON'T put one-word calc in parent AND a relationship. I confess I've done it!

As with everything FM, there's more than one way to skin a cat. Depends on how expansive your needs are. I skimmed over the status field being set to "Paid" or "Unpaid". I would actually store a paid date, and calc the status.

I would actually store a paid date, and calc the status.

So would I (store the paid date). Then you don't need to "calc the status", since the date field IS the status as well.

Hey! thanks a lot to BCooney, comment and Laretta!!!

This discusion opened my mind !

I am glad there are people like you out there willing to help.

Unless you have multiple payments and status paid must look to see if there's a balance due. We could go on and on...lol.

I used the first solution, the one with globals and new tables in the graph.

Now my problem is that portals from the Clients table won't show "unpaid" invoices... (but calculations work fine.. weird?)

OK. If you used the solution where you have a hardcoded global in the relationship that is equal to your paid status, then that relationship can only be used for "paid" invoices.

You need to base the portal that you want to show all documents on a relationship that only has inv::invoiceID=docs::invoiceID. Remember, the fields in the portal should match the table occurrence of the portal.