Sync Error Due to Special Character in a Field

Using a special character in any field throws error while syncing

Unexpected unexpected token: 's'' extendedErrorDetails='{[0]}'] row='1' column='43']]”

Summary

This article explains how to identify and resolve the issue by updating the mapping accordingly in DBSync.

Unexpected unexpected token: 's'' extendedErrorDetails='{[0]}'] row='1' column='43']]” ,

Problem

If a special character is used in any field while syncing, you will get an error message

Unexpected unexpected token: 's'' extendedErrorDetails='{[0]}'] row='1' column='43']]

For example:

The problem is caused by this part:

Name='"+VALUEATTR("CustomerRef","name")+"'"

If the value of CustomerRef.name contains an apostrophe (for example, Joe's Garage), it breaks the SOQL string because the single quote ' ends the string prematurely. This is the error:

Unexpected unexpected token: 's'' at column 43

is pointed out.

Error in mapping at Target field [Account__c] : [MalformedQueryFault [ApiQueryFault [ApiFault exceptionCode='MALFORMED_QUERY' exceptionMessage='from Account where Name='Joseph's Project -Catholic Charities of ^ERROR at Row:1:Column:43 unexpected token: 's'' extendedErrorDetails='{[0]}'] row='1' column='43']]

Applies To

Product/Service: DBSync Cloud Workflow

Versions: All versions supporting Salesforce-QBO integration

Operating Systems: Platform-independent (Cloud-based)

Databases / Connectors: Salesforce, QuickBooks Online

Applicable To: Quickbooks

Solution / Steps

Prerequisites:

• Admin access to DBSync

• Mapping access for the integration project

Steps:

1. Log in to DBSync and open the Integration Project used for syncing data to QBO.

2. Go to the Field Mapping section where ID is mapped.

3. Check the logic used for mapping. You’ll find something like:

Old Mapping:

TLOOKUP("Select Id from Account where Name='"+VALUEATTR("CustomerRef","name")+"'") ]

1. Log in

2. Update the mapping by adding ESCAPESPECIALCHARACTER

New Mapping:

TLOOKUP("Select Id from Account where Name='"+ESCAPESPECIALCHARACTER(VALUEATTR("CustomerRef","name"))+"'")

1. Save the mapping and re-run the sync.

Verify that the previously erroneous records are now syncing successfully.

Workaround (If Applicable)

Remove the special character from the name.

Root Cause

This points to an unexpected character or quote, probably due to a mismatched or unescaped apostrophe (') in a string.

Unexpected unexpected token: 's'' at row='1' column='43'

Additional Information / Related Articles

https://salesforce.stackexchange.com/questions/115044/unexpected-token-or?utm_source

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_quotedstringescapes.htm