HI,

I'm using FM7 and I have not written scripts/used calculations for a
while so I am quite rusty. I use the database to maintain records
(6000+ entries so far).

Two of the fields I use are the "county field" that the entry is from
and the "date" it was entered onto the database.

So, I want to be able to do the following: a) count the number of
entries in the UK (for example) and also be able to count the number of
entries for the UK in the year 2003.

e.g 2003 - UK - 100 entries
      2004 - UK - 150 entries

and so on and so on

And repeat the same for the US, Australia, Belgium and all the other
countries I have listed in the country field.

Is this possible and can anyone tell me how to do this?

Regards


Djuro

I sent you personally a small file doing what you asked for (I hope).
Others may get it also (privately).
Remi-Noel


----- Original Message ----- 
From: <djurornic@yahoo.co.uk>
Newsgroups: comp.databases.filemaker
Sent: Wednesday, September 14, 2005 1:09 PM
Subject: SIMPLE TOTAL CALCULATION


> HI,
>
> I'm using FM7 and I have not written scripts/used calculations for a
> while so I am quite rusty. I use the database to maintain records
> (6000+ entries so far).
>
> Two of the fields I use are the "county field" that the entry is from
> and the "date" it was entered onto the database.
>
> So, I want to be able to do the following: a) count the number of
> entries in the UK (for example) and also be able to count the number 
> of
> entries for the UK in the year 2003.
>
> e.g 2003 - UK - 100 entries
>      2004 - UK - 150 entries
>
> and so on and so on
>
> And repeat the same for the US, Australia, Belgium and all the other
> countries I have listed in the country field.
>
> Is this possible and can anyone tell me how to do this?
>
> Regards
>
>
> Djuro
> 

In article <1126696164.981628.260690@g47g2000cwa.googlegroups.com>,
djurornic@yahoo.co.uk wrote:

> HI,
> 
> I'm using FM7 and I have not written scripts/used calculations for a
> while so I am quite rusty. I use the database to maintain records
> (6000+ entries so far).
> 
> Two of the fields I use are the "county field" that the entry is from
> and the "date" it was entered onto the database.
> 
> So, I want to be able to do the following: a) count the number of
> entries in the UK (for example) and also be able to count the number of
> entries for the UK in the year 2003.
> 
> e.g 2003 - UK - 100 entries
>       2004 - UK - 150 entries
> 
> and so on and so on
> 
> And repeat the same for the US, Australia, Belgium and all the other
> countries I have listed in the country field.
> 
> Is this possible and can anyone tell me how to do this?

That depends on what you want the "counts" for.

If you simply want to know how many for a printed report, then a
Summary field is what you want. Create a Summary field and a layout,
and then it's a simple matter of finding, sorting appropriately and
printing.

If you want the number to be displayed on-screen and continually
update, then it becomes more complicated and requires either a
Relationship or Value List approach.



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

Remi,

Have not received a file from you as of yet.........

thanks 

Djuro

Harry ,

Thanks for the reply. Unfortunately, my request is for the latter of
your two options as I would require this field to be constantly updated
so I can keep tabs on how the database is developing

I look forward to your reply

Regards

Djuro

Sorry. I re-do it privately.
Remi-Noel


<djurornic@yahoo.co.uk> a �crit dans le message de news: 
1126780262.563200.20290@g14g2000cwa.googlegroups.com...
> Remi,
>
> Have not received a file from you as of yet.........
>
> thanks
>
> Djuro
> 

In article <1126780357.617158.29540@z14g2000cwz.googlegroups.com>,
djurornic@yahoo.co.uk wrote:

> Thanks for the reply. Unfortunately, my request is for the latter of
> your two options as I would require this field to be constantly updated
> so I can keep tabs on how the database is developing
> 
> I look forward to your reply

OK, for on-screen counts that update continually (excluding the record
currently being entered of course).

The first one is the easiest. To count the TOTAL number of records for
each country you need to create a new Relationship to group together
all records from the same country:

    SameCountry     {Relationship}
        CountryField   --->   SameTable::CountryField

and a new Calculation field to count the number of related records
(including the current one):

    CountryCount    {Calculation, Number Result, Unstored}
        = Count(SameCountry::CountryField)

Make sure it is Unstored and place this field on a Layout (in the Body
part) and it will show you the number of records that have the same
Country Field data as the current record.


The second one is trickier. To count the number of records for a
country for each year, you can use a similar approach to above. First
create a new Text field that uses an auto-enter calculation to combine
the country and year into one field:

    YearCountry     {Text, Auto-enter Calculation}
        = If (IsEmpty(DateField) or IsEmpty(CountryField),
                 "", 
                 Year(DateField) & "-" & CountryField)

This will stay empty if either source field is empty, otherwise it will
obtain the data "Year - Country". A true Calculation field can't be
used because Calculation fields can't be used on the right-hand side of
a Relationship, which is where we need it.

Now you can create another new Relationship to group together all
records from the same country AND same date using this new field:

    SameYearCountry    {Relationship}
        YearCountry   --->   SameTable::YearCountry

and a new Calculation field to count the number of related records
(including the current one):

    YearCountryCount    {Calculation, Number Result, Unstored}
        = Count(SameYearCountry::CountryField)

Again, make sure it is unstored and place this field on a Layout (in
the Body part) and it will show you the number of records that have the
same Country Field data AND same year as the current record.

BUT ...
There is a problem here. In older versions of FileMaker (before version
7) the YearCountry field will not update if you later change the date
or country data - this is because the auto-enter calculation is only
ever calculated when the record is created (or in this case when both
DateField and CountryField are not empty). This can be a problem if you
later have to go back and change a data entry mistake.

The way around this is usually to either manually correct the
YearCountry field's data or CAREFULLY use the Replace by Calculation
function to do it automatically (for the entire Found Set of records)
using the same Year(DateField) & "-" & CountryField calculation. This
can be done manually or via a script activated by a button.
Unfortunately it can't be done automatically when you change either
field since FileMaker hasn't included the ability to run a script when
exiting a field. (There are plug-ins that add this function, but
they're usually expensive though).

In FileMaker 7 or 8 you can probably make use of the new ability to
have the auto-enter calculation re-calculate itself when the source
data changes, but I'm not certain how that works.



Within the Count functions I've used the field Relation::CountryField.
This shouldn't be a problem in your case since by definition
CountryField must always contain data, but Count will only count
records that actually have data in the specified field. This would mean
any records where CountryField is empty would not be counted.






Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)