As the error message indicates, there is no record in the rowset returned in your @Caso rowset.
This could be caused by one of the following scenarios:
- The SObject is not available in Sales Cloud
- There isn't a matching
RecordId value in your Sendable Data Extension
- The
SObject or RecordId fields don't appear in your Sendable Data Extension
I'd suggest building in some exception handling to handle this scenario like you have done with the second rowset.
Additionally, you are retrieving the Id field from the Lead object, but you haven't explicity included this in the second argument. Refer to updated code below.
While this will work, I would advise against using RetrieveSalesforceObjects function in an email if you are sending to a large number of Subscribers, as the function can be very slow (approx 2-3 seconds per retrieve — for a send of >1m records, I calculated it would take 1.5 years for the email to send!). Instead, you may want to consider syncing the objects as Synchronized Data Extensions and using the LookupRows function to retrieve your rowset, which will be a lot more performant (but this is only an option if the object record was created >15 minutes ago).
%%[
var @mySObject, @myRecordId, @Caso, @CasoRow, @CasoIDLead, @Lead, @Name, @Email
Set @mySObject = AttributeValue("SObject")
Set @myRecordId = AttributeValue("RecordId")
Set @Caso = RetrieveSalesforceObjects(@mySObject, "Id, ID_Prospecto__c", "Id", "=", @myRecordId)
if RowCount(@Caso) > 0 then
Set @CasoRow = Row(@Caso,1)
Set @CasoIDLead = FIELD(@Casorow,"ID_Prospecto__c")
else
RaiseError('no matching rows found for ID_Prospecto__c', true)
endif
Set @Lead = RetrieveSalesforceObjects("Lead", "Id,Name,Email", "Id", "=", @CasoIDLead)
if RowCount(@Lead) > 0 then
Set @LeadRow = Row(@Lead, 1)
Set @LeadID = FIELD(@LeadRow, "Id")
Set @Name = FIELD(@LeadRow, "Name")
Set @Email = FIELD(@LeadRow, "Email")
else
RaiseError('no matching rows found for Lead Id', true)
endif
output(concat("<br>rowCount: ", @rowCount))
output(concat("<br>Name: ", @Name))
output(concat("<br>LeadID: ", @LeadID))
output(concat("<br>Email: ", @Email))
]%%
Update
After reviewing this issue on a call with Esteban, I identified that the Data Extension field names in the Triggered Send DE were incorrect (e.g. scenario #3 was the issue), as they used the field names mySObject and myRecordId. Updating these field names to the correct values fixed this issue.
FYI, when using a Salesforce Triggered Send, if you add the fields SObject and RecordId to your Triggered Send Data Extension (like Esteban has done) then these fields will be automatically populated with the respective object name and record Id, so you can then use these values as arguments in AMPscript functions.
