newbie Q: layout for budget tracking DB with category summations

[millerj]

Hey Everyone. Thanks in advance for whatever pointer people can share. I've been reading posts, books, and FMP9 help for a while and I can't break through on this challenge. Here's what I'm doing...

 

I am moving budget tracking for a federal grant from a spreadsheet to FMP9. For this move to be successful, the database needs killer reports. The first I need to make replicates the spreadsheet's major report: a tabular display of expenditures and encumbrances in each of the dozen budget categories. It would also be good to have a budgeted amount and an 'available' amount, each of which should just be simple calculations that I haven't thought about yet. It's the 'current total expenditures and encumbrances' that I can't get to work.

 

FWIW, here's what the table would look like. There would be one for the entire grant period (multiple years) as well as one for each of the years of the grant period.

 

Categories Spent Encumbered Budgeted Available

Cat A 10,289.34 2,500.00 15,000.00 W

Cat B 8,330.50 0.00 0.00 X

Cat C 20,221.20 0.00 20,000.00 Y

Cat D 900.21 10,000.00 20,000.00 Z

 

The database contains invoice entities that include expenditures or encumbrances as attributes. Each entity also has a transaction date.

 

I guess I'm having trouble making the transition from Excel (where I had one worksheet for each category) to FMP9 (where category is an attribute). Add to this my glancing familiarity with SQL which makes me want to define a conditional summation calculation ('calculate sum of expenditures WHERE category = Cat A' for example).

 

Can I create a layout like this with summary field, or do I use calculation fields? And is the a job for scripting (e.g., to cull the appropriate found set before calculating)?

 

I appreciate any and all help people can offer.

 

Jason