ClaimRow

This function returns a single row from a Data Extension and reserves the values to prevent them from being used by another operation. If a row is found and is unclaimed, the designated claimed column is set to true and data from that row is returned. If no unclaimed rows are available in the Data Extension, this function will return an error. Claimed rows can be used again if the designated claimed column changes back to false.

This function is primarily used for assigning unique coupon codes to an audience at send time and recording other pertinent data from the send context.

To utilize the ClaimRow function, you will need a Data Extension with some specific characteristics. At a minimum, your Data Extension must include the following:

  1. a Boolean column that indicates claimed status, where true is claimed and false is unclaimed
  2. a nullable column that represents the object or recipient claiming the row.

Arguments

ClaimRow(1,2,3,4,[5a,5b]...)

Ordinal Type Required Description
1 String True Data Extension from which to retrieve rows
2 String True Name of the column that identifies a row as claimed
3 String False Name of the Data Extension column to populate
4 String False Value of the Data Extension column to populate
5a String False Additional name of the Data Extension column to populate (see note)
5b String False Additional value of the Data Extension column to populate (see note)

NOTE: Additional pairs of columns and values can be appended as arguments.

NOTE: The Data Extension name in the ClaimRow function cannot be an AMPscript variable.

NOTE: This function does not return a row set that can be checked using the RowCount function.

NOTE: By default, this function does not support primary keys like the other update and upsert Data Extension functions. Additional name/value pairs of arguments are only for recording additional information at the time the row is claimed, not for retrieving coupons using additional values. Marketing Cloud Support may be able to alter this default behavior by adding explicit indexes to your coupon data extension, per this documentation.


Not a subscriber? Subscribe now.

Example 1

The following illustrates the use of this function with the minimum number of arguments.

The sample Data Extension schema for this ClaimRow example is provided below.

Data Extension: CouponCodes

Name Data Type Length Primary Key Nullable Default Value
CouponCode Text 50 Y N
IsClaimed Boolean N Y False
EmailAddress EmailAddress N Y
ClaimedDate Date N Y

The CouponCodes Data Extension could include these initial rows:

CouponCode IsClaimed EmailAddress ClaimedDate
SJOCYUNGIX False
ZURRPSIDQD False
LFZCWGPFCS False
KTZUYGCFOZ False
WDQHIUBVFS False

In this example, checking the _messageContext personalization string allows the coupon codes to be preserved while testing the AMPscript function. In this case, when no more coupons are available, the entire send is aborted with the RaiseError function.

%%[

var @em, @couponRow, @couponCode

if _messagecontext == "PREVIEW" then

    set @couponCode = "XX TEST XX"

else

    /* include the send context attributes or columns to record here */
    set @em = AttributeValue("emailAddr")

    set @couponRow = ClaimRow("CouponCodes", "IsClaimed", "EmailAddress", @em)

    if not empty(@couponRow) then

      set @couponCode = Field(@couponRow, "CouponCode")

    else

        /* You can do other error handling here */

        /* This aborts the entire send */
        RaiseError("No coupons available", false)

    endif

endif

]%%

Coupon code: %%=v(@couponCode)=%%

Output

If the AMPscript code referenced previously was executed in an email send to a single Subscriber, [email protected], the following would appear in the email:

Use this coupon code: SJOCYUNGIX

The result of the ClaimRow function would result in this claimed row in the CouponCodes Data Extension:

CouponCode IsClaimed EmailAddress ClaimedDate
SJOCYUNGIX True [email protected] 2017-10-10 12:34
ZURRPSIDQD False
LFZCWGPFCS False
KTZUYGCFOZ False
WDQHIUBVFS False

Example 2


Not a subscriber? Subscribe now.