PDA

View Full Version : External data file


JeremyT
April 22nd, 2013, 07:41 AM
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

esmith
April 22nd, 2013, 09:12 AM
In your scenario, I think Thomas' solution (http://forums.pti.com/showpost.php?p=10537&postcount=2) 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;

JeremyT
April 22nd, 2013, 12:37 PM
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

esmith
April 22nd, 2013, 12:49 PM
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));

JeremyT
October 31st, 2013, 10:34 AM
How do I return the only the first 5 digits of the Zip?

JeremyT
October 31st, 2013, 10:38 AM
Zip is field in external data file.

Dan Korn
October 31st, 2013, 10:45 AM
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);

JeremyT
October 31st, 2013, 12:13 PM
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

step
October 31st, 2013, 12:15 PM
result = Trim(externalDF.GetFieldValue(extRecordNumber, "AddressField"));