Difference between revisions of "Common SQLink Errors"
Line 11: | Line 11: | ||
Employee - EmployeeID<br> | Employee - EmployeeID<br> | ||
− | Usually for all SME modules there will be an item/column with <name>ID which holds the QB list UID. | + | Usually for all SME modules there will be an item/column with <name>ID which holds the QB list UID.<br> |
'''To include the QuickBooks ID number in the search window:'''<br> | '''To include the QuickBooks ID number in the search window:'''<br> |
Revision as of 21:05, 21 July 2011
Error including a string of numbers “########-##########”
The “########-##########” string of numbers refers to the QuickBooks list UID for the referenced item in the error. Any time you see that string in relation to an error, you will need to locate what item that number is referring to by adding the appropriate QB list ID field to a view so you can search on the string and locate the offending item. SME pulls the string from QB when an item is synced.
The following is a list of QB list ID’s for the various modules. Setup your search view in each module to include this column so you can locate the offending item in the syncing error you received:
Customer - CustID
Invoice - InvoiceID
Vendor - VendorID
Inventory - InventoryID
Service Item - ServiceID
Employee - EmployeeID
Usually for all SME modules there will be an item/column with <name>ID which holds the QB list UID.
To include the QuickBooks ID number in the search window:
1. In the search window, click Views > Add remove fields
2. In the window that opens, check the QuickBooks ID field for that module (list above)
3. Click OK.
4. Click View > Save View. Name the field and Click OK.
For more information about search views, go to Search Window
Example Error:
Error adding Bill10506
807: Object "80000016-308248122" specified in the request cannot be found. QuickBooks error message: Invalid argument. The specified record does not exist in the list.
Solution:
The numbers “80000016-308248122” are referring to an inventory item that QB says no longer exists in QB. It may be inactive, deleted, or related to the inactive parent. You can research it as noted above and shown below in the screenshot.
SQLink Error 702
702 - 702: The query request has not been fully completed. There was a required element ("800005BC-1267498278") that could not be found in QuickBooks.
The string - ("800005BC-1267498278") - means that this customer used to be in QB but it now has a new CustID. We do not know how this happens and sometimes QB will accept it and sometimes you have to change the name to sync it.
SQLink Error 805
Error adding XYZ Company - 805: The name " XYZ Company " of the list element is already in use
This means that the name - "XYZ Company" - is already in QB on a list. User needs to locate it in QB and add something to the name. QB convention is (V) if on the vendor list, (E), if on the employee list, etc... If it's already on the customer list add (SME) and merge it in QB with the original.
SQLink Error 899
Error adding XYZ Company - 899: The string "ST. FRANCIS OF ASSISI CATH. CHURCH - VIDEO" in the field "Addr1" is too long.
Need to shorten the address to less than 40 characters. There are tools available online to help you count characters such as http://www.lettercount.com/
SQLink Error 808
SQLink Error 808: Error adding Customer XYZ 808: There is an invalid reference to a parent "80000004-1207320348" in the Customers list.
QuickBooks error message: To change parent or type when the element or its descendants has been used in a transaction, you must switch to single-user mode. <- QuickBooks must be in single user mode. This error happens when the billing address is changed on a site customer record in SME. I do not recommend making these changes in SME. This specifically happens when you click the 'Select Billing Company' button or uncheck the 'Is Billable Flag' on the Billing Address and select a new parent/billing customer. Since QuickBooks has special limitations on moving customers between Parent/Billing companies it is always best to do this in QuickBooks. There are two issue that QuickBooks raises: - QB must be in Single User mode to make the change - If the change cannot be done due to QB rules you will be warned There are a couple of ways to move customers in QB. One is available from the following video link and one is by dragging the customer to a new parent.
Link to the Video:Move QB Customers to Job Sites
The other is to place the cursor over the customer and drag it to the new parent. If there are payments, etc.. you will not be able to move the customer. This will also return an error in SQLink since if QB cannot move it then SQLink cannot move it.
These issues are why I recommend 'only' reassigning customers in QuickBooks.
Duplicate Customer
If you see the following error in SQLink, there is a duplicate customer in SME.
Customer : XYZ is skipped due to duplication
You can follow the following steps to resolve the error or watch the viceo below.
<videoflash>5EC0ZdD3I7I</videoflash>
Find which customer record is the duplicate
- Go to Customers.
- Click Search >Search Active Customers.
- Click Views > New View.
- In the Grid Column Window, check the following fields: Name, BillName, Address 1, Address 2, City, St, CustID, ParentID, QBExport. Click OK.
- Arrange the columns the way you want in the search view window.
- Click Views > Save View.
- Type a name for the view and click OK.
- In the search view window, sort by Name.
- Locate the customer name SQLink said is a duplicate.
- Follow the row to the CustID column. If the column is empty, that is your duplicate customer record. Double click to open the duplicate.
- Click your cursor in the Company field to edit the customer name. Type (DUP) after the customer name. This makes it unique and easy to identify when reassigning orders and invoices for the duplicate customer.
- Click Save.
Now that you found which customer is the duplicate record, check to see if there is any activity for customer you need to reassign
- Click the Customer Activity tab.
- Click the Invoices tab.
- If there is an invoice on this tab, double click on it. SME will jump to the invoice.
- Click Actions > Rollback Invoice.
- Go back to the duplicate customer.
- Click the Customer Activity tab > Service tab to check to see if there is a service order assocaited with the "DUP" customer.
- If there is a serivce order, double click on it. SME will jump to the order.
- Click
to search for the correct customer. In the search window, select the correct customer record, not the "(DUP)" record.
- Save the service order.
- Click Actions > Generate Invoice to re-invoice the order.
- Follow steps 1-9 for install orders and invoices for the install orders.
Now the activity associated with the duplicate customer are assigned to the correct customer, delete the duplicate customer
- Go to the "DUP" customer.
- Click Delete to delete the customer record.
Customer Link ID not initialized
If you receive the following SQLink error, you were trying to sync an invoice to QuickBooks that didn't have a valid customer in QuickBooks.
Invoice #XXX skipped - Customer link ID not initialized
This is usually caused by duplicate customer records in SME. Click here to watch a video on how to resolve the duplicate customer on an invoice and sync the invoice properly.
<videoflash>5EC0ZdD3I7I</videoflash>
In the video, when the service order was re-invoiced, we let SME assign a new invoice number. If you need to retain the original invoice number, instead of clicking Action> Generate Invoice on the order, follow these steps:
- Write down the order number that needs to be re-invoices.
- Go to the correct customer record (not the DUP customer).
- Click Actions> New Combined Invoice. SME will jump to the new invoice.
- On the Details and Settings tab, locate the Invoice # field. Click
to enter the invoice number you want to use. (If you don’t see this icon, you need to add the permission to initialize numbers to your user group settings)
- Select the Service Orders or Install Orders tab to select the order you need to invoice (the invoice number you wrote down in the first step above).
- Click Save.
Error Connecting to QuickBooks
When syncing SME to QuickBooks, if you receive the message "Error Connecting to QuickBooks", you are logging into QuickBooks with a path that is different than that refereced by SQLink. This typically happens if you open QuickBooks on a client and try to sync when the initial sync was established on the server.
To run SQLink on client machines, you will need to do the following:
- From the client computer, browse to the sme5 executable folder and locate the SQLink Documentation folder.
- Run the QBFC7_0Installer.
Also, SQLink and QuickBooks must reference the same path to access the QuickBooks company file. to check this:
- Open QuickBooks.
- Press F2 to open the Product Information window.
- Locate the File Information section and make note of the path in the Location field.
- Launch SQLink.
- Click File > Link Setup.
- Locate the Quick Books Company File field. The path in this field must be the same as the path noted in QuickBooks (step 3 above).
List Element Error
When running SQLink with QuickBooks the following message is returned in the SQLink Log: Error adding (Item Name*) 819: There was an error when modifying a (can be any type of name) list, element** "3170000-1189020798". QuickBooks error message: Cannot merge list elements.
- An item in QB can be a customer name, vendor name, inventory item, etc…
- Element “#######-#########” is the QuickBooks unique identifier
Basically either the ‘Name’ or the ‘Element’ is on a list in QuickBooks and therefore the item that SME is exporting will be rejected by QuickBooks.
For example:
Both ListID and the Name are unique keys in QuickBooks. And you modified a customer which prompts to export that Customer with the ListID that matches an existing ListID in QB and the Item Name from another record or the Item Name matches, a more likely scenario, and the ListID is different.
This produces the "Cannot merge list elements" error message back from QuickBooks.
To resolve this issue:
1. Examine the SQLink log: Look at the error:
- Error adding Customer A 819: There was an error when modifying a Customers list, element "3170000-1189020798". QuickBooks error message: Cannot merge list elements.
2. In QuickBooks, locate and edit the name of the vendor/customer:
- Choose Lists > Customer: Job List or Lists > Vendor List, or Lists> Item List, etc… as needs may require.
- Locate and double-click the name noted in the SQLink Log.
- Place a 1 at the end of the name and click OK. Leave QuickBooks and this list open.
3. In SQLink do Data Functions> Export New QuickBooks Data> (List Item). In this case it will be Customers. There should be no error for the specific record and it will export to QuickBooks and sit right next to the QuickBooks Record.
4. In QuickBooks, merge the edited name with the name that has been created by the SQLink:
- In the open list in QuickBooks, double-click the name to which you added a 1.
- Remove the 1 from the name and click OK.
- When asked if you would like to merge the names, click Yes.
5. Do this for all items that have the issue from the SME log.