I have a situation where we want to assign a CouponCode to anyone who registers through our web form. I am using a landing page with AMP script to insert the form filled into DE(Data Extension), additionally I want to retrieve the Coupons present in another DE and put it along with the new record being inserted in DE. There is a 'product qty.' field in form, we want to assign 3 coupons if product qty = 5, 1 coupon to qty < 5.

How do I retrieve 3 coupons if qty = 5? LOOKUP function is not helpful because it asks for matching column which I don't have, I just need any random 5 coupons which I can add against the record is being submitted.

1 Answer 1


In cases like this, I usually create a Coupon_DE like this:

CouponCode | IsClaimed | SubscriberKey
 (pkey)    |   T/F     |   Text(254)

Where Isclaimed and SubscriberKey are nullable and IsClaimed defaults to false.

You then have the AMPscript in your form perform a lookuporderedrows with columns returned being 1 or 3, and this will then return a rowset of your coupons. After you get the coupons, I would also add an update to run to update the Coupon_DE to push the SubscriberKey and IsClaimed status.

So I would look at something like this:

SET @cpnCnt = IIF(@productQty > 4, 3, 1)

SET @cpnRows = LookupOrderedRows('Coupon_DE',@cpnCnt,"CouponCode ASC","IsClaimed",0)

SET @cpnRowCount = RowCount(@cpnRows)

FOR @i=1 TO @cpnRowCount DO
  SET @row = ROW(@cpnRows,@i)
  SET @coupon = FIELD(@row,"CouponCode")

  OUTPUT(CONCAT("Coupon Code ",IIF(@cpnRowCount > 1,CONCAT("#",@i," "),""),"is ", @coupon))
  UpdateDE("Coupon_DE",1, "CouponCode", @coupon, "IsClaimed",1,"SubscriberKey", _subscriberkey)


This will not only help you correctly dispense the coupons, but can be used to keep track of the coupons used and who took them.

I know that a lot of this can also be accomplished via the ClaimRow function, reducing the manual need there, but I tend to find it more reliable when I do the lookup/update on my own.

The official recommendation of SFMC is to utilize Claimrow as it is more performant. So I wanted to share both in case you have better luck with it than I do. Below is a very basic sample. I have not tested it and you may need to tweak it more to fit your needs though.

SET @cpnCnt = IIF(@productQty > 4, 3, 1)

FOR @i=1 TO @cpnCnt DO
    set @couponRow = ClaimRow("CouponCodes", "IsClaimed", "EmailAddress", @em, "CouponNumber",@i, "JobID",AttributeValue("jobid"))

    if not empty(@couponRow) then
      set @couponCode = Field(@couponRow, "CouponCode")
      OUTPUT(CONCAT("Coupon Code ",IIF(Rowcount(@couponRow) > 1,CONCAT("#",@i," "),""),"is ", @coupon))
       RaiseError("Coupon Claim Error")

  • Using this process do you ever find that the multiple subscriber "claims" the same coupon in a batch since the UPDATEDE() process doesn't commit until the batch is processed?
    – EazyE
    Jul 17, 2020 at 19:26
  • I have not noticed that behavior before but will definitely go back and check/keep my eyes open for it. Jul 18, 2020 at 0:17
  • @EazyE - I got confirmation that even when done in batches this will assign unique codes on each send. The recommendation is still to use Claimrow() to be fair as according to SFMC it is more performant, so will adjust answer to also mention solution utilizing claimrow as well Jul 19, 2020 at 23:03
  • Thank you @Gortonington Giving a shot with this code.
    – seeker
    Jul 20, 2020 at 12:38

You must log in to answer this question.

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