0

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 ]%%
5
  • What's the datatype of Clean_Order_Stage_Reached__c? Commented Feb 7, 2018 at 12:30
  • Hi Adam, the datatype is boolean.
    – eazy4me
    Commented Feb 7, 2018 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". Commented Feb 7, 2018 at 16:16
  • Hi Adam, the code is not working at all. Can you post the correct code for me please?
    – eazy4me
    Commented Feb 8, 2018 at 7:15
  • Hi Adam, can you help me?
    – eazy4me
    Commented Feb 9, 2018 at 7:06

1 Answer 1

0

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

else

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

endif 

]%%
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .