Populating Account Address from Contact Address

[hkphooey]

Hi,
One thing that's always puzzled me is that when you import a list of contacts, the address data is copied to the contact, but isn't used to populate the Account Address information.

Once you've completed the import, you have a Contact with an address, but the Address fields in the Account record are empty. However there is a button with which you can copy the Account Address over to the Contact. Which is the wrong way ...

So having just set up a new SugarCRM installation, I'm looking for a way to populate the Address fields of the Account, from the contact. I'm just about to start looking at writing a monster SQL query to run on the database, but before I do that, I was wondering if maybe I've overlooked something and there is an easier way to do this.

Any hidden buttons to copy address from contact to Account perhaps?

Or maybe I should be looking at exporting a list of Contacts with their associated Company names, hacking down the data, and then re-importing it into the Accounts module, in such a way as it will update the data.

Or maybe there's another way ...

[hkphooey]

OK just tried exporting Contact data and then re-importing it as Account Data. I selected Create and Update Accounts. I was hoping that this would merge the address data in, but instead it created new companies.

Next thing to try is to create a simple SQL UPDATE script from the spreadsheet and run that on the database ...

[hkphooey]

OK, so try number two is as follows.

• Export Contact data for entire database
• Trim down columns in Excel until we just have the address fields left, and the company name.
• Construct the query with this data in the form UPDATE accounts SET billing_address_street="24 St. Martin’s Place", billing_address_city="London", billing_address_state="", billing_address_postalcode="W1A 1XP", billing_address_country="UK", date_modified=now(), modified_user_id="1" WHERE name="ABC Company";
• Use autofill to propagate the query down to the other fields.
• Export the sheet to a text file
• Fix formatting issues with regular expression searches
• Run the query.

Well its pretty ugly, and it suffers from the problem that the LAST contact for that company has the wrong address or a blank one, that is the one a company inherits. But this should get 95% of the Addresses. Anyone got a better way before I pull the trigger. Still hoping for that magic button ...

[sugarcane]

Hello,

going forward, you are better off importing the account name and address data into the Accounts module first, and then importing the Contacts information. As long as the Account Name on the Contact csv import matches the account name in the system, then they will automatically link together.

OK just tried exporting Contact data and then re-importing it as Account Data. I selected Create and Update Accounts. I was hoping that this would merge the address data in, but instead it created new companies.

Did you pull the account record ID from Sugar first? The 'Create and Update records' option will only update records if you csv contains the record ID from Sugars.

Take a look at this link. It talks about importing data and also the import order.

[datasponge]

OK, so try number two is as follows.

• Export Contact data for entire database
• Trim down columns in Excel until we just have the address fields left, and the company name.
• Construct the query with this data in the form UPDATE accounts SET billing_address_street="24 St. Martin’s Place", billing_address_city="London", billing_address_state="", billing_address_postalcode="W1A 1XP", billing_address_country="UK", date_modified=now(), modified_user_id="1" WHERE name="ABC Company";
• Use autofill to propagate the query down to the other fields.
• Export the sheet to a text file
• Fix formatting issues with regular expression searches
• Run the query.

Well its pretty ugly, and it suffers from the problem that the LAST contact for that company has the wrong address or a blank one, that is the one a company inherits. But this should get 95% of the Addresses. Anyone got a better way before I pull the trigger. Still hoping for that magic button ...

It would be much cleaner and safer to simply write one query that matches records and only writes if all account address fields are null for that record.

It would look something like this:

UPDATE ((accounts INNER JOIN accounts_contacts ON accounts.id = accounts_contacts.account_id) INNER JOIN contacts ON accounts_contacts.contact_id = contacts.id) SET accounts.billing_address_street = contacts.primary_address_street, accounts.billing_address_city = contacts.primary_address_city, accounts.billing_address_state = contacts.primary_address_state, accounts.billing_address_postal_code = contacts.primary_address_postal_code, accounts.billing_address_country = contacts.primary_address_country WHERE accounts.billing_address_street IS NULL AND accounts.billing_address_city IS NULL AND accounts.billing_address_state IS NULL AND accounts.billing_address_postal_code IS NULL AND accounts.billing_address_country IS NULL;



Of course, this is off the top of my head and I haven't run it, so you need to take all the usual precautions like trying it on a copy of Sugar after backing up all your data tables using mysqldump or similar backup utility, etc.

Also, of course the goal is to put a system into practice like what sugarcane suggests so that you never need to run this type of global query.

But in a pinch, with all proper precautions, you can overcome a lot with this type of powerful query.

Phil

[hkphooey]

going forward, you are better off importing the account name and address data into the Accounts module first, and then importing the Contacts information. As long as the Account Name on the Contact csv import matches the account name in the system, then they will automatically link together.

Yes that makes sense for the future. However if the import was already a done deal, then I didn't have this option. Its a bit fiddly though, having to do the import twice ...

Did you pull the account record ID from Sugar first? The 'Create and Update records' option will only update records if you csv contains the record ID from Sugars.

OK, so that explains the failure. But to get the acccounts.id field, I can't just export from Contacts, I have to run a database query anyway, so that doesn't really save me much time. It's strange that when you import into the contacts module it matches the Account by name, yet when you import directly into the Accounts module, it matches on the ID. Rather inconsistent, but just another one of those things we have to learn to live with I guess.

[hkphooey]

It would be much cleaner and safer to simply write one query that matches records and only writes if all account address fields are null for that record.

Actually I knew that all the Address fields for all the accounts were empty, so in this case it didn't matter. Your query below is very useful, but it still doesn't solve the problem that if there are three contacts for the same company, say, one with the correct address, and one with the wrong address and one with a blank address, there is no way of ensuring the right address goes in there.

I guess what I'm really after is something like the Contact Merge, which will display all the available addresses which contacts below an account hold, and then allow you to 'cherry-pick' which address fields are pulled into the Account address fields.

Anyway, thank you both for your comments. In the end I ran my query -- 18,000 lines of SQL and one quoting mistake. Gah, that was fun to find -- and the address data is now 95% correct.