Jump to content

Secondary Data File


TroyM68

Recommended Posts

I've read a few posts on the board with regard to the use of multiple data files... I guess I have several questions and I'm looking for some best practices.

 

I have a customer that is trying to take as much human error out of variable data inside a business card. Best definition would be a set of static information (complete address) based on what they choose in two fields ("Affiliated Group" and "Department")

 

I know this could be done in a rule, but the thought of typing out all the possible combinations in a rule makes my stomach hurt.

 

In a perfect world I would like for the end user to select their group affiliation, from a drop-down list, and depending what they choose would provide a list of department options (from a drop-down list). When they choose their department... a lookup into data source #2 will select the correct physical address and populate the "Street Address", "City", "State" & "Zip Code" Fields currently located in data source #1.

 

If a sublist according to the group affiliation selection cannot be made... then it is fine for the end user to scroll thru all the departments a choose their department. Then of course those two fields would be used to grab the address information from the 2nd data source.

 

I hope this isn't confusing.

 

My question is.... Can this be done.... and How would I do it.

 

Thanks in advance.... please let me know if any more information is needed!

 

Troy

Link to comment
Share on other sites

In a perfect world I would like for the end user to select their group affiliation, from a drop-down list

Okay, so I have to stop you there and ask: What drop-down list? Where? In some kind of web-to-print application, such as MarcomCentral or EFI Digital StoreFront? Or some other custom app? Or are you trying to build an app of some kind with a data entry form?

Link to comment
Share on other sites

Thanks Dan, this customer is using WebCRD. With that in mind...I believe there would have to be modifications made on the WebCRD side (ability to limit the # of departments by the group selection).

 

So, I know I can do this by a simple rule like:

 

if ((Field("Affiliation") == "***") && (Field("Department") == "YYY"))

{

return "<span>" + RawText("9876 S. Street Name Rd. + <br> + AnyCity, ST 34567") + "</span>";

}

 

return "";

 

But, I believe this would be easier by using the 2nd data file which includes 8 fields (1 affiliation field, 3 departmental fields and 4 address fields).

 

My question is ~

How would I write a rule that would go to the second data source look for the affiliation field and the department field, then return the address associated with that specific combination.

Link to comment
Share on other sites

What version of FusionPro are you using?

 

In FP9, you can filter an external data file by sorting on field names and finding records with specified values for the sorted field names:

var ex = new ExternalDataFileEx('/path/to/secondaryData.csv', ',');
var cursor = ex.SortBy('Affiliation', 'Department');
var rec = cursor.FindRecords('***', 'YYY');
return ex.GetFieldValue(rec, 'Address');

The above code returns the value of the "Address" field for the record in "secondaryData.csv" with a value of "***" in the "Affiliation" field and a value of "YYY" in the "Department" field.

 

Assuming that you'll want the values of "Affiliation" and "Department" in the external data file to correspond to the values of identically named fields in your primary data file, you could do this:

var ex = new ExternalDataFileEx('/path/to/secondaryData.csv', ',');
var cursor = ex.SortBy('Affiliation', 'Department');
var rec = cursor.FindRecords(Field("Affiliation"), Field("Department"));
return ex.GetFieldValue(rec, 'Address') + '<br>' + ex.GetFieldValue(rec, 'City') + ', ' + ex.GetFieldValue(rec, 'St') + ' ' + ex.GetFieldValue(rec, 'Zip');

 

Or make a global function to clean it up a little:

function ExField(field) {
 var ex = new ExternalDataFileEx('/path/to/secondaryData.csv', ',');
 var fields = ['Affiliation', 'Department'];
 var cursor = ex.SortBy.apply(ex, fields);
 var rec = cursor.FindRecords.apply(cursor, fields.map(Field)).shift() || -1;
 return ex.GetFieldValue(rec, field);
}

Then your rule could just be:

return ExField('Address') + '<br>' + ExField('City') + ', ' + ExField('St') + ' ' + ExField('Zip');

Link to comment
Share on other sites

I've never played with the global functions. Please see below.

 

These are the fields of the Secondary Data File:

 

GL Number, GL Name, Department, Street, City, State, Zip, Location

 

I've decided that the only thing I need, to populate everything the way I want it, is the GL Name field. Do I have the syntax correct?

 

function ExField(field) {

var ex = new ExternalDataFileEx('/path/to/secondaryData.csv', ',');

var field = ['GL Name'];

var cursor = ex.SortBy.apply(ex, field);

var rec = cursor.FindRecords.apply(cursor, fields.map(Field)).shift() || -1;

return ex.GetFieldValue(rec, field);

}

 

I appreciate your help on this....Also, to understand "Global Functions" better... Is this the "Java Functions" at the bottom of the Rule Editor window?

 

Troy

Link to comment
Share on other sites

I've decided that the only thing I need, to populate everything the way I want it, is the GL Name field.

Do you mean there's a field named "GL Name" in you primary data file, and that you want to match it to a field with the same name in your secondary data file? If so, then this is close:

Do I have the syntax correct?

 

function ExField(field) {
 var ex = new ExternalDataFileEx('/path/to/secondaryData.csv', ',');
 var field = ['GL Name'];
 var cursor = ex.SortBy.apply(ex, field);
 var rec = cursor.FindRecords.apply(cursor, fields.map(Field)).shift() || -1;
 return ex.GetFieldValue(rec, field);
}

But I don't know why those "apply" calls are in there, though. You can just call the functions directly. Also, if you are only searching by a single field, you don't need the cursor, and you don't need the FindRecords if you only want to find the first match. And, you shouldn't need the full path to the file if it's with your other job files (like your primary input file) or in your Search Path (as set on the Advanced tab of the Composition Settings.) This should work too:

function ExField(field) {
 var ex = new ExternalDataFileEx('secondaryData.csv', ',');
 var rec = ex.FindRecord('GL Name', Field('GL Name'));
 return ex.GetFieldValue(rec, field);
}

I appreciate your help on this....Also, to understand "Global Functions" better... Is this the "Java Functions" at the bottom of the Rule Editor window?

No, there is no "Java Functions" button. It's the "JavaScript Globals" button. Although the function can go in the rule you're calling it from too.

Link to comment
Share on other sites

But I don't know why those "apply" calls are in there, though. You can just call the functions directly.

 

The reason I used "apply" was to be able to use the same array of field names for both the "SortBy" and the "FindRecords" functions. It seemed more manageable when altering which field names by which you'd want to search as opposed to remembering to change the code in both places. Now admittedly, it seems a tad "overkill" for only two values (even more-so now that Troy says he's only keying off of one field value) but to me it seems a little less repetitive to only have to enter the field names once.

 

The reason that the function no longer works for Troy is because he renamed the "fields" array to "field" – which is the name of the argument being passed to the function. He left the reference to "fields" when defining the "rec" variable but it's no longer defined:

function ExField([color="Red"]field[/color]) {
 var ex = new ExternalDataFileEx('/path/to/secondaryData.csv', ',');
 var [color="red"]field[/color] = ['GL Name'];
 var cursor = ex.SortBy.apply(ex, field);
 var rec = cursor.FindRecords.apply(cursor, [color="red"]fields[/color].map(Field)).shift() || -1;
 return ex.GetFieldValue(rec, field);
}

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