AnsweredAssumed Answered

Counting portal rows where field = certain value

Question asked by theaudone on May 8, 2018
Latest reply on May 8, 2018 by theaudone



I have been struggling with this for a few days and I'm hoping someone can help me. I feel like I've read every single post out here on this topic but I still can't seem to solve my problem.


I have two tables:



evalID 1

evalID 2

evalID 3



detailsID  1    evalID 1     rating_code 6

detailsID 2     evalID 1     rating_code 6

detailsID 3     evalID 1     rating_code 4 


I want to count the rows for this particular evalID where the employee got a rating of 6. I have a sorted (not filtered) portal set up that works correctly looking at the detail table already, it only shows me the rows for that particular evalID. But for some reason my calculation field is grabbing ALL the rows rated level 6 from ALL the performance evaluations, not just the one the portal is looking at. Why? Am I doing the '?' incorrectly to use the current evalID in the SQL statement?


ValueCount ( ExecuteSQL ( "SELECT rating_code FROM evaluation_details WHERE rating_code = 6 AND evalID = ? "; "" ; "" ))      


I have tried placing it in the portal itself, in a sub summary part on the layout by evalID, I feel like I've tried everything. Though obviously I'm missing something somewhere.


I have also tried creating Summary fields and calculation fields instead of ExecuteSQL but I can't get anything to work correctly. I tried this method too:


GetAsNumber(GetLayoutObjectAttribute("ppkasCount" ; "6"))


The one piece I do have working is a Summary field counting the rows the employee was rated on correctly, for that evalID:

     SumRatedRows   type Summary      = Count of rating_code


Ultimately, I want to calculate a percentage of the rows the employee was rated on with a level 6 out of the total number of rows they were rated on. So I do need to be able to use the number in a later calculation.


If anyone could please help me puzzle through this I would be so appreciative!


Thanks, Audrey