Manual Import into MySQL for Custom Module

[Yardbarker]

Hey team,

I am in a bit of a bind. We are on an eval istance of SugarCRM and are off to a great start as one of our employees used to work for Sugar. Using module builder we created a custom module similar to accounts to help segment certain data from our sales pipeline.

We're going to buy shortly but need to do a sizeable import into the custom module. Unfortunately I see that the import tool is not available for custom modules and will not be available until the 5.1 release. We really can't wait that long and were hoping to to manually import from a CSV file into the database. One of our engineers is on board with this but wanted to get further instruction on the best way to do this.

Any help is appreciated.

Thanks!

[julian]

Hey Yardbarker,

You'll need a write a basic script (doesn't matter which programming language you use) to insert the data into mysql. Let's say you create a custom module named Subscribers with three fields (first name, last name, lucky number). Assuming you use pkg as your package name and Subscribers as your module name, Sugar will create two tables when you deploy the custom module:

pkg_subscribers will contain the subscriber data
pkg_subscribers_audit will contain the audit log for subscriber data (a historical look at who changed subscriber data-- this can be left out of your initial import)

So... we'll be focusing on the pkg_subscribers table from now on. Its structure looks roughly like this:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | varchar(36)  |      | PRI |         |       |
| name             | varchar(255) |      |     |         |       |
| date_entered     | datetime     | YES  |     | NULL    |       |
| date_modified    | datetime     | YES  |     | NULL    |       |
| modified_user_id | varchar(36)  | YES  |     | NULL    |       |
| created_by       | varchar(36)  | YES  |     | NULL    |       |
| description      | text         | YES  |     | NULL    |       |
| deleted          | tinyint(1)   | YES  |     | 0       |       |
| team_id          | varchar(36)  | YES  |     | NULL    |       |
| assigned_user_id | varchar(36)  | YES  |     | NULL    |       |
| first_name       | varchar(50)  | YES  |     | NULL    |       |
| last_name        | varchar(50)  | YES  |     | NULL    |       |
| lucky_number     | int(11)      | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

id is in the internal, unique ID that Sugar uses. It can be anything, but we use the create_guid() function in ./include/utils.php to create our GUIDs. They look like this: c23c5a42-e3f7-508d-cc7d-4892fae4f861

name can be ignored-- it came with the module we built, but we're using first_name and last_name instead. Either way, this is just a simple text field.

date_entered is the date/time the record was created. It expects a timestamp in the format YYYY-MM-DD HH:MM:SS.

date_modified is the date/time the record was last modified. Same timestamp format as above.

modified_user_id should be the GUID corresponding to the User record who last modified this record. If you are not keeping track of which users last modified records (in your original data), you can set these all to the same value. In all Sugar installations, the GUID of the admin user is 1.

created_by -- same as above. The GUID of the User who created the record.

description can also be ignored, unless you're using this in your custom module. It will show up in Sugar as the Description field.

deleted is a flag denoting whether or not the record has been deleted. I'm guessing you'll want to set this to 0 for all the records you're importing.

team_id contains the GUID corresponding to the Team that has access to view this record. If you would like everybody to see this record, set team_id to 1 (the Global team). If you would like to assign this record to a specific team, go look up the Teams you may've created (login to Sugar as an admin, click the Admin link, then Team Management).

assigned_user_id is similar to the team_id above. This contains the GUID of the User who owns this record. If you would like admin to own these records, set this field to 1.

Now for the custom fields we created ourselves...

first_name and last_name are simple text fields. Just insert the data from your CSV straight into these columns.

lucky_number is an integer field-- make sure you only insert numbers into this field, or you may run into MySQL errors.

I hope this gives you a decent idea of how to perform the import. You'll probably have to try it a few times, tweaking your script as you go. Good luck!