AMPScript Display most recently created record with RetrieveSalesforceObjects

Question

We are using RetrieveSalesforceObjects to pull records from the Lead object but we want to make sure that the record we select from the Row is the most recently created record. How can we approach this situation?

Answer 1

As explained in the answer provided by Rachid, it is not possible to sort records pulled using the RetrieveSalesforceObjects function. Instead, since you're looking to retrieve Lead data, it would make sense to use the LookupOrderedRows function and pull the data from your Synchronized Data Extensions. Here's an example:

set @lookupValue = AttributeValue("Email") /* value from attribute or DE column in send context */
set @rows = LookupOrderedRows("Lead_Salesforce", 1, "CreatedDate DESC", "Email", @lookupValue)

Above will pull one lead, with the most recent CreatedDate and it will look it up using the email address.

Answer 2

Since the RetrieveSalesforceObjects function will return a rowset, you can get creative with that rowset to find a most recent date. Here's one flow that works, using a combination of RetrieveSalesforceObjects, IF Statements, FOR loop, and the DateDiff function:

1. Set a default date (1900/01/01 00:00AM for example)
2. Loop through your rowset (FOR loop, all rows returned in Retrieve function)
3. Compare the date field you're interested in with the default date (DATEDIFF function)
4. If that records row has a greater date, overwrite the default date and save the record ID to another variable. If it's not greater, skip. (IF conditional statement)
5. Once each row has been compared, the row with the most recent date value will be saved over the date variable and you will have their ID saved to another variable. You can use that ID as needed, or use to retrieve the remaining information in another Retrieve call.