Displaying the value of values in detail view based on id

[routledge]

Hello Peeps

Quite a long title, so now I will explain. I had been using a MySQL trigger to calculate the value of values based on id, from my database. This works as so when our guys add there time to a task, the time from all tasks assigned to a bug gets calculated and placed into the bugs table in field total of tasks. see below the MySQL trigger

This is to insert from new:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `sugarcrm`.`tasks_bugs_effort_totals_insert`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `tasks_bugs_effort_totals_insert` BEFORE INSERT ON `tasks_cstm`
FOR EACH ROW BEGIN
REPLACE INTO bugs_cstm (id_c,total_tasks_effort_c)
SELECT tasks.parent_id, SUM(tasks_cstm.actual_effort_c)
FROM tasks_cstm LEFT JOIN tasks ON tasks_cstm.id_c = tasks.id
GROUP BY tasks.parent_id;
END;
$$

DELIMITER ;

This is to update:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `sugarcrm`.`tasks_bugs_effort_totals_update`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `tasks_bugs_effort_totals_update` AFTER UPDATE ON `tasks_cstm`
FOR EACH ROW BEGIN
UPDATE bugs_cstm
SET total_tasks_effort_c = (
SELECT SUM(tasks_cstm.actual_effort_c)
FROM tasks_cstm LEFT JOIN
tasks ON tasks_cstm.id_c = tasks.id
WHERE bugs_cstm.id_c = tasks.parent_id
GROUP BY tasks.parent_id
);
END;
$$

DELIMITER ;

Very simple and it works, however it isn't too scalabale and i'm running into issues. So what I have been attempting is updating the detail view for bugs in the custom file to add customCode to add this data, but this is not working. Can anyone supply tips on which would be the best way to accomplish getting the toatl values of a field based on id.

Regards

John

[routledge]

Me again

May have made this more complicated than it needs to be. All I require is the sum of fields from my tasks.actual_effort_c field to be entered into bugs.total_tasks_effort field.

It really shouldn't be this difficult, I built a logic hook that can calculate the total of two separate fields but this really does not work for me. Any suggestions would be welcomed

Regards

John