Can I import direct into the database in SugarCE 5?

[jspencer]

I am working on making a custom module (called Agencies) in the new Sugar CE 5.0 and have discovered that there is currently no import function for it. To work around this and get my system running I'd like to do a direct import from a scrubbed SQL file for around 900+ "items" into this module (too many to do by hand). This leads me to a few questions around the database ID and how all of the modules relate to each other.

Q1) --> What generates the ID field for modules/items? Is it randomly generated by the php code?
A1) --> It is generated by the utils.php file using a random seed based on the date/time of item import/creation.

Q2) --> What is the format if I just want to do it by hand in Excel or something and import using phpmyadmin or similar? Is there any difference in the ID field between the various modules (ie accounts, contacts, users,my custom module) or does the same php code generate ALL ID fields?
A2) --> All ID fields share the same format, so you can use an ID for any type of item (account, contact, etc). If you want to generate just IDs yourself the easiest way is to create a blank Sugar install, then import a bunch of junk into one of the modules (say contacts) and let sugar generate them for you. Then just do a database dump (or export the module list) into excel and use the IDs as needed.

Q3) --> Are there any things that could break if I do the manual import outside of Sugar?
A3) --> Haven't found anything broken so far, just make sure you get to know the database structure and make sql imports into ALL related tables as needed (etc).

This new module is also related to the built in contacts/accounts modules. Once I get the "Agencies" imported into my custom module I will be importing hundreds of contacts/accounts that relate to these agencies. For fun I tried using the import functions in the contacts/accounts modules and it seems there is no way to specify the "related to" id of my custom module.

Q4) --> Am I correct in assuming this means I'll also need to manually import these contacts/accounts/etc into the database using SQL?
A4) --> For now the only way is to do a manual direct SQL import. Other posts on the forum suggest importing into custom modules MAY be rolled out at a later date. Again, get to know the table structure and use excel to set up your import file in the same structure, and use my ID creation answer from Q2 for any needed IDs.

Q5) --> I have created some test items in all of the modules and made sure the relationships work with these items. I also exported these items from the DB and know the correct table structure for the import (and what related tables will need imports), I just want to make sure these "id" fields can be set by hand without breaking anything...
A5) --> Yep, use my answers above and you SHOULD be golden (but no warranty applies to these answers!)

Thanks!

[jspencer]

Since I didn't get any replies yet (I know it was early evening when I posted) I'll give a quick update. I did try searching yesterday but the stupid forum kept filtering the word ID out of my search terms. I did some alternate word searches this morning and just came across this thread:

http://www.sugarcrm.com/forums/showt...t=database+key

Seems to partially answer my first question, so now I guess I'll look in the utils.php (if it still exists in v5.x) and try to use this function to generate a bunch of IDs. If that goes well I'll just randomly give these to my data and do a trial import and see what happens.

Anyone care to comment on possible issues with this brute force approach?

[jspencer]

I pretty much got it working and answered my own questions (nobody has any answers? )

Anyway, I edited my original post with what I think are the answers...

[kuske]

Q2) --> What is the format if I just want to do it by hand in Excel or something and import using phpmyadmin or similar? Is there any difference in the ID field between the various modules (ie accounts, contacts, users,my custom module) or does the same php code generate ALL ID fields?
A2) --> All ID fields share the same format, so you can use an ID for any type of item (account, contact, etc). If you want to generate just IDs yourself the easiest way is to create a blank Sugar install, then import a bunch of junk into one of the modules (say contacts) and let sugar generate them for you. Then just do a database dump (or export the module list) into excel and use the IDs as needed.

You can use any unique string with max. 36 chars as id.
E.g. I used the original 18 chars SalesForce ids directly in SugarCRM.
You can define some number ranges for own imports like
acc-000001-000001 to acc-000001-999999 for the first import of accounts or
con-000003-000001 to con-000003-999999 for the third import of contacts a.s.o
just as you want.

hk

[jspencer]

Thanks for the reply! Ithink that puts the final nail in my ID import clarification coffin

[dogfuel]

I've always just cast an auto incremented value (added to my source) as a string and used that as the id. Or do the same with a natural key.

Any problems this will cause (seems to work fine)?

Thanks

[kuske]

No problem, any unique char key should (must!) work.