constrain a found set - omit duplicates

[rbaintern]

I've looked through here and other places, but I'm still finding it hard to do the following:

I have table with records of performing artists and their associated concerts. The fields I have are ArtistID, ConcertSeason, Artist Full Name, (and recently added Mark, Global --trying to follow instructions from a Filemaker Tutorial)

Some artists perform in more than one concert in one season, but I need just a count of the number of artists who performed in a certain season-- without a repetition of their names.

I've tried a few scripts, but doesn't seem to work well...

Any suggestions would be greatly appreciated.

I've also got another step to do after and perhaps I shall outline my next question here.

I eventually need to count from this set of artists who performed this season, I need a count of how many have performed in this concert series for the first time. So basically--those who do not have records with another ConcertSeason value.

I hope I have been clear.

Thank you again

[comment]

I need just a count of the number of artists who performed in a certain season

One way to do this would be to count the related records in the Artists table. Alternatively, you could sort the records by season and by artist, then count the subgroups as explained here:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__view__findpost__p__289204

I need a count of how many have performed in this concert series for the first time. So basically--those who do not have records with another ConcertSeason value.

I am afraid I didn't get this part.

[eos]

This could be done without finds and (almost) without scripting if you use another data model. See if the attached file might help you.

In addition to an Artists table, it has a Seasons table and a Concerts join table for individual concerts.

A triggered script checks every new concert entry for an artist and sets the year of the earliest concert, i.e. the “newcomer season”, for that artist.

Two simple relationships from Seasons let you see at a glance who performed in this season overall, and who performed for the first time.

ArtistsSeason.fp7.zip

[LaRetta]

I have table with records of performing artists and their associated concerts. The fields I have are ArtistID, ConcertSeason, Artist Full Name

I am afraid I can't get past the first few sentences and particularly the part I highlighted in blue. Does not an Artist ever perform in more than one Concert/Season? If you have ArtistID and Artist FullName then that should be one table called Artists. Then you would have another table called Concerts which holds the concert information (and then probably a join table between them).

Edited May 22, 2012 by LaRetta

[rbaintern]

Thank you all for your feedback.

I've tried doing the way comment suggested. I think I have done it correctly. But I can seem to find a way for the report to calculate the total number of distinct artists (i.e. the Total of the Total of cInverseCount) that performed this past season. I think someone addressed this in the older post below; but I can't seem to find a way to do it.

I was wondering if there is a way....?

One way to do this would be to count the related records in the Artists table. Alternatively, you could sort the records by season and by artist, then count the subgroups as explained here:

http://fmforums.com/...post__p__289204

Sorry,

I made the previous post without the proper quotation tags. The last paragraph is a quote of Comment's post...

[rbaintern]

I've tried the following

1) made a new field (temporarily called "TestTotal")

2) made it a calculation of Unstored, = Sum (sCountEmployees)

It seemed to work, giving me a total number of all the distinct artists that performed. The problem is when I clear the find, and do another search and then sort, the field is empty!?

BUT I when I go into "Manage Database" and then return to the database, the number magically shows up!

Can anyone explain this, or can someone explain how I can get this to run properly?

Thank you