Detailed field 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!

[LaRetta]

I have several fields in each record that have multiple possible responses

That structure is probably not optimum. That is flat-file style instead of relational. When you have 'many' of anything relating to one then the 'many' usually should be split so each 'many' can be counted, found and calculated based upon their individual values (as you are now finding out). What happens if you want to add another 'many' field? You will have to change every calculation, script etc just to accommodate and add the extra field whereas, if related, you simply add a record. We would need to know the purpose of those 'like' fields and how many fields are involved before we could qualify further but I would seriously consider it.

But for your situation at present, create new calculation field called cNumComplete (result is number) and use one of the two formulas below, substituting 'text#' for your real field names. This will give you a count per record of the fields which hold Complete:

( text1 = "Complete" ) + ( text2 = "Complete") + ( text3 = "Complete" )

or

Let ( string = " " & text1 & " " & text2 & " " & text3 ;

PatternCount ( string ; " Complete" )

)

... then create a summary field sTotalCompleted = Total of cNumComplete

[LaRetta]

Upon reread again, you might want to count the total of each field INDIVIDUALLY. So you will need an individual calculation for each one.. It would be simple:

Self = "Complete"

None of this is the way to go when, if they were related records, you would have only one calc.