5 Replies Latest reply on Jan 12, 2012 10:06 AM by philmodjunk

    COUNT calculation field

    CourtneyGreen

      Title

      COUNT calculation field

      Post

      I am working on layouts/reports where I can see how many EVENTS each STUDENT attended. Right now, I have a calculation field in the STUDENTS table for COUNT(GrantParticipation::OpportunityID). However, this field displays all of the opportunities (funding, tutoring, learningop, events etc) that students attended and not just EVENTS.

      Where do I have to put a field, and what does the script say that will allow me to count only the EVENTS attended by each student? 

       

      Thanks in advance for your help!!

      Screen_Shot_of_Database.gif

        • 1. Re: COUNT calculation field
          philmodjunk

          You either have to redesign the relationship to match to only GrantParticipation records of type Event or you can use a one row filtered portal with a summary field defined in GrantParticipation that counts records. The portal filter would limit related recods to only those of type event. (The borders of a portal can be made invisible so that the one row portal looks just like any other field on the layout.

          You an also change table context by using a layout based on grant participation, performing a find to pull up only records of type Event. The same summary field I mentioned for the filtered portal would produce the desired counts here. (and sub summary parts can be used to "condense" entries down to one row for each student.)

          Your summary field would be defined as a "count of" summary field and you'd set it to count a field that is never empty such as the student ID field.

          Either summary report based on Grant Participation or A filtered portal to the same can produce your counts. The summary report can be a bit more flexible, allowing you different report options just be performing different finds and sorts. There is one key difference, however. If it is important to see a list of all students, even if they have not participated in any events, use the filtered portal option. Students that do not have at least one grant participation record of type event will not appear in the summary report.

          • 2. Re: COUNT calculation field
            CourtneyGreen

            Thanks for your quick response! I was wondering does it matter that the GrantParticipation table does not have the OpportunityType field... that field is only in the Opportunities Table.

            This is what I did, but it does not seem to work:

            I put a summary field in GrantsParticipation that counts records COUNT(StudentID)

            I created a portal based on GrantsParticipation that would filter that summary field by (Opportunities::OpportunityType)

            However the portal numbers are not right?? What am I doing wrong?

             

            • 3. Re: COUNT calculation field
              philmodjunk

              What was the exact filter expression that you used? You should be able to filter by comparing to Opportunities::opportunityType to a specified value.

              I put a summary field in GrantsParticipation that counts records COUNT(StudentID)

              That doesn't look quite right, but this could just be the way you chose to describe what you did. When you examine this field in Manage | database | fields. What field type is shown? If you have a field of type calculation, you have the wrong field type. You need a field of type summary.

              • 4. Re: COUNT calculation field
                CourtneyGreen

                 You're right I wasn't in a summary field type.. I got it to work!

                Now my next question is... is there a way to get these counts to divide out by Semester (OppSemester)?

                So right now it is showing only the EVENT type which is great, but if possible it would be helpful to also see those counts for each semester.

                Right Now it Looks like

                7 (Events attended)

                 

                It'd be helpful for it to look like this:

                Fall2011 2 (Events attended)

                Fall2012 5 (Events attended)

                 

                • 5. Re: COUNT calculation field
                  philmodjunk

                  Each line in the new version represents multiple records in the GrantParticipation table. Sure you don't want to try the summary report option? This will not be a big change when using that option.

                  If you must have this in a portal, you'll have to add a new table with one record for each semester that then links to GrantParticipation by Semester. Then this new table, in turn, can link to an occurrence of the GrantParticipation table so that your summary field can be added to the portal row to show the semester sub totals.

                  Students::anyField X Semesters::anyField
                  Semesters::Semester = GrantParticipationBySemester::Semester AND
                  Semester::constEvent = GrantParticipationBySemester::EventType

                  constEvent would be a calculation field that produces the same value (such as "event") for all records in semester that will match to the EventType field in GrantParticipation. EventType will be a bit of a challenge as it must be an indexed field for this to work and must be defined in GrantParticipation, not the related opportunity table for this relationship to work. Define it as a text field that will use a looked up field value to copy Event type from the opportunity table. The catch here, is that changing the event type in opportunity for an existing record, will not update this matching field in GrantParticipation. You may need to add a script to manage such updates to keep your data in good shape.

                  In this portal to Semesters, you'd add the summary field from grantParticipationBySemester. (can't add it from GrantParticipation, even though it refers to the same table.)

                  If you haven't added new table occurrences before:

                  In Manage | Database | relationships, make a new table occurrence of GrantParticipation by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.