Having trouble with first external data file

[TotalChoiceNate]

Good afternoon!

I am having trouble with my first external data file and hoping someone can help. What I am needing to do is simply call office names from a data file and it isn't working. I've attached my rules below, any help is greatly appreciated!

// This is in "OnJobStart"

XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt"); //MasterDepartmentsListFinal.txt is housed in the same folder as the master PDF file.

return XDF.valid;

This is contained in a blank javascript text rule:

if (FusionPro.inValidation)
	Rule("OnJobStart");

primaryline1 = "";

for (i = 1; i < XDF.recordCount+1; i++)

{
	Department = XDF.GetFieldValue(i, 2);
		{
			if (Field("Department") == Department)
				{	
					var primaryline1 = XDF.GetFieldValue(i, 3);
				}
		}
}

return primaryline1;

The below text is the beginning of the External file, whereas the Template column corresponds to a Set page usage rule to control which template it is dropped in to, Full name is the name of the Office/Department that will be contained in a drop down list on our portal, and PrimaryLine1, PrimaryLine2 and Secondary Line1 correspond to values that need to populate the variable information inside of the template.

Template	Sequence	Full Name	PrimaryLine1	PrimaryLine2	Secondary Line1
1line	1	Division of Humanities 	Division of Humanities		
1line	2	Art and Art History Department 	Art and Art History Department		
1line	3	Chinese and Japanese Department 	Chinese and Japanese Department		
1line	4	Classics Department 	Classics Department		
1line	5	English Department 	English Department		
1line	6	French and Arabic Department 	French and Arabic Department		
1line	7	German Department 	German Department		
1line	8	Music Department 	Music Department		
1line	9	Philosophy Department 	Philosophy Department		
1line	10	Religious Studies Department 	Religious Studies Department		
1line	11	Russian Department 	Russian Department		
1line	12	Spanish Department 	Spanish Department		
1line	13	Theatre and Dance Department 	Theatre and Dance Department		
1line	14	Division of Science 	Division of Science		
1line	15	Biological Chemistry Department 	Biological Chemistry Department

If anyone has any idea of how to write a set page usage rule to set the active pages to the corresponding named pages in the template, that would be much appreciated as well! I'm sure I can figure it out once I understand the XDF rules, but that would skip a step for sure. Thank you in advance!

[Dan Korn]

Originally Posted by TotalChoiceNate

I am having trouble with my first external data file and hoping someone can help. What I am needing to do is simply call office names from a data file and it isn't working.

First a few pointers about using External Data Files:

1. As of FusionPro 10, you don't need to declare XDF files in OnJobStart. You can declare them where they're used, and they will be cached to prevent loading them multiple times.
2. You should almost always use field (column) names instead of numbers.
3. You should generally use FindRecord instead of iterating the entire XDF.

With these pointers in mind, the entire rule can be this:

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt");
var found = XDF.FindRecord("Full Name", Field("Department"));
return XDF.GetFieldValue(found, "PrimaryLine1");

However, there's one other wrinkle with your particular file, which is that you have some stray spaces around your field values, mostly after the department names in the third column ("Full Name"). For instance, the value of that field in the first record is "Division of Humanities ", with a space at the end, rather than "Division of Humanities" as you probably were expecting. This makes the FindRecord call fail to find the exact match. (You can set the property XDF.sortCaseInsensitive = true to do case-insensitive matching, but there's no built-in way to trim extra spaces before and after field values.) So in this case, you need to either (a) clean up the XDF to remove those extraneous spaces, or (b) revert to iterating the file and doing some cleanup of the values in the rule, which would look something like this:

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt");
var mainDepartmentName = Trim(ToLower(Field("Department")));
var found = -1;
for (var i = 1; i <= XDF.recordCount && found < 0; i++)
{
    var XDFDepartmentName = Trim(ToLower(XDF.GetFieldValue(i, "Full Name")));
    if (XDFDepartmentName == mainDepartmentName)
		found = i;
}
return XDF.GetFieldValue(found, "PrimaryLine1");

Note that I'm using both Trim and ToLower on the main and XDF field values to strip spaces and do case-insensitive comparisons.

Originally Posted by TotalChoiceNate

The below text is the beginning of the External file, whereas the Template column corresponds to a Set page usage rule to control which template it is dropped in to,

I assume that when you say "template" here, you mean that you're calling out a different page from a set of Unused Body pages, not really a different FusionPro VDP template (nor a repeatable component).

Originally Posted by TotalChoiceNate

Full name is the name of the Office/Department that will be contained in a drop down list on our portal,

Okay. You might want to make sure that's not putting out extra spaces as well.

Originally Posted by TotalChoiceNate

and PrimaryLine1, PrimaryLine2 and Secondary Line1 correspond to values that need to populate the variable information inside of the template.

Again here, I recommend being consistent in the field names. Why is there a space in the "Secondary Line1" field name but not in the others? Being consistent about such things will avoid problems down the line.

Anyway, if you want to do this all at once, without writing three separate rules to return the three different values, you can use "variable injection", by moving the code into OnRecordStart, and replacing the last "return" line with this:

if (found > 0)
{
    FusionPro.Composition.AddVariable("PrimaryLine1", XDF.GetFieldValue(found, "PrimaryLine1"));
    FusionPro.Composition.AddVariable("PrimaryLine2", XDF.GetFieldValue(found, "PrimaryLine2"));
    FusionPro.Composition.AddVariable("Secondary Line1", XDF.GetFieldValue(found, "Secondary Line1"));
}

Then you can insert any of those variables into the Text Editor, even though they don't appear in the Variables drop-down. (You need to type in the name then click Insert.)

Though, this can be generalized as well, to add all the fields from the XDF as text variables, like so:

if (found > 0)
{
    for (var i = 0; i < XDF.fieldCount; i++)
        FusionPro.Composition.AddVariable(XDF.GetFieldValue(0, i), XDF.GetFieldValue(found, i));
}

(Though the ability to do this without the "for" loop, and automatically add in all the XDF fields as text variables, would be a great enhancement to FusionPro. The ability to do this without writing any JavaScript code at all, i.e. to have a dialog where you could specify the XDF file, the "key" field name in the main data, and the field name in the XDF to match to, and to then have all the fields from the matching XDF record added as text variables, would be even better. This is on our list.)

Originally Posted by TotalChoiceNate

If anyone has any idea of how to write a set page usage rule to set the active pages to the corresponding named pages in the template, that would be much appreciated as well!

So again, I assume here that you mean you're calling out a different page from a set of Unused Body pages, not really a different FusionPro VDP template (nor a repeatable component). The easiest way to do this is to name the pages the same as the field values, set them all to Unused, and then just add this line to the OnRecordStart code:

FusionPro.Composition.SetBodyPageUsage(XDF.GetFieldValue(found, "Template"), true);

So putting this all together, the whole thing in OnRecordStart would be:

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt");
var mainDepartmentName = Trim(ToLower(Field("Department")));
var found = -1;
for (var i = 1; i <= XDF.recordCount && found < 0; i++)
{
    var XDFDepartmentName = Trim(ToLower(XDF.GetFieldValue(i, "Full Name")));
    if (XDFDepartmentName == mainDepartmentName)
		found = i;
}

if (found > 0)
{
    for (var i = 0; i < XDF.fieldCount; i++)
        FusionPro.Composition.AddVariable(XDF.GetFieldValue(0, i), XDF.GetFieldValue(found, i));
}

FusionPro.Composition.SetBodyPageUsage(XDF.GetFieldValue(found, "Template"), true);

Or, if you fix up the XDF to remove those extra spaces:

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt");
var found = XDF.FindRecord("Full Name", Field("Department"));
if (found > 0)
{
    for (var i = 0; i < XDF.fieldCount; i++)
        FusionPro.Composition.AddVariable(XDF.GetFieldValue(0, i), XDF.GetFieldValue(found, i));
}
FusionPro.Composition.SetBodyPageUsage(XDF.GetFieldValue(found, "Template"), true);

[TotalChoiceNate]

Thanks Dan! I will dive in to this and see where I end up, your expertise is very much appreciated!

[TotalChoiceNate]

Dropped the XDF in to indesign and it appears to be working like a charm, will update if I run in to any issues!

Thanks again so much!

[sdavenport]

Dan,

I really appreciate your detailed explanation. Your code is indicating that it is no longer necessary to define a delimiter, is that true?

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt" );

This is what the Building Blocks/Objects is asking for:

new ExternalDataFileEx(Filename [,Delimiter OR Type] [,encoding OR Excel sheet])

[Dan Korn]

Originally Posted by sdavenport

Your code is indicating that it is no longer necessary to define a delimiter, is that true?

Yes. As of FusionPro 9.3, you no longer have to specify the delimiter for a flat-file XDF. FusionPro will detect it in most cases, for typical delimiters such as tab (which I always recommend), comma, semicolon, pound sign (hashtag), or pipe (|). You probably do want to specify it if you know it, though, as any reduction in ambiguity is a good thing.

Originally Posted by sdavenport

This is what the Building Blocks/Objects is asking for:

new ExternalDataFileEx(Filename [,Delimiter OR Type] [,encoding OR Excel sheet])

Yes, the square brackets indicate optional parameters. So that second parameter can also be a data source type, such as "Excel", so that you can use an Excel worksheet as an XDF. You can also optionally specify a sheet name, in the third paramater, in case you have an Excel workbook with multiple sheets (tabs), and want to use something other than the first sheet (which is of course the default).

For a flat-file XDF, you can also specify an encoding for the file in the third parameter, as you can for most other plain-text resources. So for instance, if your file is in Mac Roman encoding, you can specify that. If the encoding is not specified, then the same logic is used to determine the encoding for the primary data file, which is that if there's a UTF-8 or UTF-16 marker, the file will be considered to have Unicode encoding, and if not, it will be considered to have Latin-1 on encoding Windows or Mac Roman encoding on Mac.

[sdavenport]

Thank you, Dan!

This Forum is part of what makes PTI such a great product!

[MartyrSyx]

Dan, thank you for the extended explanation of the XDF optional parameters (in addition to the rest of your response).

[TotalChoiceNate]

Follow up question on this. I am now needing to implement a rule on this template, that incorporates the "Change case selection of name field" to one of the XDF fields that are being pulled in to the template.

The problem that I am running in to, is when I write the rule to add the name field, it is giving me an error saying that the field doesn't exist (because it doesn't exist in the template, only in the XDF). Is there a way to write it in-line in the text box where the rule is to convert just that line to small caps?

Below is the rule set that I currently have:

My Rule Trying to convert "STPL1" field from my XDF to small caps:

// Rule converted from XML Template "Case selection for a name field Rule":
// Please specify a field and choose the appropriate case.
// Begin XML Template selections //
var Var1 = "STPL1"; // "Choose a field for the conversion to appropriate case." (Required): FieldList
var CaseSelection = "smallcaps"; // "Choose case.": PickList ["allcaps" (ALL CAPS), "smallcaps" (small caps), "propercase" (Proper Case or Title Case), "lowercase" (lower case)]
// End XML Template selections //



if(CaseSelection == "allcaps")
return ToUpper(Field(Var1));

if(CaseSelection == "smallcaps")
return "<smallcap>" + Field(Var1) + "</smallcap>";

if(CaseSelection == "propercase")
return ToTitleCase(Field(Var1));

if(CaseSelection == "lowercase")
return ToLower(Field(Var1));

And my "OnRecordStart" Rule:

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt" );
var found = XDF.FindRecord("Full Name", Field("Department"));
if (found > 0)
{
for (var i = 0; i < XDF.fieldCount; i++)
FusionPro.Composition.AddVariable(XDF.GetFieldValu e(0, i), XDF.GetFieldValue(found, i));
}

[Dan Korn]

I think all you need to do is change OnRecordStart to this:

var XDF = new ExternalDataFileEx("MasterDeparmentsListFinal.txt" );
var found = XDF.FindRecord("Full Name", Field("Department"));
if (found > 0)
{
    for (var i = 0; i < XDF.fieldCount; i++)
        FusionPro.Composition.AddVariable(XDF.GetFieldValue(0, i), XDF.GetFieldValue(found, i));

    var STPL1_small = "<smallcap>" + TaggedTextFromRaw(XDF.GetFieldValue(found, "STPL1"))) + "</smallcap>";
    FusionPro.Composition.AddVariable("STPL1", STPL1_small, true);
}

Then just use the "STPL1" variable directly in the Text Editor instead of "Case selection for a name field Rule".

P.S. When posting code, first click the "Go Advanced" button, then use the # button for a Code block instead of a quote.