Fetch the Lead Owner name on the Cloudpage using Ampscript

Question

I'm working on the Contact Us page, I want to fetch the lead owner from the Lead table on the cloud page. When i am trying to fetch the Lead Owner its show me 500 internal Error.

SET @subscriberRows = RetrieveSalesforceObjects("Lead", "Id,FirstName,LastName,Email,AnnualRevenue,MobilePhone,Company,LeadScore__c,RequestedCallBack__c,PreferredTime__c,IndustrySector__c,**Owner**","Id", "=", @contactId)

IF RowCount(@subscriberRows) > 0 THEN
  SET @row = row(@subscriberRows,1)
  SET @FirstName = field(@row,"FirstName")
  SET @LastName = field(@row,"LastName")
  SET @Email = field(@row,"Email")
  SET @Company = field(@row,"Company")
  SET @Turnover = field(@row,"AnnualRevenue")
  SET @Mobile = field(@row,"MobilePhone")
  SET @IndustrySector = field(@row,"IndustrySector__c")
  SET @LeadScore = field(@row,"LeadScore__c")
  SET @PreferredTime = field(@row,"PreferredTime__c")
  SET @Industry_Sector = field(@row,"IndustrySector__c")
  **SET @RM_Name= field(@row,"Owner")**

ENDIF

Answer 1

As per our last comments.

You want to understand the SFSC data model. Have someone show you Schema builder in SFSC Setup to understand the relationships between tables. Also make sure you understand what a Lookup relationship is in Sales Cloud.

A Lead has an Owner, and ownership is held by a User.

The name of a Lead Owner is thus something you need to get from the User object as this is where it is stored in SFSC.

So the first part of your retrieve code is all correct, but you can only get the OwnerId off the Lead Object. Nothing else about the User owning the Lead is physically represented there, - after all, why would we put it there, it can be found on the user once we have the user's unique ID. That's how a relational system works.

With the OwnerId from your first retrieve, you write a second retrieve on the User object, and get the relevant fields from there, in my example I used firstname and lastname.

code is from memory ("should work"), you get the idea:

%%[
SET @subscriberRows = RetrieveSalesforceObjects("Lead", "Id,FirstName,LastName,Email,AnnualRevenue,MobilePhone,Company,LeadScore__c,RequestedCallBack__c,PreferredTime__c,IndustrySector__c,OwnerID","Id", "=", @contactId)

IF RowCount(@subscriberRows) > 0 THEN
  SET @row = row(@subscriberRows,1)
  SET @FirstName = field(@row,"FirstName")
  SET @LastName = field(@row,"LastName")
  SET @Email = field(@row,"Email")
  SET @Company = field(@row,"Company")
  SET @Turnover = field(@row,"AnnualRevenue")
  SET @Mobile = field(@row,"MobilePhone")
  SET @IndustrySector = field(@row,"IndustrySector__c")
  SET @LeadScore = field(@row,"LeadScore__c")
  SET @PreferredTime = field(@row,"PreferredTime__c")
  SET @Industry_Sector = field(@row,"IndustrySector__c")
  SET @ownerId= field(@row,"OwnerId")

  SET @userRows = RetrieveSalesforceObjects("User","Id,FirstName,LastName","Id", "=", @ownerId)
    IF RowCount(@userRows) > 0 THEN
        SET @row = row(@userRows,1)
        SET @firstname = field(@row,"FirstName")
        SET @lastname = field(@row,"LastName")
        SET @ownerName = concat(@firstname," ",@lastname)
    ENDIF
ENDIF
]%%%%=v(@ownerName)=%%

Be advised that this chaining of retrieves into Sales Cloud can be slow, each one takes a couple seconds. So make sure you test this and ensure that the load time doesn't pose problems.