Jump to content
Sign in to follow this  
skiss

How to do in filemaker the equivalent of the SQL Count and Group By function ?

Recommended Posts

Hi,

I have about 60 000 records, that describe a list of incidents. Each record has an "error_type" field. It contains a string.

What I would like to do is described very simply in SQL ;)

SELECT error_type, COUNT(error_type) AS error_type_count FROM table GROUP BY error_type ORDER BY error_type_count DESC

Basically I want to get a count of how many incidents there has been for each type of error (each "error_type" value).

What's the easiest way to do that in Filemaker ?

What I have done so far is add a summary field, that I have set to be a "running count" of the field "error_type". I have also enabled the function "restart summary for each sorted group".

I it gives me this kind of output :

error_type_1 1

error_type_1 2

error_type_1 3

error_type_1 4

error_type_1 5

error_type_2 1

error_type_2 2

error_type_2 3

In this example, what I'm looking for would be :

error_type_1 5

error_type_2 3

I have tried to export the records, and have them grouped then, but it gives me that :

error_type_1 8

error_type_2

Basically it adds the total number of records and put them on the first line.

Any ideas ? It would be sooo quickly done in SQL....

Share this post


Link to post
Share on other sites

Read up on summary fields.

Essentially, you make a summary field - being the count of your error_type field.

Next you create a new layout, in layout mode you will see a button called parts - drag this to your layout. Select Sub Summary and select your error_type field where it says "sub summary by" or something like that.

Next put your error_type field into this subsumarry part, and put your summary field next to that.

Finally, sort your records based on the error_type field (Records->Sort Records: you can also do this via script)

Share this post


Link to post
Share on other sites

I have just done what you told me.

My layout looks like this ;)

--Header

--Sub-summary by error_type

[error_type field] [summary field]

--Footer

The thing is I get this :

[Error_type] [summary]

D4690 1

D4690 2

D4690 3

FL4960 1

FL4960 2

J2800 1

J2800 2

J2800 3

J2800 4

My summary field for counting the error_type is setup this way :

count of "error_type"

when sorted by "error_type"

"Running count" is checked

"Restart summary for each sorted group" is checked also

As you know, I would like to get only one line for each error_type, with the total number of occurences. Not a count from 1 up to the total value.

What I'm looking for would be :

[Error_type] [summary]

D4690 3

FL4960 2

J2800 4

It's my second day in Filemaker, it's intense.

It's not so complicated, it's just I wanna do very special things right from the beginning.

Any ideas on how to get rid of all the duplicates and have only the total number of the count ? I could probably right a script but I don't have much time. I hope there is an easier way. Otherwise I'll export it as tab-delimited and write a perl script...

Share this post


Link to post
Share on other sites

Hmm, okay...

1) Untick "running count" for your summary.

2) Make sure there is no body part on your layout.

3) You have to run the sort by error_type as instructed - this will eliminate the "duplicates".

Share this post


Link to post
Share on other sites

I did have a body in my layout, sorry I'm a bit tired. But when I remove it, and place the two fields I told you about in my subsummary, the result I get in the "browse mode" is a complete blank page. Nothing at all.

I have tried to untick the "running count", at first what it did is change the 1,2,3,4,5 pattern to the total of records I had in my table (basically it counted how many records had a value in the field error_type, and it put that result in each single record). But as soon as I moved the two fields from the boddy part of the layout to the subsummary part, the window became blank, whatever this checkbox setting.

And then there's nothing to sort either cause the sort dialog doesn't give me any fields to choose from for the sort, since they aren't any.

It seems what I'm trying to do is not that easy...

Share this post


Link to post
Share on other sites

No, its just the way of doing it that at first is not entirely straight forward - but that's the same with any program or programming language for that matter.

This all comes back to requiring sorting your records (the equivilant of "order by" in your SQL statement).

When you click Records->Sort a dialog shows up.

Up the very top left corner of the dialog it says Current Layout and has a drop down. Click this drop down and choose "current table" instead. This should give you some fields. Move error_type accross to the sort side and select descending.

Share this post


Link to post
Share on other sites

I have done that also.

I clicked and chose "current table". I picked error_type and clicked sort.

The window is still completely blank. It became blank when I moved my two fields "error_type" and "count" (my summary field) to the subsummary section instead of the body, and deleted the body.

Also, you told me to sort by error_type, but it's a string, and I don't really want to sort by alphabetical order, I would like to sort by count. But my summary field is grey in the sort list we just talked about. Even when I go and pick "current table" instead of current layout.

There must be something else.

Share this post


Link to post
Share on other sites

The sort section makes sense, cause there is this "rearrange by summary field" function. I think that's the right settings.

But I still have a completely blank window as soon as I go to Browse mode.

Share this post


Link to post
Share on other sites

You want to be in layout mode not browse mode.

Share this post


Link to post
Share on other sites

I am in layout mode when I edit my layout.

I have a layout that's like this ;)

--Header

--Sub-summary by error_type

[error_type field] [summary field]

--Footer

But then I want to view the results right ? So I go to Browse mode. How do you see the results if you don't go to browse mode ? And in browse mode, I come pretty close to what I want, but I still have the duplicates.

The closest I was able to get is by putting the two fields in the body part of the layout (cause if I put them in the subsummary and leave nothing in the body part, I get as I told you a blank result page when I go to browse mode).

And I had to check the checkbox "running count" and "restart summary for each sorted group".

I have also tried exactly what you told me but it didn't seem to work.

Share this post


Link to post
Share on other sites

Lol, sorry, I meant the other one... ah, preview mode that's the one.

Share this post


Link to post
Share on other sites

It would sure be easier just to post your file. :wink2:

Share this post


Link to post
Share on other sites

You know, i was just going to suggest that lol.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.