Salesforce connector failing with condition

Hi,

I’m experimenting with the Salesforce connector and have fallen at the first hurdle…

I have managed to connect up to Salesforce and can request a list of Accounts that come back from Salesforce (all good so far), but as soon as I specify a conditional field using the LastModifiedDate field, I get an odd error message back from Salesforce, which I can’t see how to overcome.

Here is a screenshot of the error:

The critical error message is

“description”: " LastModifiedDate FROM Account WHERE LastModifiedDate>=‘2021-12-13T00:00:00.000+0000’ ^ ERROR at Row:1:Column:44 value of filter criterion for field ‘LastModifiedDate’ must be of type dateTime and should not be enclosed in quotes"

I’m not enclosing the timestamp in quotes, so how do I overcome this? I’ve also followed the tooltip guidance to use an expression.

Flow is below.

Thanks
Scott

{
“name”: “TEST SALESFORCE”,
“nodes”: [
{
“parameters”: {},
“name”: “Start”,
“type”: “n8n-nodes-base.start”,
“typeVersion”: 1,
“position”: [
240,
300
]
},
{
“parameters”: {
“resource”: “account”,
“operation”: “getAll”,
“options”: {
“fields”: “LastModifiedDate”,
“conditionsUi”: {
“conditionValues”: [
{
“field”: “LastModifiedDate”,
“operation”: “>=”,
“value”: “={{$json[“modifiedTimestamp”]}}”
}
]
}
}
},
“name”: “Salesforce”,
“type”: “n8n-nodes-base.salesforce”,
“typeVersion”: 1,
“position”: [
680,
300
],
“credentials”: {
“salesforceOAuth2Api”: {
“id”: “8”,
“name”: “Salesforce account 2”
}
}
},
{
“parameters”: {
“values”: {
“string”: [
{
“name”: “modifiedTimestamp”,
“value”: “2021-12-13T00:00:00.000Z”
}
]
},
“options”: {}
},
“name”: “Set”,
“type”: “n8n-nodes-base.set”,
“typeVersion”: 1,
“position”: [
460,
300
]
}
],
“connections”: {
“Start”: {
“main”: [
[
{
“node”: “Set”,
“type”: “main”,
“index”: 0
}
]
]
},
“Salesforce”: {
“main”: [
[]
]
},
“Set”: {
“main”: [
[
{
“node”: “Salesforce”,
“type”: “main”,
“index”: 0
}
]
]
}
},
“active”: false,
“settings”: {},
“id”: 15
}

Hi @scottjscott, I’m so sorry you’re running into this behaviour. It seems Salesforce does not like single quotes when querying date fields.

Unfortunately, the default JavaScript Date object doesn’t seem to be understood by Salesforce either here, so I believe the node would need some custom handling for dates to make them work with Salesforce. Maybe @RicardoE105 can chip on this, in case I have overlooked something?

For the moment, would you be able to use the IF node to filter by date? For example like so:

Example Workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "resource": "account",
        "operation": "getAll",
        "options": {
          "fields": "LastModifiedDate",
          "conditionsUi": {
            "conditionValues": []
          }
        }
      },
      "name": "Salesforce",
      "type": "n8n-nodes-base.salesforce",
      "typeVersion": 1,
      "position": [
        880,
        440
      ],
      "credentials": {
        "salesforceOAuth2Api": {
          "id": "8",
          "name": "Salesforce account"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "modifiedTimestamp",
              "value": "2021-12-13T00:00:00.000Z"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        660,
        440
      ]
    },
    {
      "parameters": {
        "conditions": {
          "dateTime": [
            {
              "value1": "={{$items(\"Set\")[0].json[\"modifiedTimestamp\"]}}",
              "value2": "={{$json[\"LastModifiedDate\"]}}"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        1100,
        440
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Salesforce": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Salesforce",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Thanks @MutedJam - unfortunately I don’t think it is going to be practical to return all accounts to then filter them locally in the flow.

I think the main problem to overcome is the quoting of the date in the request - I think this likely needs an n8n change, and given the popularity of salesforce, probably should be expedited…

Ultimately I think I might need to abandon the standard connector and break out the http connector…

Thanks @scottjscott, I’ve filed this as a possible bug internally. If filtering in n8n is not an option due to the amount of accounts you have in Salesforce, the HTTP Request node might indeed be a suitable alternative until the issue is fixed since it gives you more granular control over the requests sent to an API.

@scottjscott Found the issue and got it fixed. It will be available in the next release.

1 Like

Amazing! That’s what I call excellent service :grinning:

2 Likes

when do you think this will go live?

Friday or Saturday probably. If can create a custom docker image that includes that if you want to use it right now. That, of course, assuming you are self-hosting n8n.