Tom,

Currently Centric CRM 4.1 does not support importing leads that are in XML format on the fly but may be available in future versions of the product.

What you will have to do instead is export your leads from the Goldmine CRM in CSV format. Within Centric CRM you then will go to Leads > Import > New Import in order to upload them.

Tell me how this goes. Thanks.

Brandon Antone
Centric CRM

Hi Brandon!

Thanks for the quick reply. I think I wasn't clear in what I was hoping to do. (Sorry.)

We're already aware of the basic importing abilities in the web interface. What we have in the XML file is quite a bit more than basic contact information. (We have history items/records, calendar events & other information.) We're more interested in how to use the Transfer application in order to bring that information into Centric.

In other words, we're not afraid to get under the hood of Centric code - we're just looking for something of a helping hand to get us started. I saw references to using CSV readers, database readers and XML readers, but I'm just not sure how to get that set up. Are there any examples available of how one might do this?

Thanks Brandon. I hope this is more clear. Can you help?

~Tom

Tom,

In short, the Transfer application reads a configuration file which has a reader and writer specified. The Transfer application loads the reader and passes a writer object to it. The reader which is specialized to read from a particular data source (CSV, Centric db etc) reads records and passes each record to the writer which is specialized to write to a particular data source (CSV, Centric db etc).

I know that was boring and not very helpful! For some brownie points I am ready to continue blabbering...

Now that you have an XML full of Gold, we need to somehow store it in your Centric's vault.

To be able to use the Transfer application to work here, you will need a Reader that can read from your XML and Write it to a Centric database. We have an XML reader (reads from a XML file which conforms to Centric's backup file format) and a Centric Database Writer that can be used here.

The biggest challenge would be to convert your XML file to Centric's backup file format.

1. How many records are there in this XML file?
2. Your each record needs to be manually mapped to a record in Centric.

(I know, I agree with you....Centric needs to provide a UI which lets you process your gold file and map it to Centric objects)

You could probably write a script which reads your XML and outputs a Centric XML file. This will need you to understand Centric's backup XML format and get familiar with the object map.

This is how I would proceed once i have a Centric XML file.....I would use the Backup/Restore process available in Centric.

1. Grab Centric CRM v4.1 source from the SVN repository
2. Compile the source and deploy using ant deploy
3. Time to create a shell database. Run ant install.database
4. Time to initialize the object map used by the restore process. Run ant install.syncapi
5. Time to run the script bin/testBackup.bsh. Make sure you edit the db connection info. The script will take the XML backup file as input and call the restore process.
6. If everything goes well you should have your gold in Centric.

Ananth

Hello Ananth!

Thanks so much for your quick reply! This was a big help! I'm still getting used to how Centric works, so please forgive me if I'm missing something obvious in my questions =)

> In short, the Transfer application reads a configuration file
> which has a reader and writer specified. The Transfer
> application loads the reader and passes a writer object to
> it. The reader which is specialized to read from a particular
> data source (CSV, Centric db etc) reads records and
> passes each record to the writer which is specialized to
> write to a particular data source (CSV, Centric db etc).
>
> I know that was boring and not very helpful!

Haha... on the contrary, it was quite helpful! By the way - is this stuff documented somewhere besides these forums? For example, I can't find information on how to generate a backup XML file from a Centric DB. (This would be helpful for testing and comparing to our converted Centric XML file.) I imagine something like this must exist since you have a cool Reader set up for it... but is there a backup function that generates such a file? If so, how do I use it?

> For some brownie points I am ready to continue
> blabbering...

I love it!

> Now that you have an XML full of Gold, we need
> to somehow store it in your Centric's vault.

Right ;)

> To be able to use the Transfer application to work
> here, you will need a Reader that can read from
> your XML and Write it to a Centric database.
> We have an XML reader (reads from a XML file
> which conforms to Centric's backup file format)
> and a Centric Database Writer that can be used
> here.

Great. I saw those.

> The biggest challenge would be to convert your
> XML file to Centric's backup file format.

Right. Not a problem. Sounds like a fun project!

> 1. How many records are there in this XML file?

I'd estimate somewhere between 5-10k contacts.

> 2. Your each record needs to be manually
> mapped to a record in Centric.

Right.

> (I know, I agree with you....Centric needs
> to provide a UI which lets you process your
> gold file and map it to Centric objects)

It would be nice =) Keep plugging away at it! Thanks for your hard work.

> You could probably write a script which reads
> your XML and outputs a Centric XML file. This
> will need you to understand Centric's backup
> XML format and get familiar with the object
> map.

That's our plan. Is Centric's backup XML format documented somewhere? And do you have any tips on how to go about familiarizing myself with the object map? Are you referring to import-mappings.xml? That's a pretty daunting file! =)

> This is how I would proceed once i have a
> Centric XML file.....I would use the
> Backup/Restore process available in Centric.
>
> 1. Grab Centric CRM v4.1 source from the
> SVN repository
> 2. Compile the source and deploy using ant
> deploy
> 3. Time to create a shell database. Run ant
> install.database

I follow you up to this point.

> 4. Time to initialize the object map used by
> the restore process. Run ant install.syncapi

Could you say more about what this does?

> 5. Time to run the script bin/testBackup.bsh.
> Make sure you edit the db connection info.
> The script will take the XML backup file as
> input and call the restore process.
> 6. If everything goes well you should have
> your gold in Centric.

I think I understand these last couple things as well.

Thanks for pointing me in the right direction, Ananth. This was very helpful. Any further guidance is appreciated as well!

~Tom

>> Haha... on the contrary, it was quite helpful! By the way - is this stuff documented
>> somewhere besides these forums? For example, I can't find information on how to generate
>> a backup XML file from a Centric DB. (This would be helpful for testing and comparing to our
>> converted Centric XML file.) I imagine something like this must exist since you have a cool
>> Reader set up for it... but is there a backup function that generates such a file? If so, how
>> do I use it?

There is a Backup/Restore tool that we have been working on that has not been announced anywhere. Also there is very little documentation and I'll provide some documentation on the wiki that should help all the interested parties.

bin/import-db.sh - reads data out of a centric database and feeds it to a Centric XML writer
bin/testBackup.sh - loads data out of a centric XML format backup file and writes to a centric database

You could use import-db.sh to read data out of your sample centric installation and see what the format looks like. Run this from your centric source working directory.

My import-db.sh looks like this.....

ant import.database -Darg1=jdbc:postgresql://127.0.0.1:5432/f-20070314-actionplans -Darg2=postgres -Darg3=postgres -Darg4=database-backup.xml

Now you should have a database-backup.xml file in your current directory.

In short each record in the database is represented by the following XML (here it is the user object):

<dataRecord action="insert" name="user" shareKey="false">
<dataField alias="guid" name="id">1</dataField>
<dataField name="username">ananth</dataField>
<dataField name="password">792fef441691aa41135a15c1478a5ee4</dataField>
<dataField name="encryptedPassword">792fef441691aa41135a15c1478a5ee4</dataField>
<dataField name="lastLogin">2007-06-22 14:28:02.324</dataField>
<dataField name="ip">0:0:0:0:0:0:0:1%0</dataField>
<dataField name="timeZone">America/New_York</dataField>
<dataField name="startofday"></dataField>
<dataField name="endOfDay"></dataField>
<dataField name="expires"></dataField>
<dataField name="enabled">true</dataField>
<dataField name="entered">2007-03-29 16:27:00.396</dataField>
<dataField name="modified">2007-03-29 16:27:00.396</dataField>
<dataField name="currency">USD</dataField>
<dataField name="language">en_US</dataField>
<dataField name="webdavPassword">e8613b3e0d8f1581eec4a5fa17fac7ff</dataField>
<dataField name="hidden">false</dataField>
<dataField name="hasWebdavAccess">false</dataField>
<dataField name="hasHttpApiAccess">false</dataField>
<dataField name="addContact">false</dataField>
</dataRecord>
 
The "name" of the dataRecord corresponds to a particular object in Centric. Yes, import-mappings.xml is the file which defines each object is Centric.

The restore process creates a sync client record. When there is a dataField that contains an alias "guid", this tells the server that this client will send more records and that it should map his records to the records that are inserted on the server.

So the id=1 for 'user' for this client will be stored at the server in a client map. So when your xml has any dataRecords with dataFields that say lookup="user" then the server will use the map to lookup what "user" record the client's sent user id maps to.

The server populates the org.aspcfs.modules.admin.base.User object from this xml and calls the "insert" method on the User object.

If you do a select * from sync_table, you should be able to see a list of xml objects and the Class each one is mapped to. Using this information and the above xml format if you transform your gold xml file to a centric xml file, then you can use the restore process.

Also see how the org.aspcfs.apps.transfer.reader.cfsdatabasereader.CFSDatabaseReader and org.aspcfs.apps.transfer.writer.CFSXMLWriter work in combination (import-db.sh) to load the import-mappings.xml and use the PropertyMap to output a centric format xml file.

Also ant install.syncapi reads the import-mappings.xml file and initializes the object map (sync_table) which is used by the restore process.

Hope this helps...

Ananth

Hello Ananth,

Thanks so much for another very helpful reply! I did what you suggested. (Or at least I think I did.) However, I am having some trouble. Here is a snippet of my XML file that I'm trying to import into Centric:

<aspcfs>
<cfsdata>
<dataRecord action="insert" name="account" shareKey="false">
<dataField alias="guid" name="id">1</dataField>
<dataField name="name"></dataField>
<dataField name="accountNumber"></dataField>
<dataField name="url"></dataField>
...snip...
<dataField name="insertPrimaryContact">false</dataField>
</dataRecord>

<dataRecord action="insert" name="contact" shareKey="false">
<dataField alias="guid" name="id">1</dataField>
<dataField name="userID"></dataField>
<dataField name="orgID" valueLookup="account">1</dataField>
<dataField name="company"></dataField>
<dataField name="title"></dataField>
...snip...
<dataField name="noFax">false</dataField></dataRecord>
</cfsdata>
</aspcfs>

As you can see, I'm trying to insert an account (with id 1) and then a contact associated with that account (also id 1). However, whenever I load the data, the contact's "orgId" field is always NULL. Rather, I'd like it to use the previously entered account's id.

I tried setting it to "1" using a lookup (as above). I also tried using $C and just hard-coding a "1", but none of it seems to be working.

I noticed the following snippet in the logs:
Transaction-> Adding transaction item
TransactionItem-> New: org.aspcfs.modules.contacts.base.Contact
XMLUtils-> setGuid(1) **IGNORED
XMLUtils-> setUserID(null)
XMLUtils-> setOrgID(1) **IGNORED
XMLUtils-> setCompany(null)
XMLUtils-> setTitle(null)

It seems like the ID fields are getting ignored (which lines up with what I see in the DB).

Could you help me understand what I'm doing incorrectly? I feel like I'm close but just missing something simple.

Thanks again for your help!

~Tom

Oh gosh. Nevermind... I just realized I had a cAsE mIsMaTcH! =P

(orgID should have been orgId)

Good to hear from you...

:). It should be userId, contactId, orgId and so on.....

d is always lower case. I can't think of an id attribute in Centric which is defined with d in uppercase.

If you've got an XML schema doc for your data then I can likely build you an integration pack in another open source product called jitterbit. I can then ping you a jitterpak and you can update the source, target and click a button..........I'm guessing it will be significantly less time consuming than picking through code.

Having said that you may well have already solved this one.

Vince and Ananth,

Thanks to both of you for your help. Yes, we did get the Goldmine->Centric import working. It took a while, but in the process I became pretty familiar with Centric's DB schema. This is good, because we'll probably be developing a couple modules of our own over the next months and the experience was very helpful.

Oh btw, Ananth, I found what I think is a typo in the import-mappings.xml. Under "OrganizationAddress" there is an "email" field. Looks like it might have been a copy-paste error from OrganizationEmail below.

~Tom