0

I need to generate a unique order ID based on 3 criteria :

  • product specific string "ABCDE"
  • "MMdd" formatted timestamp "1127"
  • order number from that day in "001" format

The way I'm trying to solve this is to do a LookupRows on the existing order IDs which contain the concatenation of the string and timestamp (e.g "ABCDE1127") and I increment the number it returns by 1 before appending said number to the end of my concatenated string (e.g first order of the day should be "ABCDE1127001").

I got 2 records in my data extension which match the value yet when I display the @rowcount value which should be 2 it returns 1...

Here is my code :

var @daystamp, @rowcount, @concatstamp, @dailyrowcount, @orderid

set @daystamp = Format(Now(), "MMdd")
set @concatstamp = Concat("ABCDE", @daystamp)
set @rowcount = RowCount(LookupRows("DataExtension", "OrderID", @concatstamp))
set @dailyrowcount = Format(Add(1, @rowcount), "000")
set @orderid = Concat("ABCDE", @daystamp, @dailyrowcount)
2
  • Can you update your question with some details about what's in "DataExtension"? Nov 27, 2017 at 18:25
  • What's the context of this code? An email, landing page or SMS message? Nov 27, 2017 at 18:27

1 Answer 1

0

It may be easier simply populate a data extension with a huge number of unique order IDs and then utilize the claimRowValue() to reserve a number for each email:

The OrderNums Data Extension would have these columns:

  • OrderNum, String (50), Primary Key, non-nullable
  • IsClaimed, Boolean, nullable
  • EmailAddress, EmailAddress, nullable
  • ClaimedDate, Date, nullable

The email would include code something like this:

%%[

var @emailAddress, @defaultOrderNum, @orderNum

set @defaultOrderNum = "X"
set @emailAddress = AttributeValue("emailaddr")
set @orderNum = ClaimRowValue("orderNums", "orderNum", "IsClaimed", @defaultOrderNum, "EmailAddress", @emailAddress)

if @orderNum == "X" then
  raiseerror("no more order numbers")
endif

]%%

Order Num: %%=v(@orderNum)=%%

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.