0

Can I use the ClaimRow() with where clause?

Essentially, I want to include voucher codes from various campaigns in one DE.

Thanks,

1

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 JobID and 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.

  • Looking at this - it may/may not work. The @codes may produce a temporary rowset, and the ClaimRowValue() may not actually be updating the de, but instead the temp rowset. – Kelly J Andrews Mar 29 '14 at 15:25
  • The ClaimRow() and ClaimRowValue() functions do not support a data extension name that is not literal. Meaning, you cannot use a variable instead of an actual DE string. – user2135025 Mar 29 '14 at 17:01
  • Was afraid of that, but didn't see it in the docs, so I may have missed it. With that then, what you are looking to do isn't possible. – Kelly J Andrews Mar 29 '14 at 17:03
  • Based on my tests, the ClaimRow() picks the first record available (unclaimed) in the DE. The 'key' columns are updated with information, such as JobID, Email Address etc etc. Should a second email is sent out to the same subscriber, then the 'key' columns become a WHERE clause. Is my understanding incorrect? Thanks. – user2135025 Mar 30 '14 at 10:56
  • This is the workaround that was built: 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()) We can identify which voucher codes correspond to specific campaigns this way. Do you think there is a flaw in the logic? Also, this'll be used in Triggered Sends. There a risk sending the same voucher to two different subscribers? – user2135025 Mar 30 '14 at 11:02
0

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")

See http://help.exacttarget.com/en/documentation/exacttarget/content/ampscript/ampscript_syntax_guide/data_extension_ampscript_functions/#ClaimRow for specifics

  • Hi Timothy, thanks for your reply. I had the same impression, however –in my experience– this is not a where clause but 'inserts'. All the keys and values are inserted in the available row, for instance: Job ID is populated in the DE with the actual Job ID, List ID with ListID, so on so forth. Is what I am saying incorrect? Thanks! – user2135025 Mar 29 '14 at 9:39
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.