1 2 Previous Next 25 Replies Latest reply on Feb 8, 2012 11:52 AM by jball

# total invoice amt to date

I have my invoices totalling their respective amounts to date.........but I really want it to calculate only by invoices with the same Job #. Example, 3 invoices at \$50 each to job 233, then 4 invoices at \$100 to job 234......i want total invoice amount to date to be \$150 for job 233 and \$400 for 234, not a total of \$550 invoiced to date. where am i going wrong??

• ###### 1. Re: total invoice amt to date

You need to add a sub summary part based on job number to your layout with the invoices total summary field in it, and sort the records by job number

That will give you a subtotal by job

Karen

• ###### 2. Re: total invoice amt to date

Oops, I may have been too quick.  How are you getting the total currently?  I assumed you were using a summary field, but maybe you are using a calculation field with a relationship of some kind.  If the latter, then you will need to add the job number to that relationship.

Karen

• ###### 3. Re: total invoice amt to date

Each invoice is a new record, are you talking about adding the sub summary to the invoice  report?

• ###### 4. Re: total invoice amt to date

Yes. Create a summary field, total of invoice totals.  That will give you a total of the found set.  If you place it inside a subsummary part based on the job number, then it will give you subtotals by job number.

Karen

• ###### 5. Re: total invoice amt to date

My field is set up as a summary (sum=total amt of invoice)  I can’t figure out how to get the job # to fit in

• ###### 6. Re: total invoice amt to date

Can I do this in a report layout?

• ###### 7. Re: total invoice amt to date

Yes, exactly.  The report wizard will walk you through it - here's a sample layout using the customer ID instead of the job ID, but it's the same concept:

sorting by customer ID - the report comes out something like this:

• ###### 8. Re: total invoice amt to date

I'm trying to do the same thing but I'm probably a little more green than most users here. Is there any way you could be a little more "step-by-step'ish" on how to do this. I get to the create summary field and the only option that my fields are not greyed out is the Count Of option. The Total Of option does not allow me to select any fields. Any suggestions? Sorry for thread jacking.

• ###### 9. Re: total invoice amt to date

webink wrote:

The Total Of option does not allow me to select any fields.

The Total of option will let you select fields of type Number, Date, Time and Timestamp - or calculation fields that return results of those types.

• ###### 10. Re: total invoice amt to date

Ahh, my field type was text. Lets see if I can do it now. Thanks!

--Worked perfectly! Thank you for the direction.

• ###### 11. Re: total invoice amt to date

I followed step by step, and my records are not showing up properly (see attached).  Did I miss something?

• ###### 12. Re: total invoice amt to date

Are these records sorted?  If you have the summary total in a subsummary part, then the records must be sorted by the subsummary part in order to display the subtotal.

And the subsummary part must be based on the field you are sorting by.  That is, if you are trying to get a subtotal by job number, then the subsummary part must be based on the job number AND the records must be sorted by job number in order to see the subtotal.

Also - I did not ask before, but if you are using an earlier version than FileMaker 11, you will need to also view the report in Previeew mode to see the subtotal.

If you can't get it to work, post a clone of your database (no records) and I will take a look at the layout.

Karen

• ###### 13. Re: total invoice amt to date

This is my “copy” that can be messed with.

• ###### 14. Re: total invoice amt to date

Okay - I got an error message when I first went to the layout that it was looking for the Orders database.  But other than that - all I had to do was sort the records by job number and THEN CHOOSE TO DISPLAY THEM AS A LIST.  You were in FORM view - which only displays one record at a time.

Here is the "fixed" file

Try it and see.

1 2 Previous Next