Counting Sub-Summary

[We Can Deb]

Hello everyone,

 

I am having a problem with counting the unique values in a sub-summary report.  I have tried to use the Execute SQL calculation, but it didn't work, so I've completed the 'sum the reciprocal' instructions I've found in several forums.

 

It works great until I do a find.  Then the field is blank and won't recalculate again unless I make a change to the field definition in database and then change it back.

 

The layout/report and all of the fields in it exist in the same table and the calculation fields are unstored.

 

Detail:  records are entered through a layout called: Message Encounter Form in which the users will take messages (each of which may have many notes attached) from a Contact.  I can tally the notes and sort by purpose of message (MEF Tallies Layout) and it works fine.   (I did sneak in the offending field on this layout, but it works only when the sub-summary report, Contact Tally Unduped is working) 

 

I also need to count the number of people who have made calls, without counting the multiple number of times they may have contacted the office (unduplicated messages).  This is the troublesome layout: Contact Tally Unduped.  The field, Unique Count, will not calculate once I have performed a find using date.

 

A file is attached.  Thank you in advance for any suggestions!CONTACTS Sample.zip

[eos]

Hi Deb,

 

 Thank you in advance for any suggestions!CONTACTS Sample.zip

 

Here's the first one: post unprotected files or provide the credentials … 

[We Can Deb]

So sorry!  I forgot to mention that!

 

The user name is Test and there is no password.

 

Thank you!

[We Can Deb]

Hello everyone and thank you in advance for any advice.

 

You may have picked up on this already, but there is another problem with counting and that is with the checkboxes. 

 

When I run the summary report (MEF Tallies) based on purpose, any checkbox selections that are greater than 1 per message record are not incorporated into the totals.  I am working diligently on finding the answer to this issue also. 

 

Not only are there lots of purposes in the value list, but the users tend to change the list every so often, so I'd prefer not to have to add a field for each value.

 

Thank you again,

Debbie

[eos]

Deb –

 

if you want to report on Purposes, do not use a value list and checkboxes (a general rule for data you want to report on!); use a Purpose table to define the Purposes to choose from, and a child table (like you do for notes) to record the purposes for a message encounter. Then create the report in that child table.

 

You need a certain level of granularity, as a brief demonstration will show: imagine your summary comprises 6 messages, each with 3 purposes, for a total of (say) 13 distinct purposes; then you would have to show a summarized list with 13 entries – but you only have 6 message records! If, OTOH, you use a child table, then you will have a total of 18 records with non-distinct purposes, which you can sort and summarize/condense into those 13 distinct purposes.

 

Users can create new purposes (or edit existing ones) by adding records to the Purposes table.

 

Have a look inside this file; there are two scripts: one two create new Purpose records, one to create join table records for the existing checked values in Messages. Of course, you must create those two tables.

CONTACTS Sample_eos.fmp12.zip

[We Can Deb]

Dear EOS,

 

Thank you so much for your work!  I understand what you're proposing and you were quite helpful in forcing me to ask the users for more detail on how they count the messages.  I wasn't clear on the specifics, so it was more confusing than it needed to be.  I have resolved the problems with counting the number of notes by purpose based on your clarifying my issues and can now count the unique values with a looping script!

 

Again, this forum is a life-saver.  Thank you!

 

Debbie