andym Posted January 27, 2015 Share Posted January 27, 2015 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 Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted January 27, 2015 Share Posted January 27, 2015 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. Quote Link to comment Share on other sites More sharing options...
andym Posted January 27, 2015 Author Share Posted January 27, 2015 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 Quote Link to comment Share on other sites More sharing options...
ryanceot Posted October 1, 2020 Share Posted October 1, 2020 (edited) 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 October 1, 2020 by ryanceot Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted October 2, 2020 Share Posted October 2, 2020 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.