Details about this thread
  • State Suggested Answer
  • Replies 10 replies
  • Answers 3 answers
  • Subscribers 316 subscribers
  • Views 5341 views
  • Users 0 members are here
Related content from around SugarClub

Integrate Sugar with external database

Rene Ryhner

Hi everyone,

I need to connect Sugar CRM (v9) with an external database. Specifically, I want to react to the change of a field on the Contacts Module. Information of the changed record should then be transferred to another database. The remote database is only accessible via DBlink or direct login. Sugar DB and the remote DB are Oracle 19 DB's.

First thought was a trigger on the database (field level - Oracle PLSQL). But I would like to avoid that. I also don't really want to set up a scheduler.

Were there other approaches or best practice to reach this. Any ideas were very appreciated.

Thank you in advance!
Rene

Parents
  • 0

    We chose to do a 2 step approach:

    An after save logic hook puts the changes into a staging table when the record is changed, then a scheduler reads the recently staged records with "staged" in the status from Sugar and makes the changes in Oracle, when the Oracle update comes back successful then the staged record is changed to status "synced", if there are issues it is reported to the developer that there is a problem and the status is changed to "error".  This allows us to track changes and track the sync. to better handle any errors.

    Of course you need to have 1:1 relationship between Sugar Contact and Oracle Contact, and make Sugar the master.
    Things can get really messy if you allow your users to create/modify Contacts in both systems.

    I highly recommend against any 2-way sync.

    FrancescaS

Reply
  • 0

    We chose to do a 2 step approach:

    An after save logic hook puts the changes into a staging table when the record is changed, then a scheduler reads the recently staged records with "staged" in the status from Sugar and makes the changes in Oracle, when the Oracle update comes back successful then the staged record is changed to status "synced", if there are issues it is reported to the developer that there is a problem and the status is changed to "error".  This allows us to track changes and track the sync. to better handle any errors.

    Of course you need to have 1:1 relationship between Sugar Contact and Oracle Contact, and make Sugar the master.
    Things can get really messy if you allow your users to create/modify Contacts in both systems.

    I highly recommend against any 2-way sync.

    FrancescaS

Children
  • 0 Sugar Employee Badge in reply to Francesca Shiekh

    I would second, strongly, the point of avoiding 2 - way sync. 

    Regardless of which systems are in play, bidirectional synchronization is a complex topic that can easily cause many problems and potentially lead to data loss.

  • 0 in reply to Francesca Shiekh

    Hi Francesca,

    Thank you very much for your answer. 

    The process at the moment looks like this. The sales admin set a "role"-Field on the contact module to initiate a manual workflow to an admin person. This person creates a new User on a web application (e.g. named as "MVD" ). Currently the "role" Update on contacts module triggers a SUGAR BPM which creates a Task and send an Email to the admin person. This person create the user and close the Task after. So far so good.

    My task is to automate the whole process after updating the "role"-field. In this case, a third application (data pool) play a role in addition to CRM and MVD.

    With your mentioned approach (suggested staging table), i'll keep the process from updating the contact to create a Task record. Collecting the contact data for "MVD" I would do with a before_save hook on the task module.

    As all our customer applications will manly populated from the mentioned data pool (CRM -> Accounts), there exists a process table. So the before_save hook writes a process record into the data pool table (realized with the above help from )

    From there a DB Procedure create the user in MVD. After successfully processing, the same procedure closes the Task record out from the data pool(via REST). 

    The contacts (MVD-Users) will be administrated only out from CRM. Admin people can't login to MVD (anymore) nor to the data pool

    I hope this is understandable and also looks quiet similar to what you have suggested from a CRM point of view.

    Rene