Mysql triggers - subpanel 'select action' issue

SuiteCRM Forum - English Language Developer Collaboration
1

Hello everyone

I trying to do something like automatically calculate score field depended on a one-to-many relationship

So what my goal - do auto calculating score with just summarize all score of related one-to-many relationship record

For example, In Account module I created score field and it’s score should be the sum of all score records his subpanel Contacts (Account-contacts is a one-to-many relationship)
The formula = Account’s record score = score relationship1 + score relationship2 + score relationship3 …

The easiest way I think is to make MySQL triggers for it
I have already did triggers for insert, delete, update and all of them works well except of subpanel action

The subpanel action is adding exist record via choosing it from subpanel

image
image1129×285 13.3 KB

What I expect when choosing a existing record = Contact record should recalculate (OLD)Account’s score and also recalculate (NEW)Account’s score
But it only recalculates (OLD)Account’s score and I don’t know why

I tested this ‘changing Account’ mechanism by Editview Contact and there is work well

Here is my MySQL trigger for this. It runs when OLD.id_organization != NEW.id_organization
id_organization is where I stored relationship ID (I rebuild my one-to-many relationship by ‘Suitecrm Nice Relationship’)

BEGIN
 DECLARE oldAccountScore INT; 
 DECLARE newAccountScore INT; 
 
 IF OLD.id_organization != NEW.id_organization THEN

 SET oldAccountScore = (SELECT organizations.score FROM organizations WHERE organizations.id = OLD.id_organization);
 SET newAccountScore = (SELECT organizations.score FROM organizations WHERE organizations.id = NEW.id_organization);

 UPDATE organizations SET score = oldAccountScore - OLD.score WHERE id = OLD.id_organization;
 UPDATE organizations SET score = newAccountScore + NEW.score, description = 'by update' WHERE id = NEW.id_organization;
 
  END IF;
END
2

Hi,
I haven’t debugged your code/problem, but I just wanted to mention that you could do the same within a logic hook. The benefits:

  • no logic on database level -> all calculations are done within your application. I wouldn’t recommend db triggers, as they are often forgotten when switching environments.
  • your code remains debug-able, and by using breakpoints, you can exactly see how the calculation is processed (and a possible new dev. would probably not look for application logic within the db)
  • you can use workflows/logic hooks: if you change values via sql-statements, the CRM won’t trigger any workflows as the framework was bypassed.
1 Like