The problem I am finding is that I only have one table for the values - I mean the field amount is on the same table for revenue and expense. I sort everything with at the beginning when I set the field for revenue or expenses transaction
Sorry, but that does not make sense when I read it. Do you have one FIELD for revenue and one FIELD for Expense?
If so, a simple summary report that groups your records by month can be set up with summary fields to show the montlhly sub totals and running balance.
If you use the same field for expenses and revenue, it's still possible, but we'll need to add some calculation fields to separate out the expenses and revenue entries.
No I have one field called amount...which is used to enter the values for expenses and revenue.
They way I report it is by sorting the "transaction field" in expenses or revenues.
Hope it makes sense now.
I read that to mean that a number field, amount, records the dollar figure and a text field, transaction, identifies whether it is "revenue" or "expense". I will also assume that both revenue and expense amounts are entered as positive numbers.
Set up these calculation fields:
cMonth: YourDateField - Day ( YourDateField ) + 1 //select Date as the result type
cExpense: If ( Transaction = "Expense" ; amount )
cRevenue: If ( Transaction = "Revenue" ; amount )
cBal: cRevenue - cExpense
Set up these summary fields:
sTotalExpense: Total of cExpense
sTotalRevenue: Total of cRevenue
sBalance: Total of cBal
Now set up a list view layout and remove the body layout part. Replace it with a sub summary layout part "when sorted by cMonth".
put cMonth and the three summary fields inside this sub summary layout part. cMonth can be formatted in the Inspector to only show the name of the month.
Now perform a find for the records you want on your report and sort them by cMonth to sort them in proper order and grouped by month. Please note that your layout will appear blank if you do not sort your records by cMonth.
Genious. thanks for your help. Really appreciated!!! You guys Rock!!!
It works like a charm!!
I have another little issue. I need to run a monthly report where it only shows the totals for the previous months. I have looked in the forum, but could not find something that would work for me.
I would like to be able to select the company I am running the report for (I have one Accounting System for 4 companies) and then select the month I would like the report to be run (this would allow the user to re-print or re-save older reports).
At the moment, with the script I have, when I run the report, I get all months which have data (I.e: April and May - are sample data).
Thanks for any help
You just need to perform a find for the records that you want in your report, then sort them by month (so that the sub summary layout part is visible).
One way to do that is to have the user enter or select data in some fields with global storage while in browse mode and then run a script that pulls up your report.
See this thread for examples of scripted finds that you may adapt to your purpose: Scripted Find Examples
Thanks for all your help up to here. Now today I was trying to add a grand total to calculate the year amount, but could not add a summary calculation to a already summary field (ie: sTotalBalance = Total of sBalance). How can I do that?
Thanks once again
Just use the same summary field. If you put the field in a header, footer or grand summary layout part the same field will show the total for all the records in your found set. If you have data from multiple years, add a sub summary layout part "when sorted by" a year field and put the summary field into it.
To get a year field, add a calculation field that uses the Year function to extract the year from your date field.
Hello Filemaker Gurus,
On the same subject of the date, I have entered data on the db, however, I have some data from 2012. I would like to create a report where the user can select the year for the report. At the moment when I run
The script suggested to me in older posts, I am getting data from 2012 and 2013. I want the user to be able to make a selection for which year to have the report previewed.
Let's say that you enter or select 2013 in a global field named gYearSelected
Enter Find Mode 
Set field [YourTable::YourDateField ; YourTable::gYearSelected ]
Set Error Capture [on]
Perform Find 
will find all records dated with a 2013 date, provided that YourDateField is of type date and not type text.
The way I am doing is:
From a layout, the user clicks a button, which will take to another layout where he is asked to enter which year he wants to run the report for.
Then there is where I get stuck.
I have the calculations suggested by Phil above and I am getting the reports correctly:
apr 4000 6000 -2000
The problem is that December/2012 is also showing. I want the user to be able to run the report for a particular year. As our db is just starting (always have been doing this in excel), is not a major issue as I only get december from 2012, but I want to transfer more past data to this db and i would be great if the user could select the year for the report.
Thanks for all your help
The script that I posted should find only records of a specified year.
Sorry, I wrote my post above without seeing that you actually had already posted. But I am still having some issues, and maybe I did not explain myself properly.
I want to run a report where I get a monthly total showing for each month (you already helped me with that on earlier posts on this same thread). you suggested:
But it did not work. What Am I doing wrong?
Once again Thanks for your help