Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using our Daily Account Extract in a new QV file I am building.
One thing I've done is create a bookmark based on numerous selections - this is a selection of customers I am sending a marketing message to on a monthly basis.
It works perfectly except for one thing I have to then do manually in excel after sending the selections to excel.
My selections are based on about 10 different fields. Then I send it to excel and I there I check that the customers have valid cell phone numbers.
In excel I do the following:
Is there any way I can do this in QV? Ideally I would like to create a new field called "Valid Number". Then, if it meets the above criteria it must be Yes, and if not it must be No. This way I can just thsi field as "Yes" to my bookmarked selection.
Does this make sense?
Also, before I text the customers, I change the international format to the format our SMS interface can understand. I do this by removing the +27(0) or the +264(0). So in a new excel colum I say =right(A1,9) - (I just need the right 9 characters of the number). then I replace the international numbers with these 9 numbers. Can I somehow do this in my script already?
I know this is a two-part question; help in either or both would be great!
Thanks,
G
Hi,
If this is not very urgent. I can look at this tonight... Also please post some sample data for testing purpose.
Cheers - DV
Give it a try with
IF((LEN(PhoneNumber) = 15 OR LEN(PhoneNumber) = 16) AND (LEFT(PhoneNumber, 6) = '+27(0)' OR LEFT(PhoneNumber, 7) = '+27(0)',
'YES', 'NO') AS NumberIsValid
HTH
Peter
You can do very robust phone number validation in script using regular expression testing. See the QV Cookbook example "Regular Expression pattern matching function."