Unanswered

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

(0) Share

Report

Posted on by timatello

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.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

joelzehring 208 on at

Like (2)

Report
Great question.

As you've pointed out, search in Salesforce works very different than the Get records activity in Power Automate.
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)
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:
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
Converts it to an integer value (int(<string>)). Source: int
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)
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')}'

Was this reply helpful? Yes No
timatello 38 on at

Like (0)

Report

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"
}

Was this reply helpful? Yes No
timatello 38 on at

Like (0)

Report

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": []

Was this reply helpful? Yes No
Verified answer

timatello 38 on at

Like (1)

Report

@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!

Was this reply helpful? Yes No
joelzehring 208 on at

Like (0)

Report

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!

Was this reply helpful? Yes No