Write to Sugar DB Tables from outside source?

[Morkeleb]

I know that it's physically possible to write to the db from an outside source, but what I wanted to know is if we do, how does the 'id' field get assigned within sugar? For instance, if I wrote data to create a new lead, how would it get the 'id' or would it automatically get one when we converted it to a contact?

For those out there who are wondering, we have an application here called Email2DB, which works as an excellent email parser and we parse information from the body of the email from several sources, in several different formats. We write the collected data to a mysql database, which we can in-turn then export and then import into sugar through the import option, but I'd never actually asked if there was a way to import into a lead via direct db write, or if not having an 'id' was important?

If this is possible, then my email parse problems are settled! Unfortunately fo anyone else looking to do this, the email2db only has a 30 day trial, whereupon you would have to purchase a one year license, which is about $500 and change, but well worth the purchase from our point of view. I've parsed over 17,000 emails in the last four months, with unique entries.

Randy
Systems Administrator at a reputable Trucking Company.

[Angel]

If you look in the file <sugar root>/include/utils.php, you will find a function called create_guid. This function can be used to generate that value in conformance with what Sugar expects.

The biggest problem outside of that is making sure you understand the relationships of the different tables and what conditions cause certain things to be populated.

[Morkeleb]

Well, as long as I'm not modifying the field data and only writing it to an existing db field, shouldn't that take care of the worrying about the relationships? What I'm looking at doing is a direct write to the Leads table. Until it's converted, there is no relationship, correct? BTW, we're not using the 'accounts' option. This is strictly a Recruiting Database utility for us, so we're using the Lead, Contacts and Activities, mostly.

[Angel]

You should be pretty safe in your case. Most of the gotchas come into play when you start linking calls to a contact and the like.

[Morkeleb]

Okay,

Here's an update to what's going on with this. You don't have to mess with the create_guid function at all. All you have to do is make sure that the id that is written to the 'leads' table is unique in the database. I tested this by doing the following:

1. Opened phpMyAdmin and made up a unique ID (In this case, LCT01102007001), which is based on our company name, MMDDYYYY and a 3 digit numeric identifier for that date. We'll probably go with a four or five digit in the final, but it doesn't matter. As long as it's unique.

2. Opened the lead in Leads tab in SugarCRM and verified that it was entered.

3. Converted the lead to a contact with no errors.

4. Opened the contact and noted the unique 'SugarCRM' id assigned to the contact (I have the ID on the display screen for ease of referencing back, if need be.

So, it would appear that if you need to do a direct DB write to SugarCRM, Leads is your best place to do it. We have kept the fields in Leads to default, so that I don't have to mess with the second table, leads_cstm, but I imagine it would not be a problem to write to that as well through Email2DB if we expand later. Thank you so much for all of your help. We now have a fully functional Driver Recruiting Database, thanks to Open Source SugarCRM and will most likely be looking at the commmercial solution for our sales department. This sells it for our Company President.

Randy Stevenson

[Angel]

Okay,

Here's an update to what's going on with this. You don't have to mess with the create_guid function at all. All you have to do is make sure that the id that is written to the 'leads' table is unique in the database. I tested this by doing the following:

In general terms, you are correct. The ID value is used to generate a value that is supposed to be unique and hence, provide a key for the records in the table.

There is a little more to it though. I would recommend you give this thread a look before you go on to create your own IDs.

http://www.sugarcrm.com/forums/showt...highlight=guid

[Morkeleb]

Thanks for that. I guess I'd have to go a bit further to explain that the 'Leads' section of our Recruiting database is used solely for working with recruits until they are either accepted or denied. At that time they get converted to Contacts and marked as either accepted or denied upon conversion, as well as marked as email opt out or not for marketing later. We do not use the Accounts section of Sugar for this db, nor do I see any reason that we would want to. In addition, we will not be merging it with any other database, except through a mysql export and then reimport. Any imports into this database, will of course, be done through the Import Contact field. Since the guid in sugar is hexadecimal in nature and form, the format that we're using will never be duplicated, on the off chance that we created leads another way.

This database also is isolated from the outside world by a firewall and shall remain that way as well. I read the thread and the issues discussed do not cover our situation.

Thanks again for all the insight.

Randy

[Morkeleb]

In any case, I was able to create a file called guid.php, which does nothing but create a unique ID for Leads. Now to get procmail to grab the information that it puts out and automatically change the subject line of the email for the second round through email2db.