Jump to content
Salesforce and other SMB Solutions are coming soon. ×

How would you approach this layout?


Recommended Posts

I am new to Filemaker Pro, but have a number of years experience with spreadsheets, macros, and programming. I am trying to do something that is likely very easy for someone experienced with databases. This is my first database project which is a personal expense tracking system.


The underlying table structure is the basic record from a Transactions table with sub-records from an Items table in a portal. The match field is a unique TransID field in the Transactions table.


Transactions table






TotalAmount (Sum of Items::Amount)


Items table








Data entry works just fine, but what I am stuck with is how to create a Layout/Report by Category AND Month like the in following table [in Excel I can use Pivot Tables or SUMIF()]:


Category | JAN | FEB | MAR | APR | ... | TOTAL |











I have referred to several books, none of which touch on this type of layout. Perhaps the answer is that I am stuck in a spreadsheet mentality, and DBs are much more flexible to create summaries on the fly. However, I like a concise table which is one page and simple for the spouse and I to refer to without the need to know how to "drive" the database.


Also, I'm sure there are a multitude of methods to accomplish this, each with various efficiencies. I would like to hear your ideas to get me started down the right path.


Thanks in advance.

Link to comment
Share on other sites

  • 3 months later...

You will also need to add year to your thinking unless of course you want a running total of the months including all years.


There are several approaches and this is just one of them.


I would add a calculated field for each month and the calculation would be:


If month(date) = 1, amount, 0 ) /January

If month(date) = 2, amount, 0 ) /February


if month(date) = 12, amount, 0) /December


Thus the amount would show up in the appropriate month calc field and you can do a report by category and year and the sums would show up.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use