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:
- Check for a value in the ParentID field of the Account record.
- If there is none, assign the account ID to the ManagementCompany field.
- If there is one, check its Account record for a ParentID.
- If there isn't one, assign that account as the ManagementCompany.
- If there is one, check and see if it has a value in the ParentID field.
- 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 ]%%