0

We have MobileConnect contacts which we use filed "mobile number" as foreign key to the DE with the relationship one-to-many in order to send SMSs. The number of sending SMSs is equals number of that DE records with related phone number in mobileconnect contacts.

For example, if in this DE there are 3 records with Customer#1 mobile number, we are going to send 3 SMSs.

We can send 3 SMSs, however, we can't choose the record to send (always pick the first one) so we try to update the flag to skip the sent record and pick the next one. Here is our code:

%%[
var @rows, @row, @row_count
var @policy_number, @or_number
var @response
set @rows = LookupRows("ADA_Successful_Debit","Phone", MOBILE_NUMBER, "SMSSent", 0)
set @row_count = rowcount(@rows)
If @row_count > 0 then
    set @row = row(@rows,1)
    set @policy_number = field(@row,"Policy Number")
    set @or_number = field(@row,"OR Number")
    @response = UpdateDE("ADA_Successful_Debit",1,"Policy Number",@policy_number,"SMSSent", 1)
Endif
]%%

But updating the data extension doesn't work. Do you have any solution for this case?

  • 1
    There is still missing clarity around your question. Are you using this AMPscript in your sms message? If yes then why do you have a lookup and an update to the same DE? – Brad Sapkota Oct 24 '17 at 13:31
  • I'm not sure I understand things correctly. Correct me if I'm wrong. Yes we are using it in our sms msg. We update the flag to mark that we already send this SMS of this policy number(0 is not and 1 is sent) so that we wont get sent records in the next time we query. 1 Customer(or 1 Mobile number) can have many policy number records in the DE and we want to sent the number of sms msg equals the number of policy number records. – PRein Oct 25 '17 at 2:56
1

You're missing a set before your @response.

And you're using the wrong function. You should be using UpdateData() instead of UpdateDE(). There are a couple of sets of AMPScript functions that only work in certain contexts. I have to look it up every time.

%%[
var @rows, @row, @row_count
var @policy_number, @or_number
var @response
set @rows = LookupRows("ADA_Successful_Debit","Phone", MOBILE_NUMBER, "SMSSent", 0)
set @row_count = rowcount(@rows)
If @row_count > 0 then
    set @row = row(@rows,1)
    set @policy_number = field(@row,"Policy Number")
    set @or_number = field(@row,"OR Number")
    set @response = UpdateData("ADA_Successful_Debit",1,"Policy Number",@policy_number,"SMSSent", 1)
Endif
]%%

You might consider testing your code in landing/cloud page before adding it to MobileConnect. I find it much easier to debug.

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.