Report on Invoices in a Range of Dates

[siou]

Hello and thank you for your help! I am at a block with this, and could really use the help!

So..

I am creating a Reports database in filemaker 6.

I have created the first report in the "Reports" database

which reports on invoices created in a file called "Invoices".

These invoices are created in the file "Invoices"

but use a portal to record the information ordered in each invoice

that is stored in the file "Reports".

So, The problem is this.

I have made a relationship for this first Daily report ,

which relates "Reports" and "Invoices",

this relationship equals the orderdate (in "Invoices") to a field called datecalc (in "Reports")

(datecalc=orderdate)

In the reports file, I have made fields called, "Sum by day"...etc, that use this calculation to create end of day reports. This works very well, and has enabled me to create very comprehensive day reports that show all of the details of each invoice along with the sums of the invoices found.

So, I need a calculation that will look at my choice of range of dates and report in the exact fashion that my Daily Report does. I would like to make this choice of dates in the reports database, and then, have this choice of dates generate a weekly report or monthly report.

I hope that this has been clear. Please email me if there are any more questions, and thank you again for your time. Best, ami

[Oldfogey]

The trick is to identify the records in your date range.

Set up a calc field = (orderdate > BeginDate and OrdrDate < EndDate)

Link to that via Global = 1 and use your Sum functions.

[siou]

Dear Paul,

THank you so much for your advice...its sounds just like what I was hoping would work,

so let me just ask you some little questions so I program it correctly.

I will again make a relationship between the Invoices and the Reports.

This relationship will equal the order date in Invoices to a new calc field you described...

(orderdate > BeginDate and OrdrDate < EndDate)

First, I am not so so sure that I understand this calculation..or should I just copy it in there?

because in Reports I just have one field that is called orderdate, and it is a lookup from the orderdate info in the Invoices, so do i have to make another field called OrdrDate for this calculation?

I understand that i will now make new fields called BeginDate and EndDate, this is perfect, I just dont understand how this calculation works.

Ok, onto my final ?

"Link to that via Global = 1 and use your Sum functions."

Link this calculation to another field called Global and make this equal 1?? I am not so sure I understand this.

I think I follow..."use your sum functions" because then I will use this relationship that was created to bring the information about each invoice over to the reports system, and summarize all:..

ok, thats all! Thanks again in advance! Best, Ami

[Oldfogey]

For (orderdate > BeginDate and OrdrDate < EndDate)

read (OrderDate > BeginDate) and (OrderDate < EndDate)

will return 1 when the order date is between the begin date and the end date. Do the extra brackets clear that up? They are not necessary.

Sorry about the typo.

BeginDate and EndDate are Global fields used to enter the relevant dates.

Call the field with the above calculation, say, InRange.

Define One as Global and initialise it to 1 in Reports.

Define a relationship ReportIt using One-InRange.

Sum(ReportIt::InvoiceAmount) will give you the total of all invoice amounts in the date range. You might have to do some playing in Invoices to get the amounts correct.

Hope that's a bit clearer. Let me know if not. (I prefer brevity to clarity. Sorry.)