Migrating from QuickBooks to LGL

Export both the customer (donor) details as well as the transactions (donations) that you want in LGL.
When importing data using the LGL Flex Importer, take advantage of the "Full Name" field to import the Name field from QuickBooks. LGL will parse the Full Name field into its respective elements and does a good job with a variety of full name formats, such as Paul Newman; Mr. Paul Newman; Mr. and Mrs. Paul Newman; and even formats with middle names and initials.

Using the QuickBooks Name field as the external constituent ID in LGL

In QuickBooks, there is a Customer name field, which is guaranteed to be unique because QuickBooks doesn't allow you to re-use the same customer name twice. This field is very useful to set as the External constituent ID in LGL.

You will also need to set one or more name fields in LGL. The most likely fields you'll use are:

Full name	LGL will parse into first name and last name (and prefix, if there is one). It can also handle spouses, such as "John and Shasha Smith"
Last, first	LGL will parse into first name and last name. For example "Smith, Will" becomes first name = Will, last name = Smith
First name	First name of the constituent
Last name	Last name of the constituent
Employer/Organization	Organization name (either the Employer in the case of an individual constituent record or the organization itself in the case of an organization constituent record)

About constituent type

Every constituent record in Little Green Light has two possible types: Individual or Organization.

It's important to set this correctly, based on who the donor actually is. If you're recording a donation from John Jay at Johnson Machine Works, is the gift from John Jay or is it from the company? If the former, it should be an individual record; if the latter, an organization record.

If you have organization names in your Name field in QuickBooks, then you should insert a column in your upload file with the header "Organization name" and move all your organization names out of the Full Name column and into your Organization name column. Otherwise you will end up with records like "Foundation, Ford" because LGL will try to parse Ford Foundation as if it were a first and last name.

Beware subtotal rows of data

QuickBooks likes to provide summary data, such as rows of subtotals and totals. Those are not helpful when importing data into LGL. You should not upload any summary or subtotal data into LGL; LGL will be able to run any subtotals based on the individual items you upload.

The instructions below are split into the desktop version of QuickBooks Desktop and QuickBooks Online.

Migrating data from QuickBooks Desktop

You'll need to complete these three steps:

Preparing the export in QuickBooks (running a query to get the transactions you want)
Exporting that report from QuickBooks to Excel (or a text file)
Importing that file into LGL

1. Preparing the export

Generate a report that has the transactions you want to upload into LGL. In this example, we’re using the report Transaction List by Date found in the Accountant & Taxes area. Do not use a report that creates subtotals—this will cause problems when uploading to LGL.

A few things to keep in mind

Time frame: You want to make sure you select a time period that covers the period you want to load into LGL, which is probably your entire history.
Address, Phone, and Email fields: When importing your customer list from your QuickBooks file into LGL, you want as much of their contact information as possible. Customize your report to include these fields in your export.
Transaction Types: LGL is a great place to track income, but it is not designed to help you track the payments you make. So, you will want to filter your report from QuickBooks to include just the income items. Also, you might not track income from sales of merchandise or program fees in LGL (some organizations do, some don’t). When you run your QuickBooks report to get a file of transactions to upload into LGL, make sure you filter by Class or Transaction Type or some other way you might distinguish donations from other transactions.

You’ll see the results on your screen.

2. Generating the export

Click “Customize Report” and then go to the Header/Footer tab and uncheck all the header and footer information Quickbooks tries to add. This information is helpful if you’re printing the report, but it’s not helpful for the file you want to upload to LGL.

Click Excel to export a copy to Excel (create a new spreadsheet).

The Excel export is now in pretty good shape for uploading into LGL. A few of things to point out:

Make sure your transaction data is the first tab (sheet) in your workbook. If QuickBooks created a summary page on tab one, delete that or move it so your transaction data is in sheet 1. This is critical because the LGL Flex Importer only looks at the first tab of the Excel file you upload.
The first two columns have no headers. If you upload as an XLS or XLSX file, these will be ignored automatically; if you upload a CSV file you will be able to tell LGL to ignore these columns.
The first row below the headers is blank except for column A, and the same is true for the last row. LGL should ignore these rows, but if not you will be able to reject those records when reviewing your uploaded file.

3. Importing into LGL

In LGL, select the Flex Importer (on your home screen) for upload.

During the import process, you will be able to review which records LGL matched to. If you see a match that is incorrect, you can break that match (i.e., if your QuickBooks record for John Smith is incorrectly matching to an LGL record with the name of John Smith).

Map the fields from your upload to the correct LGL fields.

As the arrow in the screenshot below indicates, we are telling LGL to import the Name field into both the Full Name field as well as the External Constituent ID. This is a good option if you're not using the External Constituent ID field for anything else. You can use it to store your QuickBooks name field, and that will help make sure that future imports from QuickBooks will map to the correct constituent record in LGL. Note that QuickBooks ensures that each Name field is unique.

Where the arrow points above, there is a default being set that inserts the Name field from your QuickBooks file into the External Constituent ID field. The "Set default" rule looks like this:

After you’ve completed the mapping one time, you can save that upload as a template and then re-use it in future uploads. This will help you set up future uploads more quickly.

Migrating data from QuickBooks Online

You'll take these three steps:

Exporting customer and transaction lists from QuickBooks
Preparing these files for import to LGL
Importing into LGL

1. Exporting customer and transaction lists from QuickBooks

From QuickBooks Online, this step requires two parts (versus only one from QuickBooks desktop)

A. Customer List

In the Customers area, click the Export button to generate an export of your customer list:

Note that the Excel export includes fields for Address, Phone, and Email, even if they aren't showing in your on-screen view:

B. Transaction List

Next, go to the Reports area in QuickBooks and navigate to the Sales and Customers Reports area. We're looking for a report that will provide a listing of your transactions to import to LGL.

The best option we've found is Sales by Customer Detail. Click the Customize button to select the columns you want and to filter by date range and transaction type (Sales Receipt is most likely the only transaction type you want to use).

Make sure to include the Class field, which you'll probably want to map to Funds in LGL. And Product/Service should be mapped to Gift Category in LGL.

Make sure to edit the Rows/Columns selection and set the "Group by" to None. This is very important. Otherwise, your report will be full of rows of subtotals and summary information that you'll spend a lot of time removing.

2. Preparing these files for import to LGL

You have two Excel files to upload, and the key thing to notice is that the Customer name field in the two files is identical. You'll set this as the External constituent ID during the customer import and again for the transactions import, so LGL will match up transactions to the appropriate customer record.

Because the Name field in QuickBooks is guaranteed to be unique (QuickBooks doesn't allow two records to have the same customer name), you can use this field as an External Constituent ID in LGL. This will really simplify things when it comes to matching the records from the second file (the Transaction List) to the records you've uploaded from the first file (the Customer List).

The Sales by Customer Details report includes a few header rows. You'll want to delete those before uploading. Your first row should be the headers of your columns.

Here's an example of the two files you'll have:

Customer file
A few fields are really critical at this point. Referring to the screenshot of the Customer list import, here are some key things to do by column:

A. Add a column for constituent type. If your customer is a business or organization (government, foundation, etc.), make sure to set the constituent type to "Organization" so that LGL knows to set this as an organizational constituent versus an individual constituent.

B. Map your QuickBooks value for Customer as the External Constituent ID in LGL.

C and D. You will also want to import the customer name to LGL. This might be the same field you used for the External Constituent ID (just copy it over to another column in Excel). You can import using various formats of individual names:

Full Name (one column, column C)
Last, First (one column, column D)
First Name | Last Name (two columns) not shown

E. If the customer represents an organization, then set the type in column A and make sure to place the organization's name in a column mapped to Employer/Organization. That field is used for both an individual's employer and for the organization itself in the case of an organizational customer.

The image above is not showing the fields for address, phone, or email, but those should all be mapped as well.

Transaction file

When mapping these columns into LGL via the Flex Importer, you'll map, by column:

A. Date to Gift Date
B. --do not import-- (Transaction type)
C. Customer to External Constituent ID
D. Num is probably best to leave as --do not import--. Don't map the Num field to External gift ID, because that number is not guaranteed to be unique in QuickBooks. For example, if multiple items are on a single sales receipt, they'll have the same Num in that column.
E. Payment Method to Payment Type
F. Product/Service to Gift Category (note that in LGL Gift Categories live under Gift Type, so you'll probably want to set both the Gift Type and the Gift Category)
G. Class to Fund (or possibly Campaign, but Fund is more common)
H. Memo/Description to Gift Note
I. Amount to Gift Amount

3. After Importing into LGL

After you’ve completed the mapping one time, you can save that upload as a template and then re-use it on future uploads (so future uploads will go more quickly).