5 Replies Latest reply on Sep 2, 2011 3:02 PM by petek157

    Invoice total field on job table not working

    petek157

      Title

      Invoice total field on job table not working

      Post

      I have been racking my brain trying to figure out whats going on.  I have done this a hundred times and it always works.  I just cant seem to see what I am missing and I have been at it for two solid days.

      I have a Customer table, a job table, a materials invoice table, and a materials line item table.  I have all of the relationships set like I would any other time, What I am trying to do is take the total of all of a jobs material invoices and have a field on the job page that shows it.

      I have a invoice total in the materials table that takes the sum of all of the line items in a portal on the Invoice layout.  That works perfect.  I have a field in the job table set to a calculation of the sum of the invoice totals.  But that field on the job layout doesnt show anything.

      I have been going back and forth from another database that I have that does this exact same thing and it works perfect. So I am comparing the two and they are exactly the same.

      Any suggestion on what I could be missing?

      Need more info just ask.

      Thanks

      Pete

        • 1. Re: Invoice total field on job table not working
          philmodjunk

          What's the return type for the Invoice total calculation? This is controlled with a drop down inside the specify calculation dialog.

          Note: if you define a "total of" summary field in LineItems, you should also be able to place this field on your jobs table to get a total over all the invoices in one step instead of two.

          I'm assuming these relationships:

          Jobs----<Invoices-----<LineItems

          • 2. Re: Invoice total field on job table not working
            petek157

            The return types are all numbers.

            I will try the summary field way and see what happens. 

            You assume correctly on the relationship.

            Frustrating.....!

            • 3. Re: Invoice total field on job table not working
              petek157

              The summary field for the lineitems gives me a summary of the current invoice.  I am looking for the sum of all of the invoices for a particular job....So I tried a summary of the invoice totals in the invoice table.  that gives me the number that I am looking for, and worked in the invoice layout, but NOT in the jobs layout.

              I am guessing that means that there is a relationship issue but for my life I dont see it.

               >>EDIT

              I also see that the summary field doesnt distunguish between jobs.  It just totals all of the invoices.  I am just noteing everything that I find in hopes that someone can piece this together.

              I never get stuck on advanced topics, its always the simple stuff.

              Peter

              • 4. Re: Invoice total field on job table not working
                philmodjunk

                I thinkwe need to look at your relationships.

                If you have relationships like these:

                Jobs::JobID = Invoices::JobID
                Invoices::InvoiceID = LineItems::InvoiceID

                then

                Sum ( LineItems::field )

                Defined in Jobs should total the value in field for all line items for all invoices linked to the current job record.

                Likewise, defining a summary field in LineItems and then placing this field on the Jobs layout should compute the same total.

                Note: if you have multiple occurrences of LineItems and/or INvoices in Manage | database | Relationships, it's important to select the correct table occurrence when specifying the field in the sum calculation and when selecting the summary field from LineItems to add it to the Jobs layout.

                • 5. Re: Invoice total field on job table not working
                  petek157

                  AAAAAHHHHHHH.........

                  I got it.

                  In my infinate wisdom, when I setup the popup to select the job to assign the invoice to I used a value list that didnt include...wait for it...the job ID!

                  Turns out thats important.

                  Like I said, its always stupid simple.

                  Thanks anyways Phil.

                  Pete