Common SQLink Errors

From High5Wiki
Revision as of 17:52, 30 June 2014 by Coryc (talk | contribs)
Jump to navigation Jump to search

Best Practices for addressing SQLink Errors

  • Fix errors as they occur.
  • Fix errors in the sequence illustrated in the image below starting with Customer errors and ending with Proposal errors.

FixSequence.png


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 element (########-#########) is the QB term for an item; Customer, Vendor, Employee, Inventory, Service, Account, etc...

The string - ("800005BC-1267498278" - is the QB ID for an item in QB) - means that the item, a customer, invoice, account, service, etc... used to be in QB but it now has a new QB ID or has otherwise been modified in QB. Modifications can include setting the item as inactive, deleting or merging it with another item, etc... To resolve look at all items related to the error and make sure they exist in QB.

  • - If it's a customer, make sure the customer is active.
  • - If it's an invoice it should re-export creating a new QB ID.
  • - If it's an inventory/service item review them on the invoice and make sure they are all QB items.
  • - Check SQLink defaults and make sure all accounts on the default tabs in SQLink are present and active in QB.
  • - Open SQLink> File> Setup> check all tabs and make sure the listed items are active in QB.

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 899 - You cannot pay more than the amount due

899: The "AppliedToTxnAdd payment amount" field has an invalid value "3353.98". QuickBooks error message: You cannot pay more than the amount due.

This error occurs when QuickBooks cannot mark an invoice as paid because the invoice balance and the applied amount are in conflict. They payment cannot be applied to the QuickBooks invoice because it has already been paid.


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.

This error can also come up on Invoices as: Invoice ######## skipped - Billing Link ID not initialized. The usual cause is either the site/job or the Billing customer has been set to inactive.

SQLink Error 810

810: There was an error when saving a Invoice. QuickBooks error message: The transaction is empty.

In this case the invoice was a combined invoice and the order on the invoice was removed or deleted. Go to invoice in SME and either uncheck "export to QB" or delete invoice.

Can also come up as 'The transaction must be positive' Go to invoice in SME and either uncheck "export to QB" or delete invoice.

Can also come up as 'Invoice ##### skipped - voided invoices cannot be exported' Go to invoice in SME and either uncheck "export to QB" or delete invoice.

This error can also come under PO as: 810: There was an error when saving a Bill. QuickBooks error message: This transaction action is not allowed for this user. - Probable causes are the user running SQLink does not have permission to modify bills in QuickBooks or the closing date has been set and a QB Admin would need to modify the restriction to export the PO/Bill.

SQLink Error 851 : Unable to find the QuickBooks Request Processor

851 : Unable to find the QuickBooks Request PRrocessor on your system. Please make sure that QuickBooks 2002 Pro or higher is installed on your system

Download and run the installer from the following link. QBFC 11.0: http://high5software.com/downloads/QB/QBFC11_0Installer.exe


SQLink Error 201

Error adding 201: Required field CustomerName not specified.

Error 201 can pertain to any Customer, Vendor, Inventory, Service Item, etc.. record where the Name field is blank.

To correct:

- Go to the module referenced, Customer in the case above

- Do Search All and click on the Name field

- You will see a blank record

- Select the record

- Check if the record should be tied to a specific customer. If the record has no information you can safely set it as inactive and delete it.

SQLink Error 819 - Error adding XYZ – The object may not be changed

This is part of the – ‘Unknown error’ returned from QB – seems sometimes it returns an error # and others it does not.


- If switched to Single user – they may get - XYZ Company – Unknown error returned from QuickBooks… this means QB cannot move the child to a new parent due to history on the child company like payments, invoices, etc.. – (See below)
- Additional info to above… can also be reported as Error 819
- Error adding XYZ 819 – (The object may not be changed). Error adding XYZ 819: There was an error when modifying a Customers list, element "800009F6-1300911661". QuickBooks error message: Unknown Error. The Customer List Element is the QuickBooks UID for the customer you have flagged to move in SME.


- To Resolve: Review QuickBooks for the XYZ company and reset the SME companies to match what they were before the ‘Is Billable’ flag was lifted or the Billing/Parent Company was changed.

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 video below.

<videoflash>5EC0ZdD3I7I</videoflash>


Find which customer record is the duplicate

  1. Go to Customers.
  2. Click Search >Search Active Customers.
  3. Click Views > New View.
  4. In the Grid Column Window, check the following fields: Name, BillName, Address 1, Address 2, City, St, CustID, ParentID, QBExport. Click OK.
  5. Arrange the columns the way you want in the search view window.
  6. Click Views > Save View.
  7. Type a name for the view and click OK.
  8. In the search view window, sort by Name.
  9. Locate the customer name SQLink said is a duplicate.
  10. Follow the row to the CustID column. If the column is empty, that is your duplicate customer record. Double click to open the duplicate.
  11. 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.
  12. 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

  1. Click the Customer Activity tab.
  2. Click the Invoices tab.
  3. If there is an invoice on this tab, double click on it. SME will jump to the invoice.
  4. Click Actions > Rollback Invoice.
  5. Go back to the duplicate customer.
  6. Click the Customer Activity tab > Service tab to check to see if there is a service order assocaited with the "DUP" customer.
  7. If there is a serivce order, double click on it. SME will jump to the order.
  8. Click Magnify.jpg to search for the correct customer. In the search window, select the correct customer record, not the "(DUP)" record.
  9. Save the service order.
  10. Click Actions > Generate Invoice to re-invoice the order.
  11. 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

  1. Go to the "DUP" customer.
  2. 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 Browse.jpg 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)

InvoiceNumberField.jpg


  • 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:

  1. From the client computer, browse to the sme5 executable folder and locate the SQLink Documentation folder.
  2. Run the QBFC7_0Installer.

Also, SQLink and QuickBooks must reference the same path to access the QuickBooks company file. to check this:

  1. Open QuickBooks.
  2. Press F2 to open the Product Information window.
  3. Locate the File Information section and make note of the path in the Location field.
  4. Launch SQLink.
  5. Click File > Link Setup.
  6. 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.

- 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.

InventoryID SearchView.png


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.

Error - QBFC Not Found

QBFC Not Found.png
The QBFC program is local to the logged on user. Download and run the installer from the following link.
QBFC 11.0: http://high5software.com/downloads/QB/QBFC11_0Installer.exe

SQLink stops processing in the middle of a sync

If SQLink stops processing in the middle of your sync, you can look at the log file and find out what item is holding up the sync or if there is an error connecting to QuickBooks.

1. Right click on your SQLink shortcut and select open file location.
2. Double click on the Log folder
3. Sort by modified date and locate the file modified last, it will probably be a sqlink file.
4. Right click, not double click, on the file and select Edit.
5. Scroll to the bottom of the file and you will see the item that is holding up the sync.


QB Invoice number has changed

Processing 10 of 110 Invoices (Ref #405-I)
QB Invoice number has changed : QB = 405-I SMP = 405


Invoice numbers in SME are numeric only. Invoice numbers in QuickBooks are strings so they can have numbers and letters. When the invoice from QB syncs to SME, the letters will be taken out of the invoice number in SME. Your invoice number in QB isn't changed.


Error adding customer XYZ 810: There was an error when saving a customers list, element

Error adding Custer XYZ 810: There was an error when saving a Customers list, element " Custer XYZ". QuickBooks error message: This customer already has job information.
To add a new job, the customer's current job information must be moved into its own job.


Invoice #12345 skipped - Customer is inactive

Adding (X) Invoices
Processing 1 of (X) invoices (#12345)
Invoice #12345 skipped - Customer is inactive


Retrieve the Invoice and get the customer name. Check the name in both SME and QuickBooks and set the customer as active to export an invoice.


Purchase Order 12345 skipped - no received items to export

Adding Purchase Orders to QuickBooks
Processing 1 of (X) Purchase Orders
Purchase Order 12345 skipped - no received items to export


The ‘Export To QuickBooks’ flag is set when there are no received items to export. There are two flags, QB Bill Export and QB PO Export. Uncheck the flag as it will get set if items are received on the PO.


Payment not distributed

Payment not distributed means that there is no invoice associated with the payment. For a payment to export from SME to QB it has to have an invoice attached. I would do the following:

1) Delete the payment from the Payments module
2) Go to Receivables > Invoicing and go to the invoice
3) Go to the Payments tab and enter the payment

That way you are assured that it is attached to an invoice.

If you have the invoice in QB you can also apply the payment in QB and it will sync back to SME and close the invoice.


There is no disk in the drive. Please insert a disk into drive E.

This happens when USB devises are removed without first ejecting the media. This can happen either from USB or DVD/CD devises. You may also need to put a readable DVD or CD in your removable drives.


A quick way to stop the error from popping up is to quickly click the "Save to file" button in lower right corner. You can click "Cancel" on the window that opens.
SQLink_SaveToFile.png


Prevent the error from happening in the future:
1. First look at the removable USB devises icon next to the system clock.
RemovableDisc.png
2. When you put the mouse over the icon you’ll see the following:
SafelyRemove.png
3. Click on the icon and you will see all USB related devises
EjectFlash.png
4. Remove all drive related entries that are not currently plugged in.
5. Place a disk in all DVD/CD drives.
6. This should clear up the issue.


More info on the topic:
http://ccrqblog.ccrsoftware.info/2010/02/stopping-the-windows-no-disk-error/



For more dire cases do the following:
1. Right click on My Computer
2. Click on Manage
3. Click on Device Manager
4. Click the "+" on the Disk Drives
5. Count how many "Drives" that your computer is currently using.
6. When the Error message pops up then count how many Drives it


Note: On some computers you will see duplicate entries in your device manager. You will need to Disable the duplicates.
DuplicateDiscs.png

QuickBooks warning 550

999: Unrecognized QuickBooks warning: 550: The Customers object was saved successfully, but its corresponding Notes record could not be saved.


This is a warning / bug from QuickBooks which High5 can't do anything about. The record should be updated with the sync, but to stop getting the error you must go to the record in QuickBooks and enter any character in the Notes.


SQLink Error 511 - QBRequestID has been used previously for another request

This message is from QuickBooks. Usually closing and reopening QB will clear the message and reset the request processor. We've noticed that the QB ID generator sometimes gets stuck, and when it does, this is the outcome.
Error511 QBRequestID.png


To resolve, close QuickBooks and reopen. This should reset the QuickBooks ID generation