Struggling with data counts

[bcolburn]

I'm a relatively new FM user but have been forced to hit the ground running and am doing my best to learn along the way. This particular problem is giving me some trouble.

In generic terms, I have several fields in each record that have multiple possible responses (e.g. "Completed", "Incomplete", "Pending Data"). I would like to keep a running count of these responses for all records in the database - one for each field of interest. So basically, I can already look at the summary page for each individual record and see which tasks need to be completed, but I also want to be able to look at the database globally and use the existing data to say "X number of records still need this task completed" (i.e. "X number of records show this field entered as other than "Completed").

I have tried creating a Summary (Count) field in the layout, but I keep returning "0" as the calculated result. More importantly, I want to be sure I'm counting the number of times each individual response is selected and not just the number of times the field is completed in some way. I contemplated creating a second related checkbox to auto-fill if the field indicates the task is incomplete and then count the number of filled-in checkboxes in the database for each field (thereby compressing the value list to yes/no for the main point of interest only), but this is less detailed information than I would like and seems like more work than it should be. Plus, I am reticent to create dummy fields like this if more effective ways to do the same thing exist.

Can anyone help? Bear in mind, I'm relatively new to this program, so please be gentle with me!

[doughemi]

Count() calculations only work on related records.

Create a new text field g.CompletedMatch. Set it to be globally stored (in Options->storage on the fields dialog) and populate it with the text "Completed"

Under File, choose Manage->database and click Database. Click the Relationship tab.

1.Click your table on the screen and then click the ++ button at the bottom of the screen. Name the new Table Occurrence (TO) generated "MytableCompleted"

2. Drag a relationship between g.CompletedMatch on your original table to the field you wish to analyze (myfield) in Mytable2.

3. Create a calculation field and enter the calculation Count(MytableCompleted::myfield)

Repeat steps above for each response you wish to tally.

This is by no means elegant, and not very scalable, but it introduces you to the concept of related records and self-join relationships.

[bcolburn]

So this actually worked very well for my purposes, but I'm struggling with the last part. For my test field, I created a calculation field where any response I am interested in gets marked as "Yes" (which essentially functions as a tally mark for the next field). I then created a globally-stored calculation field in a separate table that is programmed to count the field I just created and made a relationship between those two fields. However, when I go back to browse mode, my global count field is blank. Do you have any idea why? I also tried creating a count field in the first table and running the count function there too. I'm not sure what's wrong. Is there something about how the fields are stored that would them not work?

[doughemi]

See the attached sample file. Perhaps it will make things clearer.

Count.fp7.zip

[bcolburn]

So unfortunately, I'm still struggling with this. Here's what I have:

Tables: "Checklist" and "Study Progress". "Checklist" is meant to be a way of tracking each individual record's progress and "Study Progress" is supposed to be a way to monitor the entire database's progres

Layouts: "Checklist" and "Study Progress", where "Checklist" has all the data of interest and both fields display records from the table "Checklist"

Fields: I have one calculation field to mark if Task 1 is complete by marking it as "Yes" or "No" (MARK Task 1 Incomplete) according to certain criteria and a corresponding summary field to keep a running count of that field (COUNT Task 1). These fields exist in both tables mentioned above and are set to equal each other. The field COUNT Task 1 is set as such:

[ COUNT Task 1 = Count of Mark Task 1 Incomplete (running with restart) when sorted by other field ]

*** The "Mark Task 1 Incomplete field I referenced here is from the table "Study Progress" and not "Checklist".

I just don't understand which table I am supposed to be referencing and why it matters, since I have a relationship to set the equivalent values to be equal to one another. I am also still unclear, even after reading up online, on whether setting an existing value to be stored globally will make all values equal across the entire database and thereby erase the data specific to each record that has already been entered. It seems that SOME field here has to be globally stored, but I'm not sure which one and whether or not I can set it as such without erasing pre-existing data.

Is this helpful? I've tried to be as specific as possible.

[doughemi]

You only need one table. Both layouts will be based on that one table. In your Relationship Diagram, you will create a new Table Occurrence (NOT a separate table). You will need to have a global field g.inquiryField which will be filled with the response you wish to count. Call your original TO Checklist, and your new TO StudyProgress. Create a relationship Checklist::g.InquiryField = StudyProgress::TaskStatus. Note that TaskStatus cannot be an unstored calculation. Your count field would be a calculation count(StudyProgress::TaskStatus).

Each task should be a separate record in Checklist, containing information about the task, including the TaskStatus field.

[Fitch]

Count() calculations only work on related records.

That's not true, although the Count function is probably most commonly used that way. And the original question was, I think, about using a Summary field, which is based on the found set (not on related records).

If you have a field with multiple values, and want to count the values separately, you may be better off putting them in separate fields to begin with. But if the values are mutually exclusive (like complete/incomplete/pending), and a simple sub-summary report will suffice, then all you'd need is a single Summary(count) field, placed on a sub-summary part that is based on the field you're counting. The New Layout command can walk you through creating that report.

PS: I would move your Tasks into their own table. Any time I see a "field 1, field 2" structure it raises a big red flag.

[bcolburn]

THAT's the problem. It says I can't store the TaskStatus fields because they reference a related field, a summary field, an unstored calculation field, or field with global storage. When I looked at one particular example to see which fields I referenced in the calculation, none of these seems the case. It seems it must be a "related field" since it is an indexed text field I refer to in the calculation for TaskStatus. What does this mean and how can I fix it?

[doughemi]

Note that TaskStatus cannot be an unstored calculation.

.

Relationships cannot have a calculation field on the "many" side. I don't understand why you think you need a calculation, but if you really do, change the field to a text field with an autoentered calculated value (be sure to unselect the "Do not replace existing value" checkbox in the auto-enter dialog).