22 Nov Update from QuickBooks into Legrand: ;address Mapping Issue
Update from QuickBooks into Legrand: Address Mapping Issue
QuickBooks Link
When first starting with Legrand, the import from QuickBooks is a quick and easy way to get the contacts set up and the initial QuickBooks links established. The only issue is that in QuickBooks the address fields often include the company and contact information. When Legrand imports this information, there is no way to consistently eliminate this information from the QuickBooks data. For this reason, the following few steps permit quickly and easily updating the address information so that all Legrand contacts, whether from QuickBooks or not, will consistently only have the street address in the address fields in Legrand.
Note: This works best if no other database work has been done for the day due to the ease of completing a QuickSearch on the last updated field on the audit tab.
The first step is to choose File > Accounting > Link to QuickBooks to update Legrand with the most current information from QuickBooks.
Be sure to click on the “View Settings” button to confirm the “rules” you wish to follow during the integration.
Create List of QuickBooks Companies
The second step is to go to the audit tab for Companies and QuickSearch on the updated date field for the current date. This should provide a list of the companies that were updated.
Right click on the list to select all then right click again to export the list to Excel.
Note: For ease of future reference, while here you should consider another right click and assign a keyword for these companies to make reporting on the QuickBooks linked companies in the future.
Manipulate Export in Excel
In Excel, highlight the entire spreadsheet by clicking in the upper left corner.
Choose Data > Sort > Sort by street3 (The goal when this entire process is complete is that all the true street addresses will be in street 3)
For all the “blank” street3 rows, copy and paste street2
For the remainder, scroll through the list and for any address that have part in street2 and part in street3, copy and paste street2 into street3 so the complete address is in the street 3 column
Change the column heading from street3 to street1 then delete all the columns except the company, city, and street 1 (previously street3) columns.
Save the file as a CSV
Note: If the city is blank, even if it is blank in Legrand, it will appear on the error log.
Import CSV into Legrand
File > Import > Companies
Choose the file and map the fields
Choose to update duplicate records, click on next, next and then finish
Once you click on finish, if the list of customers is quite large, be patient. It is processing even though it looks like it might be stuck.
If there were any errors, an error report will appear