AnsweredAssumed Answered

Relationship problem??

Question asked by altan on Jan 18, 2012
Latest reply on Jan 20, 2012 by altan


Relationship problem??


Hi there,

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)

  1. 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)  
  2. my "status flag" field is a calculation that if invoice "status" is equal to 'invoice', then it should be 1.
  3. my Clients "ctrl_Invoice_status" is by default '1'.
  4. 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". 
  5. 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.
Warmest regards,