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:
The return types are all numbers.
I will try the summary field way and see what happens.
You assume correctly on the relationship.
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.
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.
I thinkwe need to look at your relationships.
If you have relationships like these:
Jobs::JobID = Invoices::JobID
Invoices::InvoiceID = LineItems::InvoiceID
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.
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.