Jump to content

External data file


JeremyT

Recommended Posts

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

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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));

Link to comment
Share on other sites

  • 6 months later...

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

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...