12 Replies Latest reply on Nov 12, 2015 2:30 PM by fitch

# How to find number of records by state

I have a small database (500 records) and I'm trying to get a calculation of how many records per state and I'm not getting very far.

I spent a lot of time on this page trying all three methods but none worked, so clearly I'm too much of a novice and information is missing (using Filemaker 13 so the executeSQL should work, I think but it didn't).

Counting the number of unique values in a field | FileMaker

Ultimately, I need to know the number of records by state so I can find a average score for all the records in that state (so if I have 3 records in CA and they have scores of 1, 5 and 10, I would have 16/3 = 5 (would be great if I could round it off).

Any help is appreciated (I may end up doing to by hand since, while this seems simple, is perhaps beyond me).

Thanks,

Connie

• ###### 1. Re: How to find number of records by state

This is easy with a relationship.  Self join off state so each sate only has the same sate as a related records.  Then do a summary of the scores with a summary field or total the values of the list function.   Divide that by the count of the related records by using a summary field for count or just use value count function of the previously mentioned list.

• ###### 2. Re: How to find number of records by state

Thanks Jared.  I tried to create a self join relationship per the instructions in the link in my original post, but I couldn't figure it out.  Do I create a new table with the state field and then create a relationship to my existing table using state as the match?  The instructions said the join field was in the original table, and it would automatically ask me if I wanted to create a new table, but it never did, and I'm sure I didn't do it right.  I think if I can get the state count to work, I can set up the calculation to get the average, but I still don't have clue how to get the state count set up.

• ###### 3. Re: How to find number of records by state

Create a new "table occurrence" then use the state as the match.  Because its the same field it probably won't work to create the table occurrence automatically.

• ###### 4. Re: How to find number of records by state

Hmm, well if you mean by table occurence a new table with the field “state” I did that and created a relationship to my existing table on that field “state” and then I tried to follow the rest of the selfjoin instructions, but it just didn’t work.  I got blank field or numbers that were totally wrong.

I’m going to need some pretty basic step by step instructions, I think.

Connie

+++++++++++

Connie Mahan

Audubon Policy Office

Director, Grassroots Outreach

1200 18th Street NW, Suite 500

Washington, DC 20036

202-600-7967

http://www.audubonaction.org

• ###### 5. Re: How to find number of records by state

select the origin table. then lick the ++ button at the bottom of the relationship graph.  That will create another occurrence of the same table.  Then you can do a self join.

• ###### 6. Re: How to find number of records by state

Ok, that’s helpful, that’s a start. I’ll give it whirl using the rest of the other instructions.

Thanks!

Connie

+++++++++++

Connie Mahan

Audubon Policy Office

Director, Grassroots Outreach

1200 18th Street NW, Suite 500

Washington, DC 20036

202-600-7967

http://www.audubonaction.org

• ###### 7. Re: How to find number of records by state

I think I successfully created a self join table and then followed these instructions from a fm help page:

Next, create a calculation field called "Counter" with the following expression:

1 / Count (SelfJoin::state)

Note that you are using the "state" field from the related SelfJoin table and not the "state" field from your "original" table.

Finally, create a SummaryField called "CounterTotal" which is a total of the "Counter" field. The "CounterTotal" summary field will provide the total number of unique values in the "FieldName" field without having to sort your database.

I get a result -- but the number is something like 6.2352144 and it's the same for every record.  So that's clearly not correct. Still looking for ideas on how I can do this.

Thanks,

Connie

• ###### 8. Re: How to find number of records by state

The standard way to do this, and a basic reporting technique you should be aware of, is a summary field, and a sub summary report. I don't think Jared's suggestion is the right place to start.

To begin with, add a field to your contacts table, call it sCountID, and make it a summary field, count of ContactID.

More to follow.

Also see:

Simple example of a subsummary report | FileMaker

• ###### 9. Re: How to find number of records by state

Ha!!

Well I guess it depends on how you want to view the data.  If you want to see all the averages at once and use a list view to do so Bruce is correct.

If you want to see the data in form view do the self join or even sql.

I wouldn't say using summary fields is the "wrong place to start" but personally I trie to avoid them because they slow things down.

Why not try this calculation and your done.  No fuss!!

just place the STATE_FIELD and SCORE_FIELD with your full field names.

Let ( [

fullStateField = GetFieldName ( STATE_FIELD );

fullScoreField = GetFieldName ( SCORE_FIELD );

stateField = GetValue ( Substitute ( fullStateField ; "::" ; "¶" ) ; 2 );

scoreField = GetValue ( Substitute ( fullScoreField ; "::" ; "¶" ) ; 2 );

tableName = GetValue ( Substitute ( fullScoreField ; "::" ; "¶" ) ; 1 )

];

Evaluate (

ExecuteSQL (

"SELECT COUNT(*), SUM( "& scoreField &" )

FROM " & tablename &"

WHERE  " & StateField & " = ? " ;

"/" ; "" ; GetField ( fullStateField ) )

)

)

• ###### 10. Re: How to find number of records by state

Good suggestions. It isn't clear to me where Connie is at skill wise. So although there are reasons to avoid or be careful about using summary fields, they are basic, they work, they seem like a core skill.

Another feature of summary fields is how easily they reflect the data of the found set. In the population above, of approximately 500 entries, want to instead find by gender; or age; or some other attribute; and report on THAT distribution by state?

Easy, you start with the very same basic report design. But before running the report - just do the find you want.

• ###### 11. Re: How to find number of records by state

Well to answer your question, I can do basic stuff in Filemaker but I’ve never really tackled calculations and I’ve done some summary work in the past but it’s been a while.  That being a core skill sounds right to me and I wouldn’t mind dusting that off, for sure.  (We are doing some elaborate analysis with this set of records to break some new ground for my non profit org, and I refuse to use a spreadsheet to do it. I’ve used Filemaker almost from the beginning for flat and then later, some simple relationional databases and it’s been a good tool because it was easy and I could do most of what I needed without instruction; but I think if we are going to continue with this project, I need to get some basics under my belt, so I will look into some instruction. That being said, this list has been FABULOUSLY helpful in getting me started.  What I find very amusing are the arguments my questions seem to start among the experts as to the best way to do something!  : )  There is always more than one way to slice the onion. Anyway, my deep thanks for everyone’s time and expertise!)

Connie

+++++++++++

Connie Mahan

Audubon Policy Office

Director, Grassroots Outreach

1200 18th Street NW, Suite 500

Washington, DC 20036

202-600-7967