I don't know where to start, but I'll try. Please be patience with me. I'm new to filemaker. :)
Please see the attached file of the relationship of my tables... (I hope the linkages are right)
- I duplicate the table of "Invoices" (which contains quotations and invoice) to become "Invoices confirmed"(which I want to filter out only the invoices for calculation of the sales confirmed)
- my "status flag" field is a calculation that if invoice "status" is equal to 'invoice', then it should be 1.
- my Clients "ctrl_Invoice_status" is by default '1'.
- It works well when I have a unique client that has only 1 invoice under the client.
which means, when Client A has only Invoice A (status is 'Quotation'), I won't see the "total fees";
when Client A has only Invoice A (status is 'Invoice'), I will see the "total fees".
- The problem comes when... Client A has Invoice A (status is 'Invoice') and Invoice B (status is 'quotation'), I will see both "total fees" of Invoice A and B. Whereas I would only want to see the "Total fees" of Invoice A ONLY. And I realised "Invoices confirmed::status flag" always follow the "Invoices::status flag" in Invoice A.
I hope the explanation is clear. Could anyone tell me what went wrong please? :(
Thank you very much.