SugarCRM is an opensource CRM solution made with php and mysql, it is also available in a paid support version. It is a solid CRM with tons of features and some add-ons available. It is highly customizable for many types of businesses to work with.
Why update date modified?
CRM's can be used in a number of different ways, in many respects how the system is set up and the processes the users establish dictate how useful the CRM will be. In this case the system was pre-existing and the sales force has been using it for quite some time, so I didn't really want to try and change their process.
In SugarCRM the accounts screen view generates a table with numerous columns, one of which is the date modified which can be used for sorting. The sales team liked to use this to see which of their clients they had last worked with; however saving a note does not change the date modified. Only updating the main record updates the the data modified in the database, so they would create a note then do a save on the record to force a change on the date modified field.
I researched a number of solutions, there was logic hooks, custom fields and a number of things people have done but I had a simple idea... database triggers.
Basically I used the following statements:
create trigger note_insert insert on notes for each row update accounts set accounts.date_modified = NEW.date_modified where accounts.id= NEW.parent_id
create trigger note_update after update on notes for each row update accounts set accounts.date_modified = NEW.date_modified where accounts.id= NEW.parent_idNow when some one creates or updates a note the date modified is changed by mysql and I didn't even have to touch the SugarCRM code. I know from searching around that others have made more elaborate changes to SugarCRM for similar purposes but this worked for me so I share it with you.
Are you sure that you have your SQL sytax right?
ReplyDeleteCREATE TRIGGER `note_update` AFTER UPDATE ON `notes` FOR EACH ROW UPDATE `opportunities` SET `date_modified` = NEW.date_modified WHERE `id` = NEW.parent_id;
ReplyDeleteCREATE TRIGGER `note_insert` AFTER INSERT ON `notes` FOR EACH ROW UPDATE `opportunities` SET `date_modified` = NEW.date_modified WHERE `id` = NEW.parent_id;
mysql> SHOW TRIGGERS;
+-------------+--------+-------+-------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-------------+--------+-------+-------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| note_insert | INSERT | notes | UPDATE `opportunities` SET `date_modified` = NEW.date_modified WHERE `id` = NEW.parent_id | AFTER | NULL | | root@localhost | latin1 | latin1_swedish_ci | utf8_general_ci |
| note_update | UPDATE | notes | UPDATE `opportunities` SET `date_modified` = NEW.date_modified WHERE `id` = NEW.parent_id | AFTER | NULL | | root@localhost | latin1 | latin1_swedish_ci | utf8_general_ci |
+-------------+--------+-------+-------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
2 rows in set (0.02 sec)
This way my acquaintance Wesley Virgin's adventure starts with this SHOCKING AND CONTROVERSIAL video.
ReplyDeleteWesley was in the military-and soon after leaving-he revealed hidden, "MIND CONTROL" tactics that the government and others used to get everything they want.
These are the EXACT same SECRETS lots of celebrities (notably those who "come out of nowhere") and the greatest business people used to become wealthy and famous.
You probably know that you utilize only 10% of your brain.
Really, that's because the majority of your BRAINPOWER is UNCONSCIOUS.
Maybe that expression has even occurred INSIDE your own brain... as it did in my good friend Wesley Virgin's brain seven years ago, while driving a non-registered, garbage bucket of a vehicle without a driver's license and $3.20 on his bank card.
"I'm so fed up with going through life paycheck to paycheck! Why can't I become successful?"
You took part in those types of conversations, ain't it so?
Your very own success story is waiting to start. You just need to take a leap of faith in YOURSELF.
CLICK HERE To Find Out How To Become A MILLIONAIRE