1

I have a malformed query problem when using simple salesforce for my AWS python Lambda. The query uses the AccountContactRelation object.

When an event is dropped into an AWS S3 bucket for taxonomy CSV files that contain a mix of lead and contact records, the extraction integration is supposed to upload those records into Salesforce as either new contacts and leads or to update the existing Salesforce record. Here is the process that the integration uses:

  1. Verify if the lead or contact extracted has its SF ID or an email
  2. If the record does not contain those 2 attributes (EMail, SF ID) then do an internal query.

The query code for leads in Simple Salesforce works well:

     def find_leads(self, info):
        print('In find_leads')
        try:
            if 'FirstName' in info and 'LastName' in info and 'Company' in info and 'State' in info and 'City' in info and info['FirstName'] is not None and info['FirstName'] != '' and info['LastName'] is not None and info['LastName'] != '' and info['Company'] is not None and info['Company'] != '' and info['State'] is not None and info['State'] != '' and info['City'] is not None and info['City'] != '':
               
                self.query = self.__sf.query_all("SELECT Id FROM Lead WHERE  FirstName = '" + self.adding_escape_char(info['FirstName']) + "' AND LastName = '"+ self.adding_escape_char(info['LastName']) + "' AND Company LIKE'" + self.adding_escape_char(info['Company']) + "%' AND State = '" + self.adding_escape_char(info['State']) + "' AND City = '" + self.adding_escape_char(info['City']) + "' AND IsConverted = FALSE limit 1" )
                print(self.query)
                if self.query['records'] == []:
                    return('New', '-')
                else:
                    print('Number of Matching records found: ',len(self.query['records']))
                    return ('Lead', json.loads(json.dumps(self.query['records']))[0]['Id'])
            else:
                return('New', '-')
        except Exception as e:
            print(e)
            print('in find_leads Exception')
            self.__logging.unprocessed_records_logging('Fetch','Lead', e)

However the Contact query in Simple Salesforce either comes up empty (but there is a value) or I get a Malformed query. However, I have tested using other tools to show that the query does return a result.

     def find_contact(self, info):
        print('<< In find_contact >>')
        print(info['FirstName'])
        print(info['LastName'])
        print(info['Company'])
        print(info['State'])
        print(info['City'])

        try:
            if 'FirstName' in info and 'LastName' in info and 'Company' in info and 'State' in info and 'City' in info and info['FirstName'] is not None and info['FirstName'] != '' and info['LastName'] is not None and info['LastName'] != '' and info['Company'] is not None and info['Company'] != '' and info['State'] is not None and info['State'] != '' and info['City'] is not None and info['City'] != '':
               
                self.__sf.query_all("SELECT ContactId FROM AccountContactRelation WHERE Account_Status__c IN ('Prospect' , 'Terminated') AND MailingState = '" + self.adding_escape_char(info['State']) + "' AND MailingCity = '"+ self.adding_escape_char(info['City']) + "' AND Name LIKE '" + self.adding_escape_char(info['Company']) + "%'  limit 1" )
                print(self.query)
                if self.query['records'] == []:
                    print('<< QUERY DIED>>')
                    return('New', '-')
                else:
                    #print('Number of Matching records found: ', json.loads(json.dumps(query['records']))[0]['Id'])
                    #return ('Contact',json.loads(json.dumps(self.query['records']))[0]['Contact']['Id'])
                    return ('Lead', json.loads(json.dumps(self.query['records']))[0]['Id'])
            else:
                return('New', '-')
        except Exception as e:
           print(e)
           print('in find_contact Exception')
           self.__logging.unprocessed_records_logging('Fetch','Contact', e)
           return('New', '-')

Here is the AWS Cloud Watch Log that shows the empty dictionary

enter image description here

Note: When I run the queries using either Workbench or Developer Console, the query for the contact works well and returns a value. Here are the following screen scrapes for both workbench and Dev Console. I can't figure out what I am missing. Thanks for your help.

enter image description here

enter image description here

4
  • One thing I'm seeing is that when I look at the contact object for this client, it seems that there is no firstname nor lastname field just Name. I thought that first name and last name fields were standard fields like the lead object. This wuld possibly explain tings but I am still testing this case scenario out. Oct 9, 2021 at 20:28
  • How have you verified that the authenticated user on AWS has permission to view those records?
    – David Reed
    Oct 9, 2021 at 21:20
  • I can still use fields first name and last name in queries on Dev Console and Workbench. When I remove the First name query attribute from the SOQL query in the python code for Simple Salesforce, it seems that the query runs fine but when I add the First Name field to the query then the query fails. Oct 9, 2021 at 21:28
  • 1
    There's two different problems in your question, with two different causes. One is a Malformed Query error; the other is the query returning no results. The nature of the user that is running the query is pertinent to the latter.
    – David Reed
    Oct 9, 2021 at 22:25

1 Answer 1

0

I have fixed this issue:

This problem was 2-fold because:

  1. It dealt with extra padded spacing after the first name field in the taxonomy file being ingested into S3.
  2. Also, the code was incorrectly returning the wrong attribute API field name for the AccountContactRelation object's contact ID

Hopefully this will be huge in solving this intermittent duplicate contact issues that I was seeing.

Thanks for all the responses that you all have sent me. I appreciate them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.