I have produced a checkbook register with separate categories for different types of expenditures.

I would like to be able to calculate the total expenditures per vendor name.

If my checkbook register lists the following checks:

Acme Woodworking : $15

Ajax Plumbing : $10

Ajax Plumbing : $12

Bob's Delicatessen : $10

Bob's Delicatessen : $16

I would like my final report to total the purchases as:

Acme Woodworking : $15

Ajax Plumbing : $ 22

Bob's Delicatessen : $ 26

I will write a check to some of these vendors several times a year and some of these vendors just once.

Is there a way for FM8 to search the register then produce a report that finds each unique vendor name and totals how many dollars were spent with this particular vendor?

I'd be happy to do this with a script or a plugin if such exists.

Any suggestions?

Thanks,

Jarvis

Hi there Jarvis!

Create a summary field sAmount (summary) total of Amount (All together). Then you need a columnar/list report with grouped data sub-totals. Fields to place will be Vendor Name (or better VendorID to be sure it's unique) and your Amount field. Organize report by Vendor. Sort by Vendor (critical).

Specify sAmount summary field (Category to summarize is Vendor) and ADD SUBTOTAL. Next few options are pretty easy then let FM create the script (because it will store the sort order which is critical to store in your script).

Now ... if you don't need the detail but only want the totals, delete the body (which will also delete the body fields) and you will have a report in Preview Mode with just the Vendor totals based upon your found set.

LaRetta

LaRetta,

You have made this so easy to understand.

I've been asking this same question in a round about way for about a month and now and finally I can harvest the data.

Thank you very much!

Since I am on a roll, I thought I would push my luck and ask one more question. Can this report be made to show up in browse mode? I would like to have the information available on a report that is also a little more interactive by the user. Any ideas?

Jarvis

Your luck just ran out, I'm afraid ... sub-summaries can't be scrolled in Browse.

The only methods I've used is restricted relatiionships and placing the summary fields directly on the layout (because Management wanted columnar reports). That works really well but is task-intensive to structure AND it can get slow with large volumes of data. I have also used writing these fields to tabbed text field which produces columnar results.

Usually, I use Save/Send As Excel to provide reports to Management because they want that format for sending the data elsewhere. You could even generate your report and export then import into a static table. But truly, Comment, Fenton and many others are better at these types of reporting issues. I just haven't needed them because I use relationships and portal-type functionality for almost everything ... which is why I have 40 LineItems TOs.

I am confident others will provide elegant methods for you ... if not, I will search Forums and find some of my favorites. I have also saved these favorites in my folders but I have over 3,000 favorites I'd have to wade through.

LaRetta

LaRetta,

I kind of had a hunch that would be the answer.

Thank you so much for your help.

Jarvis