10 Replies Latest reply on Aug 19, 2013 7:06 PM by NicholasFernandez

    Count of Specific Value



      Count of Specific Value


           Hi All, 

           I feel like a fool for posting, but all of my searches return the wrong information:

           How do a create a summary "count of" that displays the number of records with a specific piece of data entered in a field. 

           For example, I have a dropdown field where the user can select "New," "Pick Up," or "Killed." I want to create a fielld that counts the number of records where a user has selected "New." 

           This should be so easy, I'm sure I'm missing something.

           Any help?

        • 1. Re: Count of Specific Value

               I sure hope this isnt a hitchhiking database........JUST KIDDING :) :)  (Come on, thats funny, no?)

               I guess the first question is what do you have so far?  If nothing, maybe this will help

               1.  In manage database you will need a new field.  Call it      z_count of status   Make it a Summary field.  Under options, click 'count of' radio button and select field you want it to count.  Under summarize repetitions, select 'individually'.

               2. Create a new report layout.  You just may need a head, sub-summary and Trailing Grand summary.

               3.  In the Sub Summary part definitions, select radio button 'Sub-summary when sorted by, and select the field you want to sort.

               On the Sub-summary part of the layout should be the field, the z-count field.

               Here's a screenshot of what you should end up with, and how the data looks.

               Let me know if were on the right track.

          • 2. Re: Count of Specific Value

                 What the report looks like. 

            • 3. Re: Count of Specific Value

                   Hi Steve,


                   Yes, very funny :)

                   Okay, so I wasn't doing the "individually," which solves part of the problem. But the big issue I'm still facing is whether I can keep subsummaries when not sorting by that field. Also, I want to be able to have the subsummary data show up in specific fields on another page (attached). 

                   I've seen some talk of doing a "self join" to create this look, but I haven't been able to pull it off. 



              • 4. Re: Count of Specific Value

                     Unlike the real guru's on this site, I'm just unable to see these things in my mind's eye.

                     I always need screenshots, etc.  But I'll ask some more questions and hopefully others will help too.

                     The six fields at the top, how do the numbers get in there? Are they portals?  If they are based on the info below, they seem to be incorrect.  I'm just trying to figure out what is going on.

                     I'm sure if you need those boxes to display a count of the related records where status is 'New', that could be done with some calculation and scripting.

                • 5. Re: Count of Specific Value


                       Yes, the numbers in the top boxes are wrong (hence my need for help, haha). The boxes would ideally contain tallies of each category. 

                       Currently they are a mix of summary and calculation fields in my attempts to get the project to work.


                  Box below L = number of records where "Type" is "L" (0)

                  Box below LC  = number of records where "Type" is "LC" (2)

                  and so on.

                  Total adds up these fields.

                       But...I want to be albe to have these counts in a header and not have them dependant on sorting.

                       I realize I've changed terminology from the original "New," Pickup," "Killed," but it's the same idea applied to a different data set.

                       Your original solution provided me with a great count (if somewhat odd in spacing), but I need to be able to maintain this count regardless of sorting and have it display within the other layout.

                       Does that make sense? (PDFs attached) 

                       Thanks for trying to help!

                  • 6. Re: Count of Specific Value

                         Now were getting somewhere.  Originally I thought you only wanted a running count if Status was 'new'.  But I see you just want a count of each Type.

                         Ok that's easier.  I just cant spew it off the top of my head on the fly (sorry).  If someone doesnt provide an answer, I'll figure it out  tomorrow morning (have to head home).  I have to make a quick sample file and play around with it.  Unless you can post this file on dropbox..

                         Edit:  based on your last post, I would disregard my earlier posts.  Those fields should be calculation, not summary fields (I think).  I'll play with it in the AM.

                    • 7. Re: Count of Specific Value

                           Thanks Steve

                           link to dropbox file: https://www.dropbox.com/sh/9feb2oupcmlv5b5/S8mhyB0m6n

                           It's the file named: Art MS FMP Version 2 Copy.fmp12

                           Thanks so much for your help!

                      • 8. Re: Count of Specific Value

                             Since you want totals in the header, you can selectively sum your records in three different ways:

                             A relationship that matches to only records of a specific type can be used. Then a summary field in the related table will report a count of just the related records. Calculation fields can be set up that match to only records of a specific type. And then you just place these summary fields from the related table occurrences in the header to show the totals.

                             A Set of one row filtered portals can be placed in the header, each filtering for a different "type". Then the same summary field from the related table can be placed in each of these filtered portals to show your counts.

                             Calculation fields with ExecuteSQL can also compute and display such totals. In the SQL expression, you can include a WHERE clause that limits the records counted to a specific type.

                        • 9. Re: Count of Specific Value

                               Sorry my friend, I took a shot, but it's very hard for me to even follow what's going on in your database.  This is not your fault, it is my inexperience.  I'll keep plugging away.

                          • 10. Re: Count of Specific Value

                                 Hi Phil and Steve,

                                 Thank you for getting back to me.

                                 Steve, don't worry about not being able to solve it. I appreciate your help and work on this project regardless!

                                 Phil, I'm sure that one of your solutions will end up working, I just have to spend some time learning the ropes. I appreciate you giving me some techniques to get started.

                                 Thanks again to both of you!