Link 2 records created by Import

[madhrishi]

Hi,

I have two modules. Module1 [primary] and Module2 [related] with one - many relation ship.
Records are created in both the modules through import. How do i link them ?

Module2, the related module, has a relate field, which links to module1......

How should i import this field, so that the corresponding records gets linked..... What should the import file [.csv] contain?

Please help....

Thanks,
Madhu

[enrashid]

Use the name field to link them up. Whenever you create a module, one of the inherent fields that automatically gets created is the name field (which is a text field),

So, Import records form Module 1. Then ensure that the .csv for records for Module 2 has the Module 1 name field as on of the fields in the file.

Now, the assumption is that in Module 2, you have created a relate field for Module 1. Also, you have to ensure that in Module 1, all the values in the name field are unique. If this is not the case, let me know and I can guide you on how to handle this.

[madhrishi]

Thanks Enrashid,

The one to many relationship i have is that between Contacts and a Custom module... When i try to import, [i tried with both name and id], i am getting the following error.

Want only a single row Query Failed:SELECT id FROM contacts WHERE name = '6a044f0f-b686-19d4-3316-49c50e8ebec6' AND deleted != 1 LIMIT 0,1::MySQL error 1054: Unknown column 'name' in 'where clause

I found this BUG : 27955

http://www.sugarcrm.com/crm/sugarbug...g_number=27955

Am i facing the same issue? Is this not resolved.. any solution ??

[enrashid]

The other option while we are waiting for the patch release is to import your records directly into the database tables. We have done this with no integrity issues. You will need to come up with unique ids for the various id fields.

Let me know if this way of importing is an option for you and I can guide you through the process.

[madhrishi]

that will really be helpful... but i dont have much understanding about database structures or how they work... can you pls guide me?

[enrashid]

I am just trying to thing of the easiest way that you can do this that is also understandable considering you do not have a complete knowledge of the database. Ok, lets begin:

Contacts module will have a table called conacts in the database. It may have other tables such as contacts_cstm or contacts_audit, but ignore these.

Your module, say it is called YourModule has probably been created in Module Builder and then promoted to Studio. You had to create a package with a key. Let say your key was Key and so your custom module in the database will be key_yourmodule

Now both contacts table and key_yourmodule table have a special internal field mantained by Sugar called id. This field has to be unique withing the table. When creating a record within the GUI, the id will be created for you or when you import contacts, again, this id will be created for you.

Lastly there is a middle table that establishes the 1-M relationship between contacts table and key_yourmodule table. It too will consist of and id field, but it will also have two other important fields: the id field from the contact table (know as ida - I am using shortform here) and the id field from the key_yourmodule table (known as idb).

So, the way I would proceed is to see if you can import data into your module without mapping to the name field of the Contact. In other words ignore the contact table completely and just focus on importing the rest of the fields for your custom module. If you are successful, then the rest of the solution is a lot easier to explain using SQL and you are done.

Let me know how this turns out and you can send a private message and I can walk you through the rest.

[madhrishi]

I was able to follow most of what you said. I can see all the ids you are talking about. However, i cant follow this statement

"So, the way I would proceed is to see if you can import data into your module without mapping to the name field of the Contact. In other words ignore the contact table completely and just focus on importing the rest of the fields for your custom module. If you are successful, then the rest of the solution is a lot easier to explain using SQL and you are done."

You mean i need to import all records in to the custom module first and then we link it with contacts using SQL query?

[enrashid]

Correct, I want you to upload all your data to your custom module and completely ignore the Contact table. Then afterwards we will use SQL to build the 1-M relationship between Contacts and Your Module.

When you are successful importing the data independ of the Contact Module, give me a should back. I just want to see if you are successful with this. If you are, then the next step is to important again, but this time I just want you to put the first name and last name of the contact is any empty field that you have. Do it like this Joe:::Smith and put this in some field in your custom module. If you do not have a field you can use, create one and then you can always delete it afterwards.

The idea is when we want to form the relationship between Contacts and Your Module, we will use the Joe:::Smith in that field in your Custom Module to help us write our SQL.

*** Now what is important is that are they two Joe Smith contacts in your Contact database; in other words, do all Contacts have a unique first-last name combination. If this is not the case then even if the import tool worked, it would try to associate all your custom records to more than one Contact record with the same name. ***

I hope this is clear. Keep me posted. But I really hope you can import the data independant of Contact ... if not there are other ways

[madhrishi]

I was able to upload all records into the custom module without 1-M relationship. I think, so far, first name and last name combination is unique... assuming this, what should i do next?
I dont understand creating a field "Joe:::Smith"

You mean, a text field?

Hmm... you were talking about importing custom module details again with this text field right? Does that mean... the csv file i use, should have a column with values "firstname:::lastname" ?

What is the significance of ':::' between first and last name...?

Sorry, Am actually clueless...

[enrashid]

Ok, this is very good that you can import your records. The firstname:::lastname needs to be part of your .csv file and the next time you import, you chance choose the create/update radio button and import the same data, but with this new column that stores firstname:::lastname.

Now, this strange column needs to be stored someplace temporarily in your custom module. Any text field with sufficient length will do. So, you many need to create one temporarily.

Here is the thinking behind brining in this firstname:::lastname column:

When you do re-import your data again, we need a way to linkup the Contact record with name Joe Smith with your record(s) from your custom module. This is where the Joe:::Smith comes in. The reason for the ::: is because there could be a name like Ann Marie Mason, but you would not know if it was Ann Marie:::Mason or Ann:::Marie Mason. And if were to perform the next tast of linking your records up in the custom module to the contact and the contact had two Contacts: firstname = Ann Marie lastname = Mason and the other one firstname = Ann lastname = Marie Mason, there would be ambiguity. As I am writing this explanation, it might be better to have two columns in your .csv, one for firstname and one for lastname. You will then have to have two text fields of sufficient length created or available in your custom module.

There should be a middle relationship table created by Sugar when you created the relationship 1-M in Studio. Something like contacts_<key>_<your module>. For sake of moving on, I will use c_k_c for short.

This table has the following fields (from memory):

id := all tables have an id field which has to be unique
ida := unique id coming from Contact table
idb := unique id coming from Your Cust Module
deleted := 0 or 1

Run the following sql statement. I will be giving ORACLE syntax, but translate it to your database:

UPDATE INTO c_k_c (id, ida, idb, deleted)
SELECT
rownum,
a.id,
b.id,
0
FROM
Contacts a,
<cust table> b,
WHERE
a.firstname = b.firstname AND
a.lastname = b.lastname

That is it and you should be able to see your custom records in the subpanel of Contacts.

NOTES:
rownum is just a function to generated unque sequences. Use whatever you want here and might be good to concatenate a date value with the sequence number just to make it really distinct. But as long as it is unique.

Cannot remember if deleted field is text or number, '0' vs just 0.

I was able to upload all records into the custom module without 1-M relationship. I think, so far, first name and last name combination is unique... assuming this, what should i do next?
I dont understand creating a field "Joe:::Smith"

You mean, a text field?

Hmm... you were talking about importing custom module details again with this text field right? Does that mean... the csv file i use, should have a column with values "firstname:::lastname" ?

What is the significance of ':::' between first and last name...?

Sorry, Am actually clueless...