Jump to content

Leading Zeros and Zip Code Formatting


andym

Recommended Posts

I am referencing a list of addresses from an external data file. There are a few addresses in Massachusetts with leading zeros. Once the spreadsheet is put on MarcomCentral, the formatting defaults to drop leading zeros. The rule I wrote for this is below:

 

var Address = "";
var Name = "";
var Street = "";
var City = "";
var State = "";
var Zip = "";
var Country = "";

myXDF= new ExternalDataFileEx("ATLocations.txt", "\t");

for (i = 1; i < myXDF.recordCount+1; i++)
{
  FirstVar = myXDF.GetFieldValue(i, 0);
  {
       if (FirstVar==Field("Address"))
       {
           Name = myXDF.GetFieldValue(i, 1);
           Street = myXDF.GetFieldValue(i, 2);
           City = myXDF.GetFieldValue(i, 3);
           State = myXDF.GetFieldValue(i, 4);
           Zip = myXDF.GetFieldValue(i, 5);
           Country = myXDF.GetFieldValue(i, 6);
       }
   }
}
return ' | ' + City+", "+State+" "+FormatNumber("00000", Zip);

 

The last part of the returned text is the zip code. In previous templates this seemed to work for me, but I am now getting different results, i.e." MA, 1913 00000" where it drops the leading zero and puts five zeros after the actual zip code.

 

Finally, one extra item that we need to work. There are also Canadian addresses. Whatever rule(s) we use also needs to work for those locations. Now, the result looks like this: "Ontario, L6T 3V1 00000."

 

Is the rule I have on the right track? Should the zip code part be left out and made into an independent rule?

 

Any help is greatly appreciated.

 

Thank you,

 

Andy

Link to comment
Share on other sites

The last part of the returned text is the zip code. In previous templates this seemed to work for me, but I am now getting different results, i.e." MA, 1913 00000" where it drops the leading zero and puts five zeros after the actual zip code.

This can happen if the second parameter to FormatNumber is actually a string, with non-numeric characters such as spaces. Try this instead on the last line:

return ' | ' + City + ", " + State + " " + FormatNumber("00000", Int(Zip));

Or:

return ' | ' + City + ", " + State + " " + FormatNumber("00000", Trim(Zip));

Finally, one extra item that we need to work. There are also Canadian addresses. Whatever rule(s) we use also needs to work for those locations. Now, the result looks like this: "Ontario, L6T 3V1 00000."

This is a horse (with a Mountie?) of a completely different color (colour?). You can't use FormatNumber on a Canadian Postal Code (not a "ZIP" code, that's USPS terminology), because, well, it's actually not a number at all.

 

If you Google for something like "Canadian Postal Code JavaScript validation," or "Canadian Postal Code JavaScript Regular Expression," you'll find some examples of JavaScript to validate and format these codes. Here's one: http://stackoverflow.com/a/26788801/3207828

 

Having said all of that, I think the third sentence in your post belies the real problem here:

Once the spreadsheet is put on MarcomCentral, the formatting defaults to drop leading zeros.

I assume that the "spreadsheet" to which you refer is an Excel document. Excel may indeed be The World's Most Popular Database , but if it's mangling your data and you have to write rules in FusionPro to unmangle it, I would instead consider trying to manage your data in a different format that doesn't modify it in the first place.

Link to comment
Share on other sites

Thank you for your help Dan (and humor). The solution you listed for the leading zero worked perfectly.

 

As for the Canadian postal code, I found a work around to achieve my end goal and figured I would share here.

 

First I separated the Canadian address postal codes into their own column in Excel and updated the external data file on MarcomCentral. It now has seperate "Zip" and "PostalCode" fields. Then I duplicated the initial rule (from first post) and changed it so if the location is in Canada it will

return Rule("CANADA-Part 2");
else
return Rule("US-Part 2");

 

The only difference is the last line of the rule:

 

This is US-Part 2:

return ' | ' + City + ", " + State + " " + FormatNumber("00000", Trim(Zip));

 

This is CANADA-Part 2:

return ' | ' + City + ", " + State + " " + PostalCode + ' | ' + 'CANADA';

 

Thanks again,

 

Andy

Link to comment
Share on other sites

  • 5 years later...

Hi Dan,

 

I'm in a similar situation (not zip code specific but leading zeros specific) but using external data. I have the current code, which is pulling the rec field instead of the "DITKT#" or "DDTKT#" fields. Any help is appreciated.

 

Thanks,

Ryan

 

//Calling data from an external data file

var ex = new ExternalDataFileEx('ppfinalfltb.csv', ',');

var cursor = ex.SortBy('KEY1');

var rec = cursor.FindRecords(Field('KEY1'));

 

 

if (((ex.GetFieldValue(rec, 'RDAT01') !='') && ex.GetFieldValue(rec,'DITKT#') != '') && (ex.GetFieldValue(rec,'DDTKT#') != ''))

 

{

return ex.GetFieldValue (rec, FormatNumber('0000000000000', Trim('DDTKT#'))) + ', ' + ex.GetFieldValue (rec, FormatNumber('0000000000000', Trim('DITKT#')));

}

 

if ((ex.GetFieldValue(rec,'RDAT01') !='') && (ex.GetFieldValue(rec,'DITKT#') != ''))

 

{

return ex.GetFieldValue (rec, FormatNumber('0000000000000', Trim('DDTKT#')));

}

 

if (ex.GetFieldValue(rec,'GROUND') !='')

 

{

return RawText('Our records indicate that have elected to make your own flight arrangements. Therefore, we do not have your ticket number(s) on file.');

}

 

return '';

Edited by ryanceot
Link to comment
Share on other sites

I'm in a similar situation (not zip code specific but leading zeros specific) but using external data. I have the current code, which is pulling the rec field instead of the "DITKT#" or "DDTKT#" fields. Any help is appreciated.

Help with what exactly? What results are you getting, and how are they different from what you're expecting? Posting a sample of the data would be helpful.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...