0

Using RetrieveSalesforceObjects I'm able to retrieve specified objects data, from what I understand this function uses the SC SOQL API to retrieve data. Using SOQL I'm able to query data like Account.Name from my Order.

However doing this using RetrieveSalesforceObjects, like this:

SET @Order = RetrieveSalesforceObjects(SObject,"Id, OrderNumber, Account.Name","Id", "=", RecordId)

Is this simply not possible or am I doing something wrong?

Edit: I’m aware I can deconstruct the various IDs and retrieve each object, the purpose of this question is the see if I can do this with one retrieve command using related fields feature from SOQL which the ampscript retrieve function is based on.

2 Answers 2

2

You can't use SOQL type of structure in ampscript. To fetch the Account Name from Order object, you would need to first get the Account ID from Order and then use the Account ID to get the Account Name.

It will be something like this:

SET @Order = RetrieveSalesforceObjects("Order__c","Id, OrderNumber, Account__c","Id", "=",RecordId)
Set @Orderrow = ROW(@Order,1)
Set @OrderAccountID = FIELD(@Orderrow,"Account__c")
SET @AccountDetails = RetrieveSalesforceObjects("Account","Id, Name","Id", "=", @OrderAccountID)
Set @Accountrow = ROW(@AccountDetails,1)
Set @AccountName= FIELD(@Accountrow,"Name")

%%=v(@AccountName)=%% Will give you the Account Name.

Let me know if this helps.

4
  • So the question wasn’t how to retrieve the released data but whether it’s possible to use the same SOQL syntax because that’s what the Retrieve function was based on. The reason I’m doing this is so I can avoid having multiple retrieve commands in the email, with large numbers this will quickly consume my SC API limits. May 27, 2020 at 16:30
  • 1
    Unfortunately you can't use same SOQL structure in ampscript. Also, the ampscript used to update salesforce won't count on API limits. But you might experience the sending will take sometime.
    – Naveen VM
    May 27, 2020 at 16:34
  • Thanks for confirming, if you can update your answer to include this I can mark it as the correct answer May 27, 2020 at 16:35
  • Hi @DeploymentFailure , I have made a statement in my answer 'you can't use SOQL type of structure in ampscript'. May I know what answer you are expecting.
    – Naveen VM
    May 27, 2020 at 16:39
0

As Naveen has pointed out: You will need the API names for your fields, then understand that the result is a rowset that you dissect with ROW and FIELD AMPScript functions.

It's a pain to get working the first time. Debug your function on a Cloudpage inside of a try/catch to get some idea about what's going wrong. Just insert your function into this Javascript wrapper:

<script type="javascript" runat="server">
    Platform.Load("Core","1");
   try {

</script>
%%[

/* your retrieve function here */



]%%
<script type="javascript" runat="server">
    Write("success");
} catch (err) {
    Write(Stringify(err));

}
</script>

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .