8 Replies Latest reply on Jan 20, 2012 8:32 PM by altan

    Relationship problem??

    altan

      Title

      Relationship problem??

      Post

      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,
      Al

       

      Screen_Shot_2012-01-19_at_1.22.10_AM.png

        • 1. Re: Relationship problem??
          philmodjunk

          1) please confirm that you did this by using the duplicate button on this tab (Has two green plus signs). That's what you should have done and this does not duplicate your table, it creates a new "occurrence" of it. This is an important distinction here as you can duplicate the actual data source table on the tables tab and this is not what you should do here.

          Assuming that's what you did here, the relationship looks correct. What kind of field is your "total fees" field? Is this a summary field or a calcualtion field. Either way, you should see just the first record for the current client with status flag = 1. If you are seeing both records, I'd check the values returned by the two status fields.

          How are you displaying the "total fees" field on your clients layout?

          Do you put it directly on the layout or are you putting it in a portal?

          A portal is often a good way to analyze problem relationships to see why they don't work as expected. You can put one on the parent table's layout and include the key fields in the portal row to see what values are present in these fields for every related record displayed in the portal--something that often clears up why you see the records that you do in it.

          • 2. Re: Relationship problem??
            altan

            Thank you PhilModJunk for your prompt reply. I've tried to recreate a new occurrence of "Invoices" using the two green plus button, but the results are the same.

            My "total fees" is a calculation field of "subtotal - discount";

            I don't display the "total fees" field in my client layout. They are displayed on the Invoice Layout. It is part of a summary of the prices of line items that resides in a portal.

            my objective is to only show the total for the invoices and not quotations. but as you see in the attached diagram as compared to the diagram that I'll upload next,.... there is a client Vincent Delia that made two seperate purchase, thus different invoice. The 1st invoice is $400. The 2nd, as it's still a quotation, is $200.

            Although invoices::status shows that the 2nd is a quotation, the occurence table invoice confirmed::status still retain the status of the 1st invoice.... and the total summary shows $800.

            as for the 2nd diagram in the next post, when I change the 1st invoice status to become quotation, the occurence table Invoices Confirmed::status immediately changed to follow the Invoice::Status again. This time round, when i click on 2nd line, Total summary only shows $200 which is the amount made from Sheris.

            And I don't understand why it is behaving this way...

            :( I'm really grateful for your detailed explanation on troubleshooting. However, I'm not as expert as you may think.. Hope you or someone would be able to help me with this.

             

            Thank you very much.

            • 3. Re: Relationship problem??
              altan
              /files/ba57b8e81b/Screen_Shot_2012-01-19_at_4.52.24_PM.png 1280x140
              • 4. Re: Relationship problem??
                philmodjunk

                Let's check a few details first.

                In what table is "total summary" defined? is it a calculation field or a summary field? Please post the details specified for that field's definition in either case.

                I see what I believe are two images of the same portal located on the invoice layout. When you enter layout mode and check the bottom left corner of the portal, do you see "Invoices Confirmed"?

                When you check them in Manage | database | fields, is Clients::ctrl_invoice_Status a calculation field that returns a "number" data type. (Have to open calculation definition and check drop down list. In your latest screen shots, you show a column of 1's titled "ctrl_invoice_status". Is this the same field (is it from Clients)? You also have two Status Flags: Invoices: Status Flag and Invoices Confirmed Status flag. Why the extra field? Is Invoices: status flag a number field?

                What's puzzling me is why we would see any invoice records in this portal unless it had a 1 in the Invoices::Status Flag field like you show in the screen shots.

                Note: None of the following addresses WHY you aren't getting the results you want.

                You could rearrange your table occurrences to look like this:

                Clients----<Invoices-----<confirmed Invoices (Invoices and confirmed invoices must be occurrences of the same data source table)

                Clients::ClientID = Invoices::ClientIDfk

                Invoices::ClientIDfk = Confirmed Invoices::ClientIDFK AND
                Invoices::ctrl_invoice_Status = Confirmed Invoices::Status Flag

                This requires moving the ctrl_invoice_Status field from clients to invoices, but eliminates having the client table "between" the two occurrences of your Invoices table.

                It's also possible to use:

                Invoices::ClientIDfk = Confirmed Invoices::ClientIDFK

                and apply a portal filter (FileMaker 11 only) that filters out all invoices that are not confirmed. This approach requires a different method for computing the totals, but can also be made to work here.

                • 5. Re: Relationship problem??
                  altan

                  Hi PhilModJunk,

                  Thank you for the reply.

                  Kindly see below for my replies please.

                  Qns: In what table is "total summary" defined? is it a calculation field or a summary field? Please post the details specified for that field's definition in either case.
                  Ans: total summary is defined in Summary of Revenue::Total Of Invoices. It is of type calculation with equation "Sum(Invoices::total fees)", unstored.

                  Qns: I see what I believe are two images of the same portal located on the invoice layout. When you enter layout mode and check the bottom left corner of the portal, do you see "Invoices Confirmed"?
                  Ans: The are two images of the same "List" layout of my Layout:List-Of-Confirm-Invoice with reference to Table:Invoices. They are not portals. The only portal I have in my layouts is under my Layout:invoice-Details where I can key in my Line-Items and Incoming-Funds. The fields are from Table:Invoices except for Invoices confirmed::status flag which is from Invoice confirmed, ctrl_invoice_status which is from Clients, and invoices confirmed::status.
                  Should I be using all the fields from Invoices-Confirmed instead?

                  Qns: When you check them in Manage | database | fields, is Clients::ctrl_invoice_Status a calculation field that returns a "number" data type. (Have to open calculation definition and check drop down list. In your latest screen shots, you show a column of 1's titled "ctrl_invoice_status". Is this the same field (is it from Clients)? You also have two Status Flags: Invoices: Status Flag and Invoices Confirmed Status flag. Why the extra field? Is Invoices: status flag a number field?
                  Ans:  Yes, Clients:ctrl_invoice_status is a calculation field that returns Number 1. The equation is ctrl_invoice_status = 1, calculation result is Number.
                  Yes, the column of 1's titled ctrl_invoice_status are the same field taken from Clients.
                  As Invoices-Confirmed is a table occurence of Invoices, it seems that when I add a field (Status Flag) in Invoices, Invoices-Confirmed will also take over the same field... That was what I observed. Yes, Invoices:status flag is a number with calculation status flag=If ( status = "invoice"; 1; 0 ).

                   

                  Qns: What's puzzling me is why we would see any invoice records in this portal unless it had a 1 in the Invoices::Status Flag field like you show in the screen shots.
                  Ans:I think it's because the fields are taken from Invoices and not Invoices Confirmed. I took some of them from Invoices because I still wanna see the entries but I just don't want to have their fees calculated in. It's strange that when I change the total fees field to Invoices Confirmed::total fees, it behaves the same as Invoice Confirmed::Status, which is that it follows the first occurence of invoice tied to the same client. I.e. the $200 under Total Fees under the 2nd invoice of Vincent Delia now changes to $400 which is the same the first invoice of Vincent Delia.

                   

                  Note: None of the following addresses WHY you aren't getting the results you want.

                  >> would it be easier if I mail you the source file?  

                   

                  You could rearrange your table occurrences to look like this:

                  Clients----<Invoices-----<confirmed Invoices (Invoices and confirmed invoices must be occurrences of the same data source table)

                  Clients::ClientID = Invoices::ClientIDfk

                  Invoices::ClientIDfk = Confirmed Invoices::ClientIDFK AND
                  Invoices::ctrl_invoice_Status = Confirmed Invoices::Status Flag

                  This requires moving the ctrl_invoice_Status field from clients to invoices, but eliminates having the client table "between" the two occurrences of your Invoices table.

                  >> I'll try this and get back to you on the result. :)

                   

                  It's also possible to use:

                  Invoices::ClientIDfk = Confirmed Invoices::ClientIDFK

                  and apply a portal filter (FileMaker 11 only) that filters out all invoices that are not confirmed. This approach requires a different method for computing the totals, but can also be made to work here.

                  >> I'm using FM11 but i'm not very sure on how to implement this. :)

                  Thanks for your help so far.

                  • 6. Re: Relationship problem??
                    altan

                    OMG!

                    -------------------------------------------------------------------

                    You could rearrange your table occurrences to look like this:

                    Clients----<Invoices-----<confirmed Invoices (Invoices and confirmed invoices must be occurrences of the same data source table)

                    Clients::ClientID = Invoices::ClientIDfk

                    Invoices::ClientIDfk = Confirmed Invoices::ClientIDFK AND
                    Invoices::ctrl_invoice_Status = Confirmed Invoices::Status Flag

                    This requires moving the ctrl_invoice_Status field from clients to invoices, but eliminates having the client table "between" the two occurrences of your Invoices table.

                    -------------------------------------------------------------------

                    It works!

                    Except that I have to use Invoices::ClientIDfk = Invoices Confirmed::ClientIDFK AND
                    Invoices::Status Flag = Invoices Confirmed::ctrl_invoice_Status for it to work as Invoice Confirmed::Status Flag still follows the 1st occurence of the invoice tied to the same client. 

                    Thank you so much for your help and time. Didn't know I can do it this way. You're Da Best!

                     

                    But do let me know if you can find the bug to the previous challenge. :))

                    • 7. Re: Relationship problem??
                      philmodjunk

                      1)

                      total summary is defined in Summary of Revenue::Total Of Invoices

                      That really complicates this. Note that a record in this table matches by a different pair of fields: "created by". Thus the value in these two fields will control what invoices supply data to the calculation field of any record in this summary table. Given that your portal matches records by clientID and status, this could easily be a completely different set of records than that listed in your portal.

                      2)

                      That really does explain why I am seeing records that are unconfirmed in the list shown. A relationship will control what records appear in a portal, but have no effect on what records appear in the layout's found set--what is listed in your screen shots here. You CAN use the relationship to control what records are shown if you use a Go to Related Records step. Either that or you perform a find to pull up the records you want to view on that layout.

                      • 8. Re: Relationship problem??
                        altan

                        Thank you so much for the explanation. I've definitely learnt something from you here.

                        1) I too realised that I made a mistake by matching the different tables using "created by". When I examine the table, I found multiple entries with all data simliar. Once I changed the matching field, I was able to proceed with my expenses process.

                        2)  Thank you for the insights. I will put a portal on my staff layout so as to view the revenue and the expenses made. That would be a better way isn't it? :))

                         

                        Thank you! :))