: FMP4 summaries of checked box fields

: FMP4 summaries of checked box fields

Post by Aaron Cies » Thu, 20 Aug 1998 04:00:00



Hello;

I have two questions regarding summary fields.  I have a FMP4 database
with a field "test" with a value list "CT, MRI, TCD, etc...".  On the data
entry layout, the field is formated as a checkbox since each patient can
have more that one test.  I have created a report layout that uses a
sub-summary when sorted by "test" to report the number of patients
recieveng each test.

(1)  When I sort by "test" and enter preview mode, I get strange results.
Example:  there are three patients, patient 1 has a CT, patient 2 has a
CT, patient 3 has a CT and MRI.  The result are as shown, # patients is a
summary field.

                              total patients: 3

      test: CT                # patients: 2
      test: CT                # patients: 1
      test: MRI               # patients: 1

Question:  How do I gets the priview page to display correctly?
      test: CT                # patients: 3
      test: MRI               # patients: 1

(2)  If a patient has more than two values selected in a check box field,
only the first two values are displayed in the summary part.  

Question:  How do I get the summary part to display all of the values?

--
Aaron Ciesar
Data Manager/Analyst
UPMC Stroke Institute

 
 
 

: FMP4 summaries of checked box fields

Post by Chuck Gardn » Thu, 20 Aug 1998 04:00:00




>>Hello;

>>I have two questions regarding summary fields.  I have a FMP4 database
>>with a field "test" with a value list "CT, MRI, TCD, etc...".  On the data
>>entry layout, the field is formated as a checkbox since each patient can
>>have more that one test.  I have created a report layout that uses a
>>sub-summary when sorted by "test" to report the number of patients
>>recieveng each test.

>>(1)  When I sort by "test" and enter preview mode, I get strange results.
>>Example:  there are three patients, patient 1 has a CT, patient 2 has a
>>CT, patient 3 has a CT and MRI.  The result are as shown, # patients is a
>>summary field.

>>                              total patients: 3

>>      test: CT                # patients: 2
>>      test: CT                # patients: 1
>>      test: MRI               # patients: 1

>>Question:  How do I gets the priview page to display correctly?
>>      test: CT                # patients: 3
>>      test: MRI               # patients: 1

>>(2)  If a patient has more than two values selected in a check box field,
>>only the first two values are displayed in the summary part.  

>>Question:  How do I get the summary part to display all of the values?

Assuming that your database "Patients.FP3" has one record per patient a
solution would be to create a separate database "Tests.FP3" for test
data.  Test data would be entered via a scrolling portal in "Patients.FP3"
via the relationship "Tests"  = Patients.FP3::PatientID --

Quote:>"Tests.FP3::PatientID with a layout like this:

PatientID:  [ 012313 ]
Name: [  Smith  ] [ John ]  

Tests (portal)

Date  Test  
------------------------
08-01-98  MRI   [ < ]  
------------------------
08-01-98  CT    [ < ]  
------------------------
08-15-98  CT    [ < ]  
------------------------

What you would then have in Tests.FP3 is one record per test you can sort
and summarize correctly. Because the information is entered via a
scrolling portal you can enter a infinite number of tests per patient. The
[ < ] in the illustration above would be a button on the portal row
scripted to "goto related record" this can be used to enter more detailed
data in the test.

You can show also show totals in the patient record using count() and
sum() functions based on the portal/relationship.

For a total of all tests for the patient tests use a calc field = count
(Tests::Date).

If you want to show the number of occurances of each test for the patient
you'll need some additional fields:

1) add a secondary key "PatientTest" in "Tests.FP3" using a calc field =
PatientID&Test.

2) Then in "Patients.FP3" you'll need to create a similar secondary key
field relationship for each test. Do this by  making your patient ID code
an autoenter serial number (created automatically when the record is first
created, and then creating secondary keys fields, one for each test type,
by using autoenter by calculation:

MRIkey =  PatientID&"MRI"
CTkey = PatientID&"CT"
 and so on..

3) Create relationships based on the secondary keys.  The relationships
would be:

"MRICount"   = Patients::MRIkey --> Tests::PatientsTest
"CTCount" =    Patients::Ctkey --> Tests::PatientsTest

and so on...

4) Now create counter calc fields based on each relationship

CountOfMRI = sum(MRICount::Date*)  * you can specify any field that has data
CountOfCT = sum(CTCount::Date*)

Totals of all tests (all patients) by type from Patients using global key
fields:

1) Create a global field for each test type and enter the test type into
the field (i.e. gCT contains "CT").

2) Create relationships based on these fields

"GlobalCT" = Patients::gCT --> Tests::Test

3) Create colc fields for the totals based on these relationships:

"CountAllCT" = sum(GlobalCT::Date*)  *any field in Tests.FP3.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
cgardner-at-nova.org * http://super.nova.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 
 
 

: FMP4 summaries of checked box fields

Post by Peter Schwar » Fri, 21 Aug 1998 04:00:00


Aaron,

I suggest you should redesign your database to be relational, i.e. the test
field should be in a separate file with a one-to-many relationship with the
patient file. Achieving the desired results would be pretty easy then.

If you want to keep a flat file structure maybe this will help:
First create a few calculation fields like TestCT, TestMRI and TestTCD. The
calculation in each of these new fields should be something like:
If(Patterncount(test,"CT")=1,1,0)

Then you can use these in Total fields which can be displayed in a Sub
Summary part in your report.

Peter

 
 
 

: FMP4 summaries of checked box fields

Post by Daryl L. Housto » Fri, 21 Aug 1998 04:00:00


I've never used summaries very much, and I've probably as a result done a lot
more extra work in trying to get things done.  Here's how I would handle
something like this.  Write a script to run the report.  The script would do
the following steps.  Before you look too hard at any of the solutions I
offer, glance over all of them, since each one seems to me an improvement on
its predecessor and none of them may, in fact, be suitable for your needs.

- Define a global count field and set it to zero.
- For each kind of test, define a global temp field ("tempCTcount", for
example)
- Find all records that you wish to include.
- Go to the first record.
- Loop
   - If[PatternCount(test, "CT") > 0]
      - SetField["count", "count + 1"]
   - EndIf
   - Go to Record[Next, Exit after Last]
- End Loop
- SetField["tempCTcount", "count"]

Do that series of steps for each kind of test you have, resetting count to
zero before each loop.  (Or you could just dispense with the count field, have
a tempcountfield for each test, and test for each one within a single loop, as
follows):

- SetField[tempCTcount, "0"]
- SetField[...other count fields..., "0"]
- SetField[tempMRIcount, "0"]
- Find All[]
- Go to Record[First]
- Loop
    - If[PatternCount(test, "CT") > 0]
      - SetField[tempCTcount, "tempCTcount  + 1"]
    - End If
    - If[PatternCount(test, "... other tests...") > 0]
      - SetField(for each one, have an if-statement and set the appropriate
field to field + 1)
    - End If
    - If[PatternCount(test, "MRI") > 0]
      - SetField[tempMRIcount, "tempMRIcount + 1"]
    - End If
    - Go to Record[Exit after Last, Next]
- End Loop

Then, you set up the layout that will display this info so that the fields
actually displayed are the temp<test>count fields.  To view the info, you run
the script, which executes the steps and kicks you into Preview mode, etc.  A
downfall to this kind of scripting is of course that it takes up execution
time.  If you've got hundreds or thousands of records and you have to execute
a script like this for each one, you'll find yourself sitting around idle for
a couple of minutes.  So I'd call this method maybe a last-ditch method.

Perhaps an even better method would be to do a series of Finds on your
checkbox field.  Define and set to zero a temp count field for each test.  For
each kind of test, do this:

- SetField[tempCTcount, "0"]
- Enter Find Mode
- SetField[test, "CT"]
- Perform Find[]  # No restore, no pause
- Go to Record[First]
- Loop
   - SetField[tempCTcount, "tempCTcount + 1"]
   - Go to Record[Exit after Last, Next]
- End Loop

Then have the temp count fields be the ones displayed in your report.  This
would be a good bit quicker, I think than what I gave above, since simple find
requests always go quickly and the computation here is pretty basic.

With apologies for not being more helpful on actual summary parts/fields,

Daryl Houston


> Hello;

> I have two questions regarding summary fields.  I have a FMP4 database
> with a field "test" with a value list "CT, MRI, TCD, etc...".  On the data
> entry layout, the field is formated as a checkbox since each patient can
> have more that one test.  I have created a report layout that uses a
> sub-summary when sorted by "test" to report the number of patients
> recieveng each test.

> (1)  When I sort by "test" and enter preview mode, I get strange results.
> Example:  there are three patients, patient 1 has a CT, patient 2 has a
> CT, patient 3 has a CT and MRI.  The result are as shown, # patients is a
> summary field.

>                               total patients: 3

>       test: CT                # patients: 2
>       test: CT                # patients: 1
>       test: MRI               # patients: 1

> Question:  How do I gets the priview page to display correctly?
>       test: CT                # patients: 3
>       test: MRI               # patients: 1

> (2)  If a patient has more than two values selected in a check box field,
> only the first two values are displayed in the summary part.

> Question:  How do I get the summary part to display all of the values?

> --
> Aaron Ciesar
> Data Manager/Analyst
> UPMC Stroke Institute

 
 
 

1. Summary Reports - Check Boxes with value list

I have a text field called <options> in a FM Pro 5.0v3 database. On the data
entry layout this field is formatted to use a value list with 10 options,
displayed as check boxes. I have created a summary report to report on how
many options are ticked for a particular found set, using a summary field
<total_options> which is a count of <options>.

The report works fine when only one value is selected in the check boxes -
then the numbers are correct. However when a record has more than one value
selected in the check box it seems to only pick up one value rather than
counting all values, thus producing misleadings figures on the summary
report. For example, it might report that there were 33 people who had
selected option 3, whereas there were 39 in total if you do a find on that
option.

Does anybody know if you can produce accurate summary reports based on
multiple values in a check box field?

Many thanks in advance - Andrew

2. net8 assistant prevent removing service name/mlml

3. validity checking a summary field

4. Database Modeling Tool

5. How to Display Bit Fields as Check Boxes is Data Grid

6. Rookie Question: Are User-Defined datatypes just for standardizing...

7. Event problem with check box field

8. Getting metadata

9. Script to select check box item in field

10. Checking more boxes in the same field?

11. Check-Box Field Totals

12. Summary fields or Summary table?