With AMPscript I want to extract the Start_date__c from the row with the Clean_Order_Stage_Reached__c = True. How can i achieve that? I tried a lot with LookupRows but every time it shows all three rows. Find my code below.

This is the DE with 3 rows:

Candidate__c         Clean_Order_Stage_Reached__c     Start_date__c
003w000001P3Zc5AAF   False                            25-04-2017
003w000001P3Zc5AAF   True                             01-02-2017
003w000001P3Zc5AAF   False                            12-12-2016

This is the code i used


VAR @contact_DE, @contactID, @rowset_Startdate, @rowscount_Startdate, @row, @DEColumn1

SET @contact_DE = "Placement__c_Salesforce"
SET @contactID = [_subscriberkey]

SET @rowset_Startdate = LookupRows(@contact_DE,"Candidate__c",@contactID,"Clean_Order_Stage_Reached__c","True")
SET @rowscount_Startdate = RowCount(@rowset_Startdate)

IF @rowscount_Startdate > 0 THEN

 FOR @i = 1 TO @rowscount_Startdate DO

 SET @row = Row(@rowset_Startdate, @i)
 SET @DEColumn1 = Field(@row, "Start_Date__c")


DEColumn1 is %%=v(@DEColumn1)=%% 

next @i ]%%

%%[ else ]%%
%%[ endif ]%%
  • What's the datatype of Clean_Order_Stage_Reached__c? – Adam Spriggs Feb 7 '18 at 12:30
  • Hi Adam, the datatype is boolean. – wfrankhuizen Feb 7 '18 at 13:11
  • I'd output the other fields from Clean_Order_Stage_Reached__c in your loop and also change your lookup criteria to "1" instead of "True". – Adam Spriggs Feb 7 '18 at 16:16
  • Hi Adam, the code is not working at all. Can you post the correct code for me please? – wfrankhuizen Feb 8 '18 at 7:15
  • Hi Adam, can you help me? – wfrankhuizen Feb 9 '18 at 7:06

First, I'd also output the value of contactID to ensure the you're getting the value for _subscriberKey that you should be.

Then I'd remove the Clean_Order_Stage_Reached__c from your lookup and output it in your loop so you know the values that are getting returned. If that looks good, then you can move it back to being part of the lookup.


var @Placement__c_DE, @contactID, @Placement__c_rows, @rowCount, @row

set @Placement__c_DE = "Placement__c_Salesforce"
set @contactID = AttributeValue("_subscriberkey")

output(concat("<br>contactID: ", @contactID))

set @Placement__c_rows = LookupRows(@Placement__c_DE, "Candidate__c", @contactID)
set @rowCount = RowCount(@Placement__c_rows)

if @rowCount > 0 then

 for @i = 1 TO @rowCount do

     var @Start_Date__c
     var @Clean_Order_Stage_Reached__c

     SET @row = row(@Placement__c_rows, @i)
     SET @Start_Date__c = Field(@row, "Start_Date__c")
     SET @Clean_Order_Stage_Reached__c = Field(@row, "Clean_Order_Stage_Reached__c")

     output(concat("<br>Start_Date__c: ", @Start_Date__c))
     output(concat("<br>Clean_Order_Stage_Reached__c: ", @Clean_Order_Stage_Reached__c))

 next @i


     output(concat("<br>no ", @Placement__c_DE, " rows found"))



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.