Counting occurrences...

[GarrettC]

The toughest questions are the ones that are tough to frame in your mind, I have been trying to figure out how to ask this question for a couple of days.

Some background...

We have a database with 3 tables: Blogs, Blog Comments and Blog Stats. The first table, Blogs, has a ID # field that is tied (related) to a key field in the Blog Comments table. This is what ties the correct comments to the corresponding blog, this is working well.

The 3rd table, Blog Stats is tied to the Blogs table via the blog author's name. It has 4 fields that show statistics. A record is created for each Blog author, this is not a problem as there are few of them, less than a 100. Each of these records has a field that named Blog Count, as the author name is tied between the 2 tables I can use the aggregate function, Count ( field {; field...} ) to count the occurrences of a particular blog author in the 1500+. t

This table is a list view: Author Name, Blog Count, Total Comments, Comment Ratio and Active Factor.

What I would like to do next is show how many times a particular author commented on another author. My other desire is to count how many times any one comment author commented. The issue here is that I cannot use my other method because in this table there are over a 100,000 records, far too many to create a unique record for each author. So I need some kind of system to do this and I really have no idea where to start.

I do not think I entirely understand the Count function and think I may have gotten lucky on the first use. Attempts to use it for these solutions has not yielded a successful result. I am now officially stuck.

Any guidance would appreciated, thanks.

[Fenton]

What you need is the author of the Comment and the author of the original Blog, and a list of authors. You may need to bring the original author's name or id into the Comments table. Then, you'd need a relationship from the Author's name to the Comment's authors name. The 2nd criterial of that relationship would be from the Author's name to the original author's name, with not equals (≠) as the operator. That produces "comments which were made by this author to other author's blogs."

You could use that to get a count of comments. If you pointed the calculation to Blogs you'd get a count of blogs. But it seems we're missing a "Blog Authors" table, or would these be the same authors? In that case you'd need another table occurrence of Authors, off of Blogs, based on Author ID or name.

It's kind of confusing to explain this otherwise circular relationship. Could you post an example of this file?

[GarrettC]

Sure I could post it. Your explanation is a bit hard to understand.

Its now quite big, 164 MB. If I deleted a number of records I could reduce the size but I might get rid of something you need to see.

What do you think?

[Fenton]

Wow, it sounded like a very simple 3-table file. Are there lots of other tables, or just lots of data? In any case, I think the best would be to Save As Clone. Then enter or import just enough records so that we'll have something to work with. Or, if a lot of tables, you could just recreate this part of it. You have FileMaker Pro Advanced, you can just copy/paste tables, then recreate the few relationships.

[GarrettC]

Lots of data and lot of indexes to speed searching and sorts.

Blogs=1700 Comments=98,700

I will try the Cloning. How do you cut and Paste Tables?

I am using version 7 of Filemaker and Mac OS Tiger.

I am not sure why my signature says 8 and Panther, I cannot find how to correct that information.

[Fenton]

Well, if you had FileMaker Pro Advanced you could cut & paste tables. Perhaps with 7 you can Import from a table, and choose Create New Table in the Target table drop-down (at the bottom). I can't remember when that was added.

If it is a simple file with lots of records, then File, Save A Clone would work well to slim it down. But it would be good to add a few fake sample records, with data that would illustrate the problem/point you're trying to solve/we're trying to explain.

It is really not a complex problem and would not require very many fields or records. We would want to see the relationships you have. But we (the collective we -) could likely just add the ones needed.

[Fenton]

You can change your Filemaker display settings by going to the top left, My Profile, Control Panel, FileMaker questions.