Sage CRM Data Import: Handling last names with apostrophes (8 replies)
In SQL you'd double up on the apostrophe character. Do you might have to insert a replace function to replace ' with '' <- two apostrophes.
http://stackoverflow.com/questions/6509159/how-do-i-search-for-names-with-apostrophe-in-sql-server
Django, thanks.
Arline, Django is correct.
You should perform this type of thing:
Lookup("CRMPERS", "pers_personid", Array(Replace(%field, "'", "''"), %otherfield), False)
Lookup("CRMPERS", "pers_personid", Array(Replace(%field, "'", "''"), %otherfield), False)
Thanks! I'll give that a try.
[Edited]It throws an error when I check the script, but it does map into Sage CRM successfully. Is that ok?Lookup:Lookup("CRMPERS", "pers_personid", Array(Replace(%[LASTNAME], "'", "''"), "%[CompanyId]"), False)Error during "check" script:Line=1;Pos=74; Unexpected identifier [TSNEPSRSIQKB] expected [,].Here's the original before the Replace statement:Lookup("CRMPERS", "pers_personid", Array(%[LASTNAME], "%[CompanyId]"), False)
It might be easier to create a new mapped field to see if your replace statement is working.
Arline, this worked here, so not sure why you're getting the syntax error.
FYI You shouldn't need to have %[CompanyId] in quote marks.
Thanks
Thanks guys, it seems to be working fine. And actually I think we had to use the % on CompanyId because of the way that field was mapped (or stored?). I remember having a hangup about that, and once we used the % everything fell into place. Thank you all so much!
Closed due to inactivity.
Hello,
What's the best practice for handling a lookup for a last name with an apostrophe? I realized my IMAN job that imports data to Sage CRM is aborting when it finds names like D'Agostino that include apostrophes.
Error whilst evaluating field [PersonId] on transaction type [Person].
Resolved Function - Lookup("CRMPERS", "pers_personid", Array("D'Agostino", "114017"), False)
Error - Description: Incorrect syntax near 'Agostino'.Unclosed quotation mark after the character string ' and (Pers_Deleted is null and Pers_FirstName is not null and Pers_LastName is not null)'.
Here is what I'm using for my Lookup:
CRMPERS
Where Clause of SQL: Pers_LastName = '%1' and pers_companyid= '%2' and (Pers_Deleted is null and Pers_FirstName is not null and Pers_LastName is not null)
Thank you guys in advance!