Importing Notes: Want to associate with Account

[naisan]

I'm importing notes from a CSV file. in my CSV file I've mapped the sugarCRM account_ID (via a dump from the sugardb) to each note, and when I import, I am mapping the matching 36-char value I took from the sugar account table to the AccountID Sugar db field shown in the import screen. . .

But I get no association?

I've also noted that the sugar studio shows notes and accounts related via the account_notes one-to-many relationship, but no corresponding table exists in my db?

What am I missing here. sugarcrm CE Version 5.2.0f w/ mysql 5.1.34-community on IIS w/ php5. ..

Thx,

naisan

[naisan]

I figured this out myself, so here it is for those who need some help:

* I used OpenOffice calc for manipulating csv files - it deals well with linefeeds / newlines in the notes fields of CSV files, and csv output is always in quotes, so that makes things easy.
* I downloaded the accounts table and took the 36-char accountID and matched it using vlookup function in openoffice to each of my notes
* created a column called Parent Type and filled it with Account - note that during the import, it recognized this and brought back a drop-down with all the possible parent IDs during the matching phase of the import - it seems to be important to Sugar that you actually name the columns exactly in some cases - i.e. name the csv column "Parent Type" if you want to ensure it recognizes. This may be superstitious, but if you don't do this, you won't see a dropdown, even if you map it in the mapping screen in sugar.
* mapped the Account ID from the CSV file to parentID in the sugarcrm field and the parenttype in the csv with parenttype in the sugarcrm fields.

And I got what I wanted.

[tall guy]

I was having the same problem associating notes with accounts. I tried doing what you described and after the mapping screen, it said the import was successful, but it didn't list out the successfully uploaded records; plus, I couldn't find the uploaded records anywhere (not in Accounts or Notes).

Any suggestions?

[datasponge]

I'm the wrong guy for help on the Sugar Import features - I always use the SQL features to write directly to the tables and create my own mapping.

But insight into the table here may help.

The SugarCRM notes table acts a little differently than other relationships in that it stores the id's directly in its own table instead of a separate relationship table.

It contains three fields used for relationships:
parent_type
parent_id
contact_id

I've found that you have to set parent_type to 'Accounts' for Sugar to recognize the relationship in addition to setting the parent_id to the account_id (as mentioned by naisan). You can set the contact_id directly to relate to both.

I find that looking for the imported records directly in the database using a query can tell me whether it's a relationship issue or a more basic import issue (i.e. whether the records were actually imported at all).

Phil