Announcement

Collapse
No announcement yet.

Issue with calculating a Percentage based fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Issue with calculating a Percentage based fields

    I have a grid that pulls data from several tables and uses the data to determine the percentage of those in the company and those who have completed the training.

    The problem I have is the grid calculates the percentage of the top row and carries that same percentage to all remaining rows.


    This is the calculation code I have in a php method which is triggered in the OnRecord event.

    Code:
    $bot_t = {bot_trained};
    $str = {strength};
    $bot_pr = (($bot_t*100)/$str);
    {bot_perc} = $bot_pr;
    Am I missing something for it to run for every row in the grid?

    The sql queries to pull the "SELECT COUNT(*); runs prior to the execution of this code and it works as intended and is pulling good data. I just can't get the percentage calculations to work correctly.

    What I am getting is the following

    Row 1
    unit - {strength} - {bot_trained} - {bot_perc}

    T1 - 25 - 5 - 25%
    T2 - 54 - 3 - 25%
    T3 - 108 - 1 - 25%

    Thanks.

  • #2
    try to 'echo' the values $bot_t and $str before calculating the percentage to check their value.


    Code:
     
     $bot_t = {bot_trained}; $str = {strength};  echo $bot_t . '--' . $str;  $bot_pr = (($bot_t*100)/$str); {bot_perc} = $bot_pr;

    Comment


    • #3
      Originally posted by nonkelmike View Post
      try to 'echo' the values $bot_t and $str before calculating the percentage to check their value.


      Code:
      $bot_t = {bot_trained}; $str = {strength}; echo $bot_t . '--' . $str; $bot_pr = (($bot_t*100)/$str); {bot_perc} = $bot_pr;
      I get the following from the echo

      Code:
      1--4 1--4
      I should be getting

      Code:
      1--4 23--69
      for the data set I ran

      Comment


      • #4

        {bot_trained} and {strength} are values from the GRID's SQL or do you define them using lookup or in the onrecord event?

        Comment


        • #5
          {bot_trained} and {strength} are added fields to the grid that use a sql lookup for the field. {bot_trained} looks for individuals that have a class record with the "BOT' in a particular unit. {strength} does they same thing except it is not looking at a specific class, it is only counting the number of people assigned to that unit using a sql lookup.

          {bot_trained}
          Code:
          SELECT COUNT(mt.member_id)
          FROM member_training mt INNER JOIN member_rank mr ON mt.member_id = mr.member_id
             INNER JOIN member_unit mu ON mt.member_id = mu.member_id
             INNER JOIN units ut ON mu.unit_id = ut.unit_id
             INNER JOIN training_classes tc ON mt.training_class = tc.training_class_id
             INNER JOIN member mb ON mt.member_id = mb.member_id
          WHERE ut.unit_active = 1 AND mr.rank_id BETWEEN 1 AND 12 AND tc.class_abbrev = 'BOT' AND ut.parent_bde = '{parent_bde}' AND mb.active = 1
          {strength}
          Code:
          SELECT
             COUNT(mb.member_id)
          FROM
             member mb INNER JOIN member_unit mu ON mb.member_id = mu.member_id
             INNER JOIN member_rank mr ON mb.member_id = mr.member_id
             INNER JOIN units ut ON mu.unit_id = ut.unit_id
          WHERE 
             mb.active = 1  AND ut.parent_bde = '{parent_bde}' AND mr.rank_id BETWEEN 1 AND 12
          grid sql
          Code:
          SELECT
             ut.parent_bde
          FROM
             units ut
          WHERE 
             ut.unit_active = 1 AND display_lvl = 2
          ORDER BY
             ut.display_order

          Comment


          • #6
            Well it started doing something different now....

            The first two lines are the same, the third line appears correct, however I don't have any records for that line.

            Comment


            • #7
              update: I had ScriptCase remote in this morning to look at the issue. He was not able to figure it out.

              All the data in the grid is correct except the calculated fields.

              Row 1 and Row 2 are duplicated only in the calculated fields, and all the other data is pushed down a row. so the data that should be in row 2 is now in row 3, and row 3 is in row 4, etc.

              anyone have issues like this or have any updates?

              Comment


              • #8
                Try running the sql selects for both bot_trained and strength in the onRecord event instead of using the added fields.

                Comment


                • #9
                  Originally posted by robydago View Post
                  Try running the sql selects for both bot_trained and strength in the onRecord event instead of using the added fields.
                  Thanks this fixed the issue it appears.

                  Comment

                  Working...
                  X