Looking for some help please�

FileMaker6 on Mac.

I have a database of invoices from many years.  What I would like to
create is a layout in which I could enter in a customers account
number and it would return sales for specific date ranges.

So I could see the amount they purchased in different years to see if
their sales we up or down, etc�

I was thinking of using several global fields for specific date ranges
but I can't figure out how to base a calculation on a date range.

So I guess my question is, how would you write a formula to return the
sum of a field within a specific date range?  If I could figure this
out then I could create different fields for each year.

Hope this makes sense; any suggestions would be greatly appreciated.


Thank you!

zero1884@yahoo.com wrote:
> Looking for some help please�
> 
> FileMaker6 on Mac.
> 
> I have a database of invoices from many years.  What I would like to
> create is a layout in which I could enter in a customers account
> number and it would return sales for specific date ranges.
> 
> So I could see the amount they purchased in different years to see if
> their sales we up or down, etc�
> 
> I was thinking of using several global fields for specific date ranges
> but I can't figure out how to base a calculation on a date range.
> 
> So I guess my question is, how would you write a formula to return the
> sum of a field within a specific date range?  If I could figure this
> out then I could create different fields for each year.
> 
> Hope this makes sense; any suggestions would be greatly appreciated.
> 
> 
> Thank you!


This sort of thing is so much easier in FM7. I would do this in FM6:


create two globals in Invoices, for StartDate and EndDate: g_StartDate, 
g_EndDate
create a calc, c_FlagDate (number result,)  in Invoices to flag the 
Invoice date:   If(g_StartDate <= InvoiceDate and InvoiceDate <= 
g_EndDate; 1; "")
which should return 1, if InvDate lies between, or is on either of,  the 
two.

As the calc can't be indexed, create  a FlagDate number field, indexed 
and have script this  (or lookup...) to the c_flagDate value, at each 
calc  a year request

Create a _constant calc number field, value 1
create a rel, rel_Inv    between _constant and FlagDate   (note, not 
c_FlagDate which can't index)
then create a Sum( rel_Inv::InvoiceAmount)  calc field using the rel.

Create a script to run ater each date gloabl cahnge and loop through, or 
replace, the FlagDate values.

In FM7, a multiparameter rel from globals to Invoice date would be used.


Chris Brown
Neurosurgery
University of Adelaide

<zero1884@yahoo.com> a �crit dans le message de news: 
33b12de.0411041340.4ff5e10f@posting.google.com...
> Looking for some help please.
>
> FileMaker6 on Mac.
>
> I have a database of invoices from many years.  What I would like to
> create is a layout in which I could enter in a customers account
> number and it would return sales for specific date ranges.
>
> So I could see the amount they purchased in different years to see if
> their sales we up or down, etc.
>
> I was thinking of using several global fields for specific date ranges
> but I can't figure out how to base a calculation on a date range.
>
> So I guess my question is, how would you write a formula to return the
> sum of a field within a specific date range?  If I could figure this
> out then I could create different fields for each year.
>
> Hope this makes sense; any suggestions would be greatly appreciated.
>

> Thank you!

Hello,

I think that this is now much easier in Filemaker 7 (because relationships 
are more flexible), but for prior versions, relationships based on date 
ranges are rather complicated to set up. Thankfully, Mikhail Edoshin has 
created a nice technique - Smart Ranges - and provides very detailed 
explanations and exemples.

http://www.onegasoft.com/tools/smartranges/index.shtml

If you only want calculations based on whole years however, this is 
overkill. I would just do as you said. i.e. create a few globals (left side 
of the realtioships) and a new field to use on the right side of the 
relationships Year_of_sale (calc, stored).
Depending on how many globals you create, you can then have 2, 3 or more 
filtered portals (if you want to see the individual invoices informations), 
or just the totals -> sum (one relationship:: invoice_total).

HTH
Marc-Andr� Paiement

Method #1=20
Do a script in which you 'find' the proper customer sales in the =
specified date ranges, then go to a layout where a summary field shows =
the total.=20
Method #2=20
Create a "token1" field equal to :=20
CustomerNumber & " / " & (case( date <=3D global_date_max and date >=3D =
global_date_min; "X"; ""))
then create a "token2" field equal to :=20
CustomerNumber & " / " & "X"
and make a relationships between 'token1' and 'token2' in the same file.
Then get the 'Sum' of the realted amount to get the good total.
Remi-Noel Menegaux
(if needed continue off line with me with an example)


<zero1884@yahoo.com> a =E9crit dans le message de news:=20
> Looking for some help please.
>=20
> FileMaker6 on Mac.
>=20
> I have a database of invoices from many years.  What I would like to
> create is a layout in which I could enter in a customers account
> number and it would return sales for specific date ranges.
>=20
> So I could see the amount they purchased in different years to see if
> their sales we up or down, etc.
>=20
> I was thinking of using several global fields for specific date ranges
> but I can't figure out how to base a calculation on a date range.
>=20
> So I guess my question is, how would you write a formula to return the
> sum of a field within a specific date range?  If I could figure this
> out then I could create different fields for each year.
>=20
> Hope this makes sense; any suggestions would be greatly appreciated.
>=20
>=20
> Thank you!

On 4 Nov 2004 13:40:26 -0800, zero1884@yahoo.com wrote:
>  Looking for some help please�
> 
>  FileMaker6 on Mac.
> 
>  I have a database of invoices from many years.  What I would like to
>  create is a layout in which I could enter in a customers account
>  number and it would return sales for specific date ranges.

What do you mean by specific ranges?

You could compute e.g. sales in steps of 365 days (that is per year), in
10 years or in 10 days.

If you want to use fixed intervals, it's pretty easy: use some dummy
records for  e.g. January, Feb, Mar, Apr, .....

Then do a sort by date and summarize by customer and interval.

Be aware that a 10 days interval is useless for a customer who orders
every three months only - the summary is zero most of the times.

>  So I could see the amount they purchased in different years to see if
>  their sales we up or down, etc�

So if your interval is one year, then all you need is a calculation that
computes year(order date), then sort by year and summarize.

>  I was thinking of using several global fields for specific date ranges
>  but I can't figure out how to base a calculation on a date range.

First of all, dates are internally just numbers: every next day
increments by one. 

Thus you may compute date ranges easily, while it may be tricky e.g. to
trigger on the last day of a month (e.g. 2004-01-31, 2004-02-28, ...):
Here a nice workaround is to use (date(year, month, 1) - 1) in order to
obtain the day before the first day of the next month - but those
intervals would have different length, while e.g. January might contain
either 20 working days (monday-friday) or up to 23.

Be aware of such problems if you want to compare e.g. 10.0 % vs 9.85 %
- while the days effect itself would create 10 � 1.5 % itself.

The other thing is that you can't compute ranges directly in FMP. If you
want to compare a very specific range of date, you should do this via a
search operation to find [first date & "..." & end date] in order to
compare two companies within this range.