Can I use the
ClaimRow() with where clause?
Essentially, I want to include voucher codes from various campaigns in one DE.
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.Sign up to join this community
I think the answer here is no, you would need to separate your campaign coupon codes into individual data extensions. The functions do not have a where clause and there isn't another method of filtration.
It is a correct statement that when you have a row that is claimed, the value will match the key values you send to it. However, typically things like
BatchID are passed in as well, and wouldn't match one send to the next.
Manually recreating the function should work as this code suggests:
SET @rsCoupon = LookupOrderedRowsCS("Coupon Codes", 0, "_CustomObjectKey Asc", "Deemed", 0, "OfferCode", @offer_code, "IsClaimed", "False") SET @CustomObjectKey = Field(Row(@rsCoupon, 1), "_CustomObjectKey") UpsertData("NEW Coupon Codes", 1, "_CustomObjectKey", @CustomObjectKey, "IsClaimed", "True", "ClaimedDate", NOW())
This will resolve the need for multiple data extensions, however, reduces the value of the function itself. It doesn't cost extra to have multiple data extensions, so it seems like the better route would be to separate concerns, and break them into individual sets. While the above section should work, it adds the chance of error.
Yes. It's inherent to the method call... your WHERE clause is built into the overloaded ClaimRow Method beginning at parameter 3.
For something like: ...
WHERE param1 = '1' AND param2 = '2' AND param3 = '3'
the method call would look like
ClaimRow("Coupon", "IsClaimed", "param1", "1", "param2", "2", "param3", "3")
Though this question is old, it's still relevant:
ClaimRow and its variants still do not support (true*) WHERE clauses. The workaround suggested by Kelly is likely to work with a reasonably low risk of collisions in a landing page context. It will however fail thoroughly if employed in an email send context.
In a send context, the total send volume is divided into batches of 500 emails. Subsequent personalization is happening so fast that all 500 emails in a thread will grab the exact same row from the data extension. That's why ClaimRow implements a row-locking feature.
As of now, there is unfortunately no way around dedicated data extensions for each voucher campaign – which is a shame because it implies unnecessary manual overhead.
* ClaimRow will use specified key parameters to check if a voucher code has already been claimed for these. If yes, it returns the same code. In all other cases, specified key/value pairs will be written to the data extension, overwriting any pre-existing content.