A READER ASKS: Charting Professional Categories In FileMaker

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

A READER ASKS
I have been watching some of your videos and I wanted to ask you if you could give some useful tips to use the charting tool of filemaker pro 11. I am not a very advanced user and I have probably made some mistakes when designing my DB.

What I want to create at the end is a chart that will summarize the number of people I have in each "professional category".

In my database I have defined 8 professional catogries, but (and here is the mistake) I defined them in a "dropdown value list".  Now I have 2400 contacts and the chart option doesn't work.

As far as I have understood, I would need to create a "professional category count" calculated field to make the chart feature work, is that so?

Do you have any tutorial bout this or can recommend me any?


DWAYNE RESPONDS
Using the Count function is one way to go and probably the best way considering you want to build a chart. The technique I would explore is a sort of “splice” approach. That is to say you will create a new table (call it STATS for now) with two fields. The first will be “professional category” and the second will be “COUNT professional category”.

Create a relationship between the two tables using the “professional category” field for each side. Create a record in the STATS table for each possible category record. Then use the COUNT function in the “COUNT professional category” field to count the related records. Then base you chart upon this. Now this would NOT be found count savvy but will definitely meet your needs for overall counting.

ABOUT THE COUNT FUNCTION
Used to count the number of valid, populated entries in an indicated field ( repeating field or related field). Quite often the count function is used to count the number of related records in another table occurrence. It is important to know that the Count function is intelligent enough to ignore empty fields and will not count them. An example of the count function could be a relationship between a customer record and their invoices. If the total number of invoices for a particular customer is over 10, they qualify for a special promotion.

ABOUT FOUND SETS
FileMaker has some unique terms to go along with it’s unique way of searching for records. After you do a find for records that have a certain criteria, you will be looking at a subset of the overall records. The subset of records you are viewing at the time is called the found set and the other records are called the omitted set. 

Searches (aka performed finds) are not the only way to create a found / omitted set. If you are looking at all the records, omit one record, then you will have a found set of all records minus one and you will have an omitted set of one.

If you experiment beyond the basics, you will discover a relational technique called Go To Related Record (GTRR). This will show only the related records in a child table and that is also a way of developing a found / omitted set of records.