0

I have a data extension of Salesforce Leads & Contacts that I want to enter into a journey. The data extension has the following fields.

  • SubscriberKey (this is the 18-digit Salesforce Lead or Contact ID)
  • AccountID (this is the 18-digit Salesforce ID of the Account the Lead or Contact is assigned to.)*
  • ManagementCompany (this is the target field for this script and should populate with the Account ID of the highest level parent.)
  • ManagementCompanySet (this is a Boolean that defaults to FALSE and gets flipped to TRUE when the

*We have a custom lookup field on our Leads for associating them to accounts.

My thinking on how to do this was to do a hacked WHILE loop (which I got here) through all the records that don't have a value in the ManagementCompany field as follows:

  1. Check for a value in the ParentID field of the Account record.
  2. If there is none, assign the account ID to the ManagementCompany field.
  3. If there is one, check its Account record for a ParentID.
  4. If there isn't one, assign that account as the ManagementCompany.
  5. If there is one, check and see if it has a value in the ParentID field.
  6. Loop through steps 3 - 5, until you find the record that has no parent and assign its ID to the ManagementCompany field.

Here's the AMPscript I came up with, which is throwing an error. What am I doing wrong here? Can I even do this with AMPscript or should I be doing all of this with SQL?

%%[
Set @rows = LookupRows ('LAD_Journey_Entry_TESTER','ManagementCompanySet','FALSE')
Set @rowCount = ROWCOUNT(@rows)
IF @rowCount > 0 THEN
    FOR @i = 1 TO @rowcount DO
        Set @recordRow = ROW(@rows,@i)
        Set @sk = FIELD(@recordRow,'SubscriberKey')
        Set @accountID = FIELD(@recordRow,'AccountID')
        IF EMPTY(@accountID) == 'TRUE' THEN
            Set @upsertManagementCompany = UpsertData('LAD_Journey_Entry_TESTER',1,'SubscriberKey',@sk,'ManagementCompany','','ManagementCompanySet','TRUE')
        ELSE
            Set @j = 1
            Set @x = 2
            FOR @j=1 to @x DO
                Set @parentID = Lookup('Account_Salesforce_DE','ParentId','Id',@accountID)
                IF EMPTY(@parentID) == 'TRUE' THEN
                    Set @upsertManagementCompany = UpsertData('LAD_Journey_Entry_TESTER',1,'SubscriberKey',@sk,'ManagementCompany',@accountID,'ManagementCompanySet','TRUE')
                ELSE
                    Set @accountID = @parentID
                    Set @x = add(@x, 1)
                ENDIF
            NEXT @j
        ENDIF
    NEXT @i
ELSE
ENDIF   
]%%
  • What's the error? – Adam Spriggs Jul 28 at 11:23
  • Not sure, but I've isolated it to the UpsertData. When I take that out, it runs fine in Automation studio. I cut and paste it into an email, manually pushed the SubscriberKey into the loops, and they returned the right Account ID. – Will Jul 28 at 11:37
  • Oorah! Figured it out! – Will Jul 28 at 11:47
4

After many hours of beating my head against my desk, I figured out the painfully simple reason the code wasn't working. When pushing values into a Boolean field in AMPscript you have to use integers, not the text string equivalent. Here's my final code that runs.

%%[
Set @rows = LookupRows ('LAD_Journey_Entry_TESTER','ManagementCompanySet','FALSE')
Set @rowCount = ROWCOUNT(@rows)
IF @rowCount > 0 THEN
    FOR @i = 1 TO @rowcount DO
    Set @recordRow = ROW(@rows,@i)
    Set @sk = FIELD(@recordRow,'SubscriberKey')
    Set @accountID = FIELD(@recordRow,'AccountID')
    IF EMPTY(@accountID) == 'TRUE' THEN
        UpdateData('LAD_Journey_Entry_TESTER',1,'SubscriberKey',@sk,'ManagementCompanySet',1)
    ELSE
        Set @j = 1
        Set @x = 2
        FOR @j=1 to @x DO
        Set @parentID = Lookup('ENT.Account_Salesforce_DE','ParentId','Id',@accountID)
        IF EMPTY(@parentID) == 'TRUE' THEN
            Set @upsertCompany = UpdateData('LAD_Journey_Entry_TESTER',1,'SubscriberKey',@sk,'ManagementCompany',@accountID,'ManagementCompanySet',1)
        ELSE
            Set @accountID = @parentID
            Set @x = add(@x, 1)
        ENDIF
        NEXT @j
    ENDIF
    NEXT @i
ELSE
ENDIF 
]%%

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.