I have a single flat file of records imported from a tab-delimited
conversion of some records from an outside system.  Each of the original
records has a unique id but potentially has multiple subject elements. The
records came to me in Excel as a series of rows:

ID    subject
1       a
1       b
1       c
2       b
2       d
etc...

I have a subsummary that lists the unique values used in a subject field
(subject), followed by a count (s_count) of the number of instances for each
of those unique terms.  This works fine.

BUT, what I would also like to do is count how many unique terms exist in
the entire database or in a subset of records.  I created a total field
(s_total) that does a count on subject and put that in a trailing grand
summary, but it gives me the total number of subjects, not a count of the
unique subjects.

How might I do this? Can I do this?

I am afraid that I might need the rethink the import and somehow create two
related tables, but I am not entirely certain how to break up the import and
retain the relationships.

In article <c6br3u$ks6$1@murdoch.acc.Virginia.EDU>, "llj"
<llj4g@virginia.edu> wrote:

> I have a single flat file of records imported from a tab-delimited
> conversion of some records from an outside system.  Each of the original
> records has a unique id but potentially has multiple subject elements. The
> records came to me in Excel as a series of rows:
> 
> ID    subject
> 1       a
> 1       b
> 1       c
> 2       b
> 2       d
> etc...
> 
> I have a subsummary that lists the unique values used in a subject field
> (subject), followed by a count (s_count) of the number of instances for each
> of those unique terms.  This works fine.
> 
> BUT, what I would also like to do is count how many unique terms exist in
> the entire database or in a subset of records.  I created a total field
> (s_total) that does a count on subject and put that in a trailing grand
> summary, but it gives me the total number of subjects, not a count of the
> unique subjects.
> 
> How might I do this? Can I do this?
> 
> I am afraid that I might need the rethink the import and somehow create two
> related tables, but I am not entirely certain how to break up the import and
> retain the relationships.

If 's_count' is already giving you the number of instance for each
unique entry, then an extra field probably isn't necessary. Try simply
putting a copy of the the 's_count' field in the Trailing Grand Summary
part - it should then give you the sub-totals you're already getting
followed at the end by the Trailing Grand Summary with an overall
total.

Note: I'm assuming here that the 's_' part of the field's name is your
method for distinguishing proper Summary fields from other types.



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

Helpful Harry <helpful_harry@nom.de.plume.com> wrote:

> Try simply putting a copy 
> of the the 's_count' field in the Trailing Grand Summary 
> part - it should then give you the sub-totals you're already getting
> followed at the end by the Trailing Grand Summary with an overall
> total.

And how can you access this value from the grand summary in a script? So
far I've only managed to export in preview mode and re-load in a
separate global as per import. Something tells me I must be missing
something.

ard

In article <1gcr00r.qlhwhneyciw5N%ardpuntjonkeratxs4allpuntnl@b.c>,
ardpuntjonkeratxs4allpuntnl@b.c (ard) wrote:

> Helpful Harry <helpful_harry@nom.de.plume.com> wrote:
> 
> > Try simply putting a copy 
> > of the the 's_count' field in the Trailing Grand Summary 
> > part - it should then give you the sub-totals you're already getting
> > followed at the end by the Trailing Grand Summary with an overall
> > total.
> 
> And how can you access this value from the grand summary in a script? So
> far I've only managed to export in preview mode and re-load in a
> separate global as per import. Something tells me I must be missing
> something.
> 
> ard

There's basically two ways. You could:

  1.  set-up some relationship links to give you access to all 
      records in the same s_count grouping, and then use calculation
      fields to "summarise" them,
OR 

  2.  you can use the GetSummary function.


I'll ignore the first option with relationship links since I don't know
if you already have those or not.

The GetSummary function allows you to retrieve the values from Summary
fields just as though you were viewing the layout in Preview Mode or
printed.

    GetSummary(SummaryField, BreakPoint)

The SummaryField is obviously the particular Summary field that you
want to retrieve the value from. The BreakPoint field is the same one
you're using to sort the records into sub-groups that the SummaryField
totals for (ie. the point at which the summarising breaks off is where
the value of BreakPoint changes).

You do already have the s_count field as the summary counter for each
unique grouping. So you can have a new calculation field:

     SummaryCount = GetSummary(s_count, GroupID)

where GroupID is the field you're using to sort the records by. This
will give each record a field containing the same s_count number from
the Summary part for the grouping they're in. (You may or may not
actually want this number for your purposes.)

Then, for the grand total you can have another calculation field:

     GrandTotalCount = GetSummary(s_count, s_count)

Using s_count again as the BreakPoint field means that you'll get a
summarising of all the records in the Found Set, ie. a grand total,
rather than a sub-total for the GroupID grouping the record happens to
be in.

You need to make sure the Storage Option for these fields is set to be
NOT stored, that way the calculations will always be "live" values of
your current Found Set.

These fields are normal calculation fields so can be used in any other
calculation fields or scripts ... BUT, there is a problem with this
method. If you've got LOTS of records (or a slow computer) it can be
slow to calculate these GetSummary functions since it basically has to
sort and summarise records every time you access the field. It's
therefore best to not put the fields on a layout or in calculation
fields / scripts that are used often.



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

ard <ardpuntjonkeratxs4allpuntnl@b.c> wrote:

> Helpful Harry <helpful_harry@nom.de.plume.com> wrote:
> 
> > Try simply putting a copy 
> > of the the 's_count' field in the Trailing Grand Summary 
> > part - it should then give you the sub-totals you're already getting
> > followed at the end by the Trailing Grand Summary with an overall
> > total.
> 
> And how can you access this value from the grand summary in a script? So
> far I've only managed to export in preview mode and re-load in a
> separate global as per import. Something tells me I must be missing
> something.
>

This sort of problem has been covered before. Check dejanews.com for
Filemaker and search on Unique.
 
You can get these totals through a self-relationship but it would
probably be too slow for what you want.

Create a self-join relationship (this file to this file) based on ID.
Create a field with a unique serial number unless you already have a
unique number field of some kind. You can populate as new serial number
field by Replace with a serial number and make it auto-entry serial
number so that new records get their own serial.

Now create an ordinary calc field which is 
serial=Min(myfile::serial)

This calc will show 1 for each time the serial number is the minimum
value for that ID (it's looking across the relationship so it looks at
the serial numbers for each ID separately). This is one 1 for each
unique ID. Now you create a summary field totalling the new calc and you
get the number of unique ID numbers in the found set.

This works but may be too slow if you have a lot of records.

Look at Dejanews for Filemaker and there may be better solutions there.

Maire Black    

I have a database where I wish to let users see only certain records.

This is a Masonic library, and as you may know, Freemasons have degrees. 
Some books are for 1st degree members, others for 1st and 2nd, and so 
on. I wish to let users log in according to their degree and only see 
the books which they can read, not the ones for higher degrees.

There is a field for each book record which gives its degree in numeric 
form. So when someone logs in as e.g. a 2nd degree member, I want him to 
see books for degrees <3.

How do I manage this in version 7?

Oke