cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
timatello
Frequent Visitor

Salesforce, Get records, Order By ??HELP??

Hi All

 

I have a flow that captures account numbers and some other data from a local excel file, Finds the account in Salesforce and creates a task.

It then deletes that line item from the excel file and creates a new line in a Seperate tab (as to maintain a log of successfully processed lines.)

 

My issue is that to find the correct account in Salesforce the account numbers need to have 10 digits.

So our "Newer" 9 digit account numbers need a leading 0 (eg. 0123456789)

And our "Older" 7 digit account numbers need 3 x leading 0 (eg. 0001234567)

 

The data dump in the file is missing the leading 0's - This can't be changed as this is how it pulls out of SAP.

- So I have a a Filter Query as per the screenshot.

- Issue I have found is that some of the older account numbers are contained within the newer numbers meaning there can be multiple results in Salesforce.

 

Regardless of this if I search for an account the top result when sorted by "Relevance" is the correct one (screenshot).

 

How do I edit Get Records > Order By > Relevance.

I have tried Id asc, Id desc, Relevance asc, Relevance desc with no luck....

 

 

Anyone have any ideas?

 

Thanks in advance.

 

Capture.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
joelzehring
Advocate III
Advocate III

Great question.

 

  1. As you've pointed out, search in Salesforce works very different than the Get records activity in Power Automate.
  2. The Get records activity uses the Salesforce REST API to query records (in this case, Account records) using OData. Your Order By value seems reasonable, but I found this in the Salesforce docs: "The fields in the ORDER BY clause of the SOQL query don't always match the properties used by the $orderby option in the resulting OData query." I take that to mean Salesforce may be expecting something slightly different via Odata like "AccountId" instead of "Id". Source: OData Query String Options (salesforce.com)
  3. The fastest fix I would suggest for your flow as it stands now is to modify the Filter Query field of the action. I would suggest: sap_id__c=@{formatNumber(int(items('Create_objectScope')['Account']),'D10')}. Here's what this expression is doing:
    1. Takes the Account string value from the current spreadsheet row (items('Create_objectScope')['Account']). This is my best guess based on your screenshot, so you might need to tweak it. Source: items
    2. Converts it to an integer value (int(<string>)). Source: int
    3. Converts the integer to a formatted string with enough leading zeros to satisfy the decimal format specifier (formatNumber(<string>, <format specifier>)). Source: Decimal Format Specifier (D)
  4. This would theoretically return an array with a single record instead of multiple records, so you could avoid the whole Order By issue.

Let me know if that works or if you have any feedback or further questions.

 

UPDATE: As @timatello mentions below, the syntax for Odata should be

sap_id__c eq '@{formatNumber(int(items('Create_objectScope')['Account']),'D10')}'

 

View solution in original post

timatello
Frequent Visitor

@joelzehring 

Just had to change the = to eq and add a comma either side of the format.

 

This was the expression that got it working:

sap_id__c eq '@{formatNumber(int(items('Create_objectScope')['Account']),'D10')}'

 

Thank you so much for your help!

timatello_1-1649384440416.png

timatello_0-1649384878838.png

 

 

View solution in original post

5 REPLIES 5
joelzehring
Advocate III
Advocate III

Great question.

 

  1. As you've pointed out, search in Salesforce works very different than the Get records activity in Power Automate.
  2. The Get records activity uses the Salesforce REST API to query records (in this case, Account records) using OData. Your Order By value seems reasonable, but I found this in the Salesforce docs: "The fields in the ORDER BY clause of the SOQL query don't always match the properties used by the $orderby option in the resulting OData query." I take that to mean Salesforce may be expecting something slightly different via Odata like "AccountId" instead of "Id". Source: OData Query String Options (salesforce.com)
  3. The fastest fix I would suggest for your flow as it stands now is to modify the Filter Query field of the action. I would suggest: sap_id__c=@{formatNumber(int(items('Create_objectScope')['Account']),'D10')}. Here's what this expression is doing:
    1. Takes the Account string value from the current spreadsheet row (items('Create_objectScope')['Account']). This is my best guess based on your screenshot, so you might need to tweak it. Source: items
    2. Converts it to an integer value (int(<string>)). Source: int
    3. Converts the integer to a formatted string with enough leading zeros to satisfy the decimal format specifier (formatNumber(<string>, <format specifier>)). Source: Decimal Format Specifier (D)
  4. This would theoretically return an array with a single record instead of multiple records, so you could avoid the whole Order By issue.

Let me know if that works or if you have any feedback or further questions.

 

UPDATE: As @timatello mentions below, the syntax for Odata should be

sap_id__c eq '@{formatNumber(int(items('Create_objectScope')['Account']),'D10')}'

 

Thanks for the response @joelzehring 
I have tried using your Filter Query and got the below result.

 

Do you know what I've done wrong regarding position 10 in the Filter Query?

 

{
  "status"400,
  "message""Syntax error at position 10 in 'sap_id__c=0001000237'.\r\n     inner exception: Syntax error at position 10 in 'sap_id__c=0001000237'.\r\nclientRequestId: f9564218-6236-4e09-83bf-62790e8440be",
  "error": {
    "message""Syntax error at position 10 in 'sap_id__c=0001000237'.\r\n     inner exception: Syntax error at position 10 in 'sap_id__c=0001000237'."
  },
  "source""salesforce-we.azconn-we-003.p.azurewebsites.net"
}
 
timatello_0-1649381503850.pngtimatello_1-1649381566500.png

 

 

Have also tried replacing the = with eq which resulted in this message:

  "status"400,
  "message""Salesforce failed to complete task: Message: \nsap_id__c FROM Account WHERE (sap_id__c = 0001000237) ORDER BY\n                              ^\nERROR at Row:1:Column:2622\nvalue of filter criterion for field 'sap_id__c' must be of type string and should be enclosed in quotes\r\nclientRequestId: 564d40bd-1fac-431c-87a3-e0dc674b809c",
  "error"null,
  "source""Microsoft.Azure.Connectors.Salesforce",
  "errors": []

 

 

timatello_0-1649383955680.png

 

timatello
Frequent Visitor

@joelzehring 

Just had to change the = to eq and add a comma either side of the format.

 

This was the expression that got it working:

sap_id__c eq '@{formatNumber(int(items('Create_objectScope')['Account']),'D10')}'

 

Thank you so much for your help!

timatello_1-1649384440416.png

timatello_0-1649384878838.png

 

 

joelzehring
Advocate III
Advocate III

Glad you got it worked out. The equal symbol would work in a SOQL query, which is how I normally query Salesforce records. Sorry for the confusion!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,253)