JeremyT Posted April 22, 2013 Share Posted April 22, 2013 I am trying to use an external data file to find a store address based on a customer list. Store address will be the return address on a postcard. I an unsure how to create a rule that uses the store number from each record on customer list to find the correct store address from the external data file. Thanks, Jeremy Quote Link to comment Share on other sites More sharing options...
esmith Posted April 22, 2013 Share Posted April 22, 2013 In your scenario, I think Thomas' solution would probably be satisfactory: OnJobStart // the "\t" indicates how data is separated (tabbed) // Could also be \c for comma-separated externalDF = new ExternalDataFileEx('path/to/yourExternalDataFile.format', '\t'); Your text rule // first line initiates connection to external data during validation of code if (FusionPro.inValidation) {Rule("OnJobStart");} result = ""; /* * store record number in external data where * Value (matching store number of your data) * in external data's FieldName matches * note that FieldName is name of field in external Data * example: * var extRecordNumber = externalDF.FindRecord(Field("Store Address"), Field("Store Number")); * where Field("StoreNumber") is the store # value from your current record */ var extRecordNumber = externalDF.FindRecord(FieldName, Value); /* * if a record is found with matching store number * return value of address field(s) * note that "AddressField" is the name of the field * in external data which contains the information you want */ if (extRecordNumber>0) { result = externalDF.GetFieldValue(extRecordNumber, "AddressField"); } return result; Quote Link to comment Share on other sites More sharing options...
JeremyT Posted April 22, 2013 Author Share Posted April 22, 2013 Eric, Thanks for the help. A couple of more questions: Now that I can return the store address from external file, I need to return City, State and Zip also. Do I have to set up separate rules for each field or can it be combined? I discovered that my customer list has store numbers as single digits. My store address list has the store numbers as 3 digit numbers. i.e. 009 . Is there code that can be written to make single digit store numbers from customer list 3 digits? Thanks, Jeremy Quote Link to comment Share on other sites More sharing options...
esmith Posted April 22, 2013 Share Posted April 22, 2013 Now that I can return the store address from external file, I need to return City, State and Zip also. Do I have to set up separate rules for each field or can it be combined? result = externalDF.GetFieldValue(extRecordNumber, "AddressField"); result += "<br />" + externalDF.GetFieldValue(extRecordNumber, "CityField"); result += ", " + externalDF.GetFieldValue(extRecordNumber, "StateField"); result += " " + externalDF.GetFieldValue(extRecordNumber, "ZipField"); I discovered that my customer list has store numbers as single digits. My store address list has the store numbers as 3 digit numbers. i.e. 009 . Is there code that can be written to make single digit store numbers from customer list 3 digits var extRecordNumber = externalDF.FindRecord(FieldName, FormatNumber("000",Value)); Quote Link to comment Share on other sites More sharing options...
JeremyT Posted October 31, 2013 Author Share Posted October 31, 2013 How do I return the only the first 5 digits of the Zip? Quote Link to comment Share on other sites More sharing options...
JeremyT Posted October 31, 2013 Author Share Posted October 31, 2013 Zip is field in external data file. Quote Link to comment Share on other sites More sharing options...
Dan Korn Posted October 31, 2013 Share Posted October 31, 2013 How do I return the only the first 5 digits of the Zip? Zip is field in external data file. Something like this: result += " " + Left(externalDF.GetFieldValue(extRecordNumber, "ZipField"), 5); Quote Link to comment Share on other sites More sharing options...
JeremyT Posted October 31, 2013 Author Share Posted October 31, 2013 Thank you Dan-exactly what I needed! Another question - I've discovered that some of the records have an extra space at the end of the Address field. Is there a way to remove the extra space at the end? Only some of the records have that extra space so I can't remove a character from all. Address is in the external data file. Thanks, Jeremy Quote Link to comment Share on other sites More sharing options...
step Posted October 31, 2013 Share Posted October 31, 2013 result = Trim(externalDF.GetFieldValue(extRecordNumber, "AddressField")); 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.