Bi-Monthly Report (Calculation)

[AeroGuy]

hey folks,

so this is my first time having to use a calculation-field... maybe someone can help me out or point me to a post that has already answered this.

I have a database that holds records/invoices from a bunch of companies. each of these records has a field called Amount which is the sum/total billed to that company for that particular invoice.

what i'd like to do is create a report (Report1) that will basically take the Amount-field from each record in this database and add them together. however, i'd like filemaker to add up only the fields that are found in the invoices that have been billed for the first half of the month (from the 1st to the 15th). i would then like to make a similar report (Report2) that adds up all the amounts from the invoices created/billed from the 16th to the end of the month....

how would i go about doing this? the report should take into account the invoices from ONLY the month i'm currently in. thus, Report1 should only add up the amounts for the invoices from the 1st to the 15th of AUGUST only... not July, June, and so forth. Likewise, Report2 should sum up all the amounts for invoices from the 16th of August to the end of August. And then there's the issue of how do i tell filemaker how many days there are in a particular month for Report2 (is that even much of an issue?).

any help or advice anyone can provide would be appreciated.

peace,

AeroGuy

[WHW]

You can set up a script to perform a find or do a manual find (cntrl-F or command-F depending which platform you are using). In the find mode, the <Date> field, you can manually insert the date range desired (i.e. 8/01/04...8/15/03). This will return all the invoices on and between those two dates. You should have a summary field of the Invoice Total-called Sum_Report1, that way, when you do a find with a date range of 15 days, you will then have the Sum of all invoice $$$ for that particular date range. You would approach collecting the Report2 information the same way.

I'm not sure how you want to present this information, but I am assuming you want it recorded. I would use the SetField function to place that information from the Sumfield into the field you so choose, such as: Aug_Inv_Rpt1 and Aug_Inv_Rpt2 respectively. You'll have to do this for each month, but it can be automated with a little work.