Retrieve Salesforce Object with AMPScript

Question

I have a Smart Capture form that is used to capture opt-ins for Salesforce Contacts. I want the form to use the contact's first name and phone number to find the person's Contact ID and then put the contact ID into the data extension tied to the Smart Capture form.

I have the below code, but it's not finding a Contact ID even though a Contact ID exists.

%%[

var @subscriberRows

set @subscriberRows = RetrieveSalesforceObjects(
"Contact",
"ID,FirstName",
"phone", "=", @phonenumber,
"FirstName", "=", @FirstName )

if RowCount(@subscriberRows) == 1 then /* there should only be one row */
var @subscriberRow, @firstName, @lastName, @phonenumber
set @subscriberRow = Row(@subscriberRows, 1)
set @firstName = Field(@subscriberRow, "FirstName")
set @lastName = Field(@subscriberRow, "LastName")
set @phonenumber = Field(@subscriberRow, "phonenumber")

/* Update Data Extension with Contact ID */
InsertDE("SMS_Optin_test", "FirstName", @firstName, "LastName", @lastName, "PhoneNumber", @phoneNumber, "ContactID", @contactID)
else
/* Contact not found */
set @contactID = "error"
endif
]%%

Answer 1

There is still a chance that there is more than one contact with the same First Name and Phone Number, so you would want to check whether the row set @subscriberRows has only 1 row or not.

You can set fixed values for the issue record and put this code on a Cloud Page to debug. It will show the available contact IDs for a predefined First Name and Phone Number value.

%%[

var @subscriberRows

set @phoneNumber = "PHONE NUMBER"
set @FirstName = "FIRST NAME"

set @subscriberRows = RetrieveSalesforceObjects(
"Contact",
"ID,FirstName,LastName,Phone",
"Phone", "=", @phoneNumber,
"FirstName", "=", @FirstName )

set @rowCount = RowCount(@subscriberRows)
if @rowCount > 0 then
    for @i = 1 to @rowCount do
        set @row = Row(@subscriberRows, @i)
        set @contactID = Field(@row, "ID")
]%%

%%=v(@contactID)=%%<br>

%%[
    next @i
endif    

]%%

Answer 2

The Contact ID is not being retrieved or declared correctly. The script needs to include a line to set @contactID based on the retrieved row. You can try the below code.

%%[ var @subscriberRows, @contactID set @subscriberRows = RetrieveSalesforceObjects( "Contact", "Id,FirstName,LastName,Phone", "Phone", "=", @phonenumber, "FirstName", "=", @FirstName ) if RowCount(@subscriberRows) == 1 then set @subscriberRow = Row(@subscriberRows, 1) set @contactID = Field(@subscriberRow, "Id") set @firstName = Field(@subscriberRow, "FirstName") set @lastName = Field(@subscriberRow, "LastName") set @phonenumber = Field(@subscriberRow, "Phone") InsertDE("SMS_Optin_test", "FirstName", @firstName, "LastName", @lastName, "PhoneNumber", @phoneNumber, "ContactID", @contactID) else set @contactID = "error" endif ]%%