ClaimRowValue

Overview

This function returns a value from a data extension and locks the row to prevent the information from use in another context until the value claimed status changes. If the function finds a row with the correct matching set of keys, the function returns the requested column value. If the function finds no claimed row, the function attempts to claim an unused row. If the function cannot find or claim value, the function returns the specified default value. Otherwise, the function returns an error. Specify multiple pairs of column names and values as needed.

Syntax

ClaimRowValue(1, 2, 3, 4, 5, 6)

Function Properties

OrdinalTypeDescription
1stringRequiredName of data extension for claim or retrieve action
2stringRequiredName of column in data extension from which to retrieve the return value
3stringRequiredName of column in data extension that indicates a claimed or unclaimed row
4stringDefault value to use in case the function fails to claim a row
5stringRequiredName of the data extension key column
6stringRequiredValue of data extension key column

Usage

The data extensions used with this function must possess the following characteristics:

  • A Boolean column that indicates a claimed row or not (a true value indicates the row as claimed)
  • An index on the Boolean and _CustomObjectKey columns to allow for an efficient lookup of claimed rows
  • One or more key columns used to identify the object or recipient claiming the row value, which requires a nullable column
  • An index on the key column or columns to allow an efficient lookup of claimed rows
  • The application automatically sets indexes on sendable data extensions, and you should contact your Marketing Cloud account executive to set indexes for non-sendable data extensions.

You can also include an optional column to receive a timestamp at the time the application executes the ClaimedRow() function. You must name this column ClaimedDate and assign it the Date data type. When this column exists, the ClaimedDate value in that row receives the timestamp for when that row was claimed.

You cannot overwrite or delete the data extension with a claimed status. The sample below retrieves the same information from the same row any time a specific job sends to a specific recipient.

Use the example data extension below as a model for the data extension you use to store and claim rows:

Column NameTypeNullDefault Value
_CustomObjectKeyNumberNoIdentity
CouponCodeText(30 character limit)No
IsClaimedBooleanNo0
JobIDNumberYes
ListIDNumberYes
BatchIDNumberYes
SubscriberIDNumberYes
EmailAddressText(100 character limit)Yes
ClaimedDateDateYes