Q&A: Synchronizing SugarCRM Applications, 10-10-06

[Dan]

During our live Jittercamp webinar "Synchronizing SugarCRM & Database Apps" users were able to ask questions about the integration being demonstrated by Ilan. All questions and answers during this session are included below. If you would like to view an archived version of this webinar, please visit our Jittercamp section.

All questions related to this webinar should be posted to this topic.

How do you handle database errors?

Database errors are handled in the following ways:

1. If a database is a source or target and Jitterbit cannot access the database it will fail an integration operation. The failure could trigger other operations or send email notifications. Failure to connect is typically due to databases not being available, not authenticating, or network problems in accessing the database server. The log should provide the appropriate message.

2. After the transformation is defined, if a database is a source or target and the database structure is changed (e.g. the table names are modified or fields have been removed) then the operation will fail. The log should provide the appropriate message.

3. If a database is a target and an update or insert could not be performed then a warning message is placed in the log. This will be the ODBC error returned by the driver used. Typical example are "tried to insert Null value in Not Null field", or "character cannot be inserted into Numberic field". These errors will only affect records that could not be processed, but records that succeeded will be saved.

4. If using a DBLookup function in the formula builder and the call has an error, it will generate a warning – even if the error is due to connection to the database. If users need to force a failure in the entire operation then they need to detect the error using an If statement and then RaiseError for certain conditions.


Can Jitterbit sync to LDAP? (i.e. sync contact details between Sugar and Active Directory?)

LDAP can sync between Sugar and LDAP. Documentation on how to use LDAP functions are provided in the following URL: http://www.jitterbit.com/PhpBB/viewtopic.php?t=117


How can the sync process be automated?

Automation of a sync process can occur by placing the initial operation on a schedule so that the sync can occur within a given interval. Alternatively, external application events can cause the sync to happen by calling Jitterbit directly. Invoking an operation from a 3rd party application is described in the following URL: http://www.jitterbit.com/PhpBB/viewtopic.php?t=81


Can the transformation scripts be edited in an external editor, for integration with an external source code control system?

Yes. In the transformation Toolkit (available through the toolbar), select Export Mappings. This writes the transformations to an external file. You can edit that file and then Import Mappings.


What about when the same field is updated between syncs on both Sugar and AccountsDB?

That is not covered in this Jitterpak. This requires a conflict resolution. Typical conflict resolution algorithms are:

1. Latest timestamp of record modified_date. This may work in some scenarios, however, there is no guarantee that the particular field in question was modified last in that record.

2. Changes in one system are favored over another. If it is clear in the business process which system should be more current then this will work.

3. Manual Intervention. This is the safest bet and requires that the Jitterpak test for the conflict before performing an update to the target system.

Any one of these can be incorporated into the Jitterpak fairly easily.


Does the sync_data table continually grow or is it purged after the sync has occurred? When a data structure is normalized and there are timestamps on each object (i.e. phone table, address table) how are changes tracked and how are multiple values (i.e. home number, work number) de-normalized?

In the Jitterpak it continues to grow. Anyone deploying to production should create a separate operation that will purge the sync_control_tab on an internal that makes sense. When deleting from the sync_data_tab only delete data records that have been used 2 or more synchronizations ago. If you delete the most recent synchronized data then the system will not know that the data was synchronized or what the previous value was.

Deleting data from the database can be done by a simple operation that has no source and the database target. Use a DBLookup function with the appropriate “delete from …” statement.


How are you dealing with error recovery? If you are doing a time interval based synchronization how do you deal with error cases & recovery?

The architecture of the solution allows for errors e.g.

1. If a record is identified as modified or created and was previously processed it is rejected by the condition in the sync_control_data target

2.If Jitterbit server is turned off during the synchronization process and part of the synchronization has not completed, all sync_control_tab records not synchronized will be processed in the next sync. The records are updated in the target system and the sync_control_tab record are updated in the same transaction – it either synchronized or marked as not processed for the next sync to pickup.

3. In the case of Synchronization, it will be important to keep a backup of the Sync DB. Unlike the Jitterbit batabase that can be restored from Jitterpaks, this needs to be treated like a product system.


Could you use Jitterbit to sync two SugarCRM installs at different versions?

Yes. I would highly recommend using the Web Service interface for this as it will save significant effort in mapping as you should only require one transformation to cover all Sugar objects vs one for each database table.


What if you need to format data before it is synchronized in other DB? For example Date, booleans, etc Is there a way visually to see what field is mapped to what on either databases?

It’s just like any other transformation – you can apply transformation functions using the Formula Builder. You would see the one or more source fields that were used in the formula in the tree.


Can you have multiple target databases you wish to sync with? Multiple target db question 2 – I have need of multiple instances of sugar which must sync from 1 master db instance. I want to control the specific data for each target db. Is it possible to build a Jitterpak for this purpose?

You would need to make a modification for synchronizing multiple system. The sync_control_table would need an additional field systems_synchronized which would values such as “sugar 1 to sugar 2”, “sugar 1 to sugar 3”. When you create data in the SyncDB you would need to populate that field from the transformation appropriately. When you update the target systems from the SyncDB you would add the appropriate filter so that only records pertaining to the appropriate sync would be used.


Can you speak briefly about sending data into Sugar (one-way) from a custom web application and the applicability of Jitterbit to this or not?

I would definitely use Jitterbit for this. You would do something similar to the first simple transformation demonstrated. Obviously, in this case Sugar would be the target. I would also recommend using the Sugar Web Services interface for this type of integration.


Does Jitterbit provide a Web Admin console?

Jitterbit provides an Admin console in the Jitterbit Integration Environment. Just click View (menu) and select Administration Console.


For Linux, I only saw RPM packages available. Will Debian/Ubuntu packages be available at some point, or possibly binary source we could compile for Ubuntu?

There are developers in the community helping us with this. If they are not able to contribute their efforts then we will do it.


Can I download a video+audio of this webinar after it finishes to share with my colleagues here?

Yes. You can find all archived webinars in our [url=http://www.jitterbit.com/Support/jittercamp.php]Jittercamp section
[/url]

[schurik]

Hello!

It's really difficult to make synchronisation with SugarCRM (via Web Service)!
But I'm sure it is possible.

I have a billing system for all of my clients. Every client has a SugarCRM installation and I need to synchronize one db-source with many clients SugarCRM installations (Contacts, Accounts and so on).

1. It would be very helpful if anybody has hints what is the better way doing it with jitterbit.

2. My question is whether and how it is possible to create automatically from one "dummy template" all the needed operations and transformations for a new Sugar Web Service if I get a new client.

Sorry for my bad English %)

Thank you for your help!

[schurik]

You said, you'll publish the pack you showed during the webinars in the web, but i can not find it. Where can I download it?

[Dan]

I apologize, we did not make the Jitterpak for this webinar available. However, you can find a number of Jitterpaks for SugarCRM in the Jitterpaks & Plugins forum. Specifically you may want to look in this thread: http://www.jitterbit.com/PhpBB/viewtopic.php?t=72

[johnwilliamsjr]

thank you some much i found the link for the jitterpacks extremely useful thanks again from john and the gang from uk with love
http://www.goarticles.com/cgi-bin/showa.cgi?C=2633910 http://www.squidoo.com/hemorrhoidmiracley

[gabossy]

What is the best open source to use for hiring website? I need to know what open source I can use to do a website for hiring people. I prefer to have it either perl or php. Anyone knows?