Can I retrieve related data using RetrieveSalesforceObjects?

Question

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.

Answer 1

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.

Answer 2

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>