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

[skiss]

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....

[Genx]

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)

[skiss]

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...

[Genx]

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".

[skiss]

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...

[Genx]

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.

[skiss]

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.

[skiss]

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.

[Genx]

You want to be in layout mode not browse mode.

[skiss]

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.

[Genx]

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

[LaRetta]

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

[Genx]

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

[bcooney]

Take a look at this example.

ErrorType.fp7.zip

[Regulatory Affairs]

Just wanted to say that the advice gaven a couple months ago is still useful. It's helped me out of a dilemna. Thanks.