Best answer

Does the Salesforce WHERE Clause support ORDER BY?

  • 12 February 2021
  • 15 replies
  • 1232 views

Userlevel 1

SOQL allows the use of ORDER BY in a SELECT statement.

Is there a way to set this up in zapier’s WHERE clause?

 

icon

Best answer by nicksimard 28 June 2021, 23:30

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

15 replies

Userlevel 7
Badge +14

Hi @WorkHard 

Check out this Salesforce help article: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_orderby.htm

Userlevel 1

I did before I posted this but not sure how that article helps.

As you can probably tell, you can’t write a full SOQL query in zapier.

It only allows specifying the WHERE clause whereas SORT BY is usually declared after SELECT.

Userlevel 7
Badge +14

@WorkHard
Perhaps try this Salesforce Search instead, followed by a Code step to sort.

 

Userlevel 1

Appreciate the alternative suggestion but you lost me here.

Zapier will ALWAYS return 1 result from a “Find Record” step.

Meaning, even if my object contains multiple rows, zapier will ALWAYS only return 1.

So not sure how the “Code step” can pull another record.

Userlevel 7
Badge +14

@WorkHard

There are 3 different Find Record searches for Salesforce.

One of which may return multiple records.

Give that a try.

 

I could be wrong tho…

How search actions work

Salesforce search actions return the most recently created record that meets the search criteria, even if there are multiple records that match.

 

And if that is not sufficient, then you may have to get more technical and leverage the Salesforce API via a Webhooks step or Code step: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/intro_what_is_rest_api.htm

 

Webhooks: https://zapier.com/apps/webhook/integrations

Code: https://zapier.com/apps/code/help

Userlevel 1

Uhm, I don’t think that’s how it works.

Find Record(s) lets you search for multiple records, yes, but it doesn’t return multiple records from the same record.

Meaning, I can search for multiple IDs (enter each Id separated by a comma) but the result from each of those IDs will only be 1 record even if there are multiple items/results for each Id.

Id Date
1 01/01/2021
1 01/05/2021
2 02/01/2021
2 02/05/2021

 

In the example above Find Record(s) will let me pick Record.Id: 1,2

But the result will still be 1: 01/01/2021 and 2: 02/01/2021.

It won’t see the other row from the same ID.

Userlevel 7
Badge +14

@WorkHard 

You can try to see if including an ORDER BY clause works with the WHERE clause, but as you suspect it may not work.

 

Thus you may have to get more technical and leverage the Salesforce API via a Webhooks step or Code step: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/intro_what_is_rest_api.htm

 

Webhooks: https://zapier.com/apps/webhook/integrations

Code: https://zapier.com/apps/code/help

Userlevel 1

I’m already using webhooks but that wouldn’t work for this because I need the result on the fly.

Do you know if I can call the salesforce API in the Code step (javascript)? Can’t find any examples so this is most likely beyond my zapier knowledge.

 

Userlevel 7
Badge +14

@WorkHard

Webhooks app in a Zap has these actions available to interact with an app’s API

 

 

Code app examples using JavaScript for HTTP requests

https://zapier.com/help/create/code-webhooks/javascript-code-examples-in-zaps#step-2

Userlevel 1

I think I tried going this path a while ago and got stuck at the fact that all these examples are simple http without authentication. 

There’s no way I can figure out how to authenticate with salesforce from here because you can’t use the existing connection here.

Userlevel 7
Badge +14

@WorkHard 

Correct, using Code or Webhooks you’ll have to manage the connection credentials separately than the authenticated app in Zapier as defined by the app’s API authentication protocols.

Webhooks app as actions has some basic authentication fields available.

 

Userlevel 7
Badge +14

@WorkHard 

If you’re still stuck, perhaps consider hiring a Zapier Expert: https://zapier.com/experts

Userlevel 1

Thanks, Troy.

Basic authentication is not exactly enterprise security level. This won’t fly in our org.

The real solution here is for zapier to implement the ORDER BY clause rather than us hiring someone to recreate the wheel.

 

This would help users that are limited by Zapier’s API because not only it can only return 1 record out of multiple, the decision of which record to return is very much up in the air and not guaranteed.

The ORDER BY clause with its DESC, ASC, and LIMIT modifiers would greatly help everyone to return which result they want and even return multiple by creating multiple steps in the zap.

 

 

Userlevel 1

@WorkHard I agree! I feel like the need to order the result table is pretty basic. What would be amazing is if we could just use 100% SOQL to query a record or records. 

Userlevel 7
Badge +11

Hi @WorkHard and @Jay Lopez!
We have a couple of open feature requests (one to support ORDERBY and the other to return multiple records from a search). I’ve added you both to those requests, so you’ll be notified via email should those they be implemented.