Changing incoming data in Data Loading/Update Data Function? | Community
Skip to main content
A
alb52547777
August 26, 2018
Solved

Changing incoming data in Data Loading/Update Data Function?

  • August 26, 2018
  • 2 replies
  • 2172 views

Hi all,

I have an SMS Gateway integrated with Adobe Campaign. The problem is it can only send SMS messages with the country code in front of the number, but most of our customer data does not contain the country code. We are in Australia (Country code +61) but mobile numbers start in 04. Thus, most of the mobile numbers in our database are stored as "04xx xxx xxx" when they need to be "614xx xxx xxx". Is there any way in a workflow or the data loading/update data function to change all entries that start with 04 to start with 614?

This is Adobe Campaign Classic v7

Many thanks,

Alex

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Garima_Gakhar

Hi alb52547777​alb52547777

There isn't an explicit 'prefix' field corresponding to the phone number which could allow you to update the prefix from 0 to 61 within a Workflow Activity.

You might have to change the phone numbers completely and that would be too inefficient since the query will have all the possible phone numbers (starting with 0) to be replaced with phone numbers starting with 61.

However you could consider the TON and NPI Settings in case the SMS Connection is being established with vendor through Extended Generic SMPP, see below Screen Shot:

NPI (Numbering Plan Identification) and TON (Type of Number) combination might allow the phone number prefix to be 0 on your side and 61 on vendor's side. For '0', TON value would be '2' (National) and for '61', TON value would be '1' (International).

The NPI value becomes '1' (ISDN/telephone numbering plan) for numbers starting with '61' and '8' for numbers starting with '0'.

Considering you are on v7, I am assuming that you might be using Extended Generic SMPP Connector only.

If so, then you could try this combination once.

For reference, below are the standard NPI and TON values:

TON:

Unknown = 0

International = 1

National = 2

Network Specific = 3

Subscriber Number = 4

Alphanumeric = 5

Abbreviated = 6

NPI:

Unknown = 0

ISDN/telephone numbering plan (E163/E164) = 1

Data numbering plan (X.121) = 3

Telex numbering plan (F.69) = 4

Land Mobile (E.212) =6

National numbering plan = 8

Private numbering plan = 9

ERMES numbering plan (ETSI DE/PS 3 01-3) = 10

Internet (IP) = 13

WAP Client Id (to be defined by WAP Forum) = 18

Hope this helps!

2 replies

Garima_Gakhar
Adobe Employee
Garima_GakharAdobe EmployeeAccepted solution
Adobe Employee
September 18, 2018

Hi alb52547777​alb52547777

There isn't an explicit 'prefix' field corresponding to the phone number which could allow you to update the prefix from 0 to 61 within a Workflow Activity.

You might have to change the phone numbers completely and that would be too inefficient since the query will have all the possible phone numbers (starting with 0) to be replaced with phone numbers starting with 61.

However you could consider the TON and NPI Settings in case the SMS Connection is being established with vendor through Extended Generic SMPP, see below Screen Shot:

NPI (Numbering Plan Identification) and TON (Type of Number) combination might allow the phone number prefix to be 0 on your side and 61 on vendor's side. For '0', TON value would be '2' (National) and for '61', TON value would be '1' (International).

The NPI value becomes '1' (ISDN/telephone numbering plan) for numbers starting with '61' and '8' for numbers starting with '0'.

Considering you are on v7, I am assuming that you might be using Extended Generic SMPP Connector only.

If so, then you could try this combination once.

For reference, below are the standard NPI and TON values:

TON:

Unknown = 0

International = 1

National = 2

Network Specific = 3

Subscriber Number = 4

Alphanumeric = 5

Abbreviated = 6

NPI:

Unknown = 0

ISDN/telephone numbering plan (E163/E164) = 1

Data numbering plan (X.121) = 3

Telex numbering plan (F.69) = 4

Land Mobile (E.212) =6

National numbering plan = 8

Private numbering plan = 9

ERMES numbering plan (ETSI DE/PS 3 01-3) = 10

Internet (IP) = 13

WAP Client Id (to be defined by WAP Forum) = 18

Hope this helps!

M
Adobe Employee
Mr-XAdobe Employee
Adobe Employee
September 24, 2018

Hi alb52547777​,

Alternatively, you can modify the data in transition using SQL activity like this,

UPDATE <%= vars.tableName %> SET semail = RIGHT(semail, '10');

UPDATE <%= vars.tableName %> SET semail = concat('61', semail) where length(semail) < 11 ;

Where in the first line->

semail is the column name I want to modify the data

10 - I'm keeping last 10 digits in the column, removing anything beyond 10

Second line ->

I'm adding 61 at the beginning of the each record where the record length is less than 11.

Regards,

Preetham

Terms & ConditionsAccessibility statement

Loading footer...