Multiple output records per input record with unique variables

[esmith]

SOLVED: See post 8 for solution.

Let's say I have a recurring job where each month I have 4 speakers presenting a seminar, and each seminar can accommodate 5 special guests and 10 regular guests. Guests must have a pre-printed pass to attend. Each speaker has a unique room assignment and a presentation date.

January Speakers (name, room, date):
1: Alice, Ballroom, 1/12/09
2: Barry, Conference 1, 1/16/09
3: Carol, Conference 2, 1/23/09
4: David, Basement, 1/31/09

February (name, room, date):
1: Edgar, Room 324, 2/3/09
2: Betsy, Penthouse, 2/12/09
3: Charles, Cafeteria, 2/19/09
4: Debbie, Lounge, 2/24/09

Pass IDs (ID):
SP1, SP2, SP3, SP4, SP5, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

My thought process is that I would have a FP template linked to an ExternalDataFile, "PassIDs",containing a field called "ID" which contains 15 records and is used month to month. The data file I link to in the "Define Data Source" dialog would be a separate data file containing the 4 speaker records. I would link to a new data source for this information each month.

When I compose records, I want FP to generate 15 output files for each speaker. Each file would contain the speaker name, the room assignment, the presentation date, and the pass ID.

I currently have a template set up that correctly generates an output file for each speaker with name, room and date. What I can't get working is the ability to pull data from the external "Pass IDs" data to generate multiple files for each speaker.

I have the following code in JavaScript Globals:

var filename = "/Users/esmith818/SpeakerPasses/PassIDs.csv";
ID_Numbers = new ExternalDataFileEx(filename, ",");

I have the following in a text rule:

var Desc = ID_Numbers.FindRecord(Field("ID"),"ID");
return ID_Numbers.GetFieldValue(Desc, "Description");

When I compose, I get the following error for all speaker records:

RULE_TextRule, line 1: Error: In Field(), no field named ID
Composing record #1, input record 1
Sheet #1, record #1
Value for variable RULE_TextRule not found in instance data
Word <{RULE_TextRule}> does not fit in frame after line 0.
The amount of text inserted into a flow exceeds the depth
of all frames in the flow <ID Number>. Text is truncated.
Text does not fit in the last frame on page 1 at (-0.12, 6.40).

I see that if I use a field name in the defined data source "January" (ie "room"), the code validates, but it does not validate for "ID" from "PassIDs" data. I'm assuming I am not calling the External Data Source correctly, or not accessing it's data by the correct method to achieve my desired results.

Would it be possible to explain the code needed to accomplish this on the forums or do I need to get support involved? I like to think I'm savvy enough to understand the JavaScript scripting, but I can't seem to make the leap from theory to application on this one.

[DSweet]

Hi'ya esmith,

I'm not sure if this is what your problem is, or why it doesn't recognize your search data field, but is there a field called "ID" in BOTH of your data files - the external and the dynamic file? You have the statement ...

var Desc = ID_Numbers.FindRecord(Field("ID"),"ID");
return ID_Numbers.GetFieldValue(Desc, "Description");

in this you're trying to use two fields from different data files with the same header name "ID". I'm not sure if FusionPro can keep them straight and is confused about which field entry is being called for which entry. I have used external data files before with success, but I've always kept the "linking" and "searching" fields unique names.

Try renaming one to be "searchID" or "speakerID" or something unique in your external file and rename the proper codes in your rules to reflect this.

Good Luck.

[esmith]

Originally Posted by DSweet

I'm not sure if this is what your problem is, or why it doesn't recognize your search data field, but is there a field called "ID" in BOTH of your data files - the external and the dynamic file?

Thanks for the response DSweet. I do not have common field names in my two data files, but I likely have a misunderstanding of what arguments each function is looking for in the code.

Perhaps I need to first know what A-D represent in the following code:

var Desc = ID_Numbers.FindRecord(A,B);
return ID_Numbers.GetFieldValue(C,D);

I'm under the impression that A is the record you are calling from the external data source and B is the name of the field in the external file that you are pulling (in which case "Field("ID") should be in the B position and I'm not sure what to put in the A position since I want to produce an output record for each record in the external data source per single record in the default data file).

In the second line of code, C would be the cell we navigated to in the previous line and D is the variable we are assigning the value to.

Is that right? If so, I would want to return that value to my variable text block and create a loop to produce multiple output records for each line in the external data file before moving on to the next record in the default data file.

I keep thinking that my logic is close, but I can't figure out my next step. Thanks for the help.

[Dan Korn]

Originally Posted by esmith

Perhaps I need to first know what A-D represent in the following code:

var Desc = ID_Numbers.FindRecord(A,B);
return ID_Numbers.GetFieldValue(C,D);

I'm under the impression that A is the record you are calling from the external data source and B is the name of the field in the external file that you are pulling (in which case "Field("ID") should be in the B position and I'm not sure what to put in the A position since I want to produce an output record for each record in the external data source per single record in the default data file).

In the second line of code, C would be the cell we navigated to in the previous line and D is the variable we are assigning the value to.

According to the FusionPro Rules System Guide, the first parameter (A) to ExternalDataFileEx.FindRecord is a Field Name or Number, and the second parameter (B) is the Value to be matched (found). So I think your logic needs to be:

var Desc = ID_Numbers.FindRecord("ID", Field("ID"));

Where "ID" is the name of the field in the external file, and Field("ID") is the value (something like "SP1") from your main data file (which happens to come from a field also named "ID" in the main data) that you're trying to match. If the field in the external data file is not called "ID", then you need to change the first parameter (A) to whatever the field name is.

That said, since you didn't post any of the contents of the files in question, I can't tell you for sure whether all your logic is correct or not, or where it's going wrong, or where it might go wrong under certain conditions. You need to figure out what all the failure points are and trace them in the code. In other words, you need to add some debugging logic to solve this problem. There are two main ways to do this: Use the Validate button on the Rule Editor dialog and add extra "return" statements, or call the Print function to write to your composition log (.msg) file. I'll use the second method as an example here.

What's the most likely failure? I would guess that it's that the external file was not even read in. So first, let's do this in OnJobStart:

var filename = "/Users/esmith818/SpeakerPasses/PassIDs.csv";
ID_Numbers = new ExternalDataFileEx(filename, ",");
if (!ID_Numbers.valid)
  Print("Unable to open ExternalDataFileEx: " + filename);
else
  Print("Read " + ID_Numbers.recordCount + " records from file: " + filename);

You can see where this is going. Next, let's add some debugging to your regular text rule, which I assume is (somewhat redundantly) named "RULE_TextRule":

var found_record = ID_Numbers.FindRecord("ID", Field("ID"));
if (found_record < 0)
  Print("No record found in external file for ID: " + Field("ID"));
var result = ID_Numbers.GetFieldValue(found_record, "Description");
if (!result)
  Print("No value for Description found in external file for record: " + found_record);
return result;

You should be able to compose and then look in the composition log file to see what, if anything, went wrong.

Originally Posted by esmith

Is that right? If so, I would want to return that value to my variable text block and create a loop to produce multiple output records for each line in the external data file before moving on to the next record in the default data file.

Well, no, this logic is not right for returning multiple records. The ExternalDataFileEx.FindRecord function only finds the first record in the external data file which matches the specified field value. If you want to return multiple records from the external data file, you'll need to implement a loop to find all of them. I think you want to do something like this (again, I'm making some guesses about field names and such since you didn't post any example data):

var result = "";
for (var i = 1; i <= ID_Numbers.recordCount; i++)
{
  if (ID_Numbers.GetFieldValue(i, "ID") == Field("ID"))
  {
    result += ID_Numbers.GetFieldValue(i, "Name") + ", " + 
    ID_Numbers.GetFieldValue(i, "Location") + ", " +
    ID_Numbers.GetFieldValue(i, "Date");
  }
}
return result;

Please refer to these threads for more information:
http://forums.printable.com/showthread.php?t=534
http://forums.printable.com/showthread.php?t=388

[esmith]

Thanks for the response Dan. You seem to have a touch of sarcasm in your voice

(somewhat redundantly) named "RULE_TextRule"

but that's OK as it means my sarcastic personality should fit well with a coder's mentality.

I attempted to provide sample data in my OP ('January' and 'February', each with 4 records and 3 fields -- name, room, date AND 'PassIDs' with just one field -- ID). My setup does not currently include a common field since I want to be able to use the PassID external data source for every record of my default data file (and for every default data file I will re-link to from month-to-month).

I may be able to figure out how to get from here to there using the code you provided in your previous post, but if you are able to offer more precise code using the sample data above, it would be greatly appreciated.

In the end, I need January's output file to contain 60 pages (4 speakers * 15 passes). In February, I want to be able to replace the default data file with 'February', recompose, and have a new output file with 60 pages (same as January, but with new speaker data in output).

Is that as clear as mud?

[Dan Korn]

Originally Posted by esmith

I attempted to provide sample data in my OP ('January' and 'February', each with 4 records and 3 fields -- name, room, date AND 'PassIDs' with just one field -- ID).

Do you mean this?

Originally Posted by esmith

January Speakers (name, room, date):
1: Alice, Ballroom, 1/12/09
2: Barry, Conference 1, 1/16/09
3: Carol, Conference 2, 1/23/09
4: David, Basement, 1/31/09

It's not clear at all to me whether that's supposed to represent the contents of the external data file or the output. If it's the data file, seeing the actual header row would be extremely illuminating, especially since getting the exact field names in the right places seems to be a point of confusion. Where is the "ID" field?

Originally Posted by esmith

I may be able to figure out how to get from here to there using the code you provided in your previous post, but if you are able to offer more precise code using the sample data above, it would be greatly appreciated.

My answers can only be as specific as your questions.

Originally Posted by esmith

In the end, I need January's output file to contain 60 pages (4 speakers * 15 passes). In February, I want to be able to replace the default data file with 'February', recompose, and have a new output file with 60 pages (same as January, but with new speaker data in output).

Again, if you can provide a sample, just the first few lines, of one of the external data files, and of your main input file, and a short example of what you expect in the output (maybe even a mockup in PDF or a JPG, you know, worth 1000 words), it will make it much easier for me to help.

Originally Posted by esmith

Is that as clear as mud?

Yes, that's why we work so closely with Adobe.

[esmith]

Sigh. That's what I get for trying to use a made up example instead of live data...

I am attaching three data files, a FP template, and a mock output file.

PassID.txt is intended to be my ExternalDataFileEx file that never changes from month to month. January-Speakers.txt is my initial default data file. The template should be set up so that this data file can be updated each month with new data (such as February-Speakers.txt attached). Speakers.pdf is my template in FusionPro. When composing, the resulting output file should resemble speakers-output.pdf (although I set this one up manually in InDesign).

I recognize that I do not have a "key" field that links PassId.txt with January-Speakers.txt, but that is because I need the data in PassID.txt to be applied to all records in January-Speakers.txt which is how the output file generates 15X more records than the number of records in the default data file. Since the number of speakers may change month to month, I can not simply add a field "Speaker" and duplicate the 15 current records in PassID.txt for each speaker in the default data file.

If the attached template does not include my rules and you need to see them, let me know and I will add them in another post. They are mainly what was added above, except file and variable names may have changed when trying to reverse engineer my example to create actual files.

In the end, I was hoping I could run a loop in an OnRecordStart callback rule that would loop 15X and generate one output record for each record in the ExternalDataFile before moving on to the next record in the default data file.

At this point, I admit I'm getting frustrated with trying to explain what I thought would be easy if I had a better grasp on JavaScript, but perhaps it's more complicated than I thought or I stink at trying to explain my thought process. (Thanks for your patience!)

[Dan Korn]

Okay, sorry, I was responding to your post in response to DSweet about how to get the syntax of ExternalDataFileEx right. After going back to your original post and looking at the actual files you posted, I think I now finally understand the bigger picture of what you're ultimately trying to accomplish, and I can tell you that the ExternalDataFileEx object is not even the right tree to be barking up, so to speak.

In older versions of FusionPro, if you wanted to take a particular page, or part of a page, and repeat it an arbitrary number of times in your output, you would have to use either repeatable components (sometimes called "templates," although that term is very ambiguous these days) or a table, along with Overflow pages, and then have a rule that returned all the multiple instances of each repeated bit as either template markup or table rows. Or you would have to use a convoluted workaround which involved rewriting the input file. All of these are certainly doable (it's been done many times, by myself and others), but not terribly straightforward (although repeatable components are easier to work with in FusionPro 6.0).

However, since you're using FusionPro 6.0, there's a much easier way to do all this, using repeated records. Just create the OnRecordStart callback rule and add this syntax:

FusionPro.Composition.repeatRecordCount = 15;
var passNum = FusionPro.Composition.repeatRecordNumber;
var passID = (passNum <= 10) ? FormatNumber("000", passNum) : "SP" + passNum % 10;
FusionPro.Composition.AddVariable("PassNumber", passID);

Then change the text frame to use the "PassNumber" variable, change the main input data file to January-Speakers.txt, and compose. You can delete the other rule and the contents of your JavaScript Globals. Those few lines in OnRecordStart are all you need. Pretty cool, huh?

Note that there's no mapping required here, thus no need for ExternalDataFileEx. If you want to use any arbitrary set of pass IDs, then I suppose you could read them from a secondary data file, but they seem to follow a pattern which is simple enough to generate from the algorithm in the code above. Or you could just hard-code your set of arbitrary IDs in an array right in your rule, like so:

var PassIDs = [ "one", "two", "foo", "bar", "whatever" ];
FusionPro.Composition.repeatRecordCount = PassIDs.length;
var passNum = FusionPro.Composition.repeatRecordNumber;
var passID = PassIDs[passNum-1] || passNum;
FusionPro.Composition.AddVariable("PassNumber", passID);

The other thing that you might want to do, but I'm not sure, is programmatically change the input file you're using based on the current month, in which case you could do something like this in OnJobStart:

FusionPro.Composition.inputFileName = FormatDate(Today(), "lm") + "-Speakers.txt";

Note that the full path is not required as long as the input file is in the same folder as your template PDF document.

Does this answer your question?

[esmith]

Thanks Dan. I will try this later today and report my results. While the first code box surely works for my example, I think the second option will work better for my real world scenario. The auto-naming of the input file may not be practical for this particular job, but you can bet I'll be incorporating that into another job that has a daily input file that can easily be named with the current date.

In an effort to better understand the code, could you explain a couple of lines from your preceding snippets? There are 3 things I'd like to understand better:

1) I can't seem to find the repeatRecordNumber property in my Rules System Guide. Am I correct in assuming that it just tells FP to repeat the current record X number of times where X is defined by the preceding repeatRecordCount property?

2) I'm not seeing how line 3 (as cool and concise as it is) of the first code box is reproducing the 15 PassIDs from my sample data file.

3) In the 2nd snippet, I think I understand how passID is being assigned a variable from the passIDs array (since the first item has an index of 0), but I don't understand the purpose of the double pipes or the trailing passNum. What is "|| passNum" doing?

In addition to being grateful for the solutions you offer at no charge, I really like to know how the code works which makes it easier for me to reapply the logic in future jobs. Thanks again for taking the time to help.

[Dan Korn]

Originally Posted by esmith

1) I can't seem to find the repeatRecordNumber property in my Rules System Guide. Am I correct in assuming that it just tells FP to repeat the current record X number of times where X is defined by the preceding repeatRecordCount property?

Unfortunately, our documentation has not kept up with recent feature additions to FusionPro. However, we do issue release notes, both on our release page and on this forum. The repeat record functionality, as well as other new features, is detailed in the release notes for FusionPro 6.0, which you can download from here:
http://printable.com/downloads/fusionpro/

The addendums to the User Guide and Rules System Guide for FusionPro 6.0 which document the new features can also be found here:
http://forums.printable.com/showthread.php?t=329

The properties of the FusionPro.Composition object related to record repeats are also described in the Building Blocks dialog, on the "JS Language" tab (in the tree view, JS Language > Objects > FusionPro > Composition > RecordNumbers; and yes, that whole dialog needs to be reorganized to make things easier to find).

That said, one of the challenges in documenting any kind of tool set (which is what FusionPro basically is, a set of tools to help you build something, specifically VDP output) is to document not just what each particular tool does, but to explain how the tools can be used together to accomplish specific goals. I can walk into Home Depot and I know what a hammer does, and a drill, and a router, and all kinds of other tools, but that knowledge doesn't mean that I know how to use those tools to build a house. Sometimes the people who make the tools don't even think of all the possible ways to use them. In a toolkit as rich as what FusionPro offers, there are often several ways to accomplish a particular goal, and we at Printable haven't necessarily even thought of all of them, and even if we did, the documentation would be hundreds of pages long. One of the purposes of this forum is for users to share their own creative ways of using FusionPro and putting features together in new ways. (Okay, that's enough philosophizing from me, or this post is going to be hundreds of pages long.)

Originally Posted by esmith

2) I'm not seeing how line 3 (as cool and concise as it is) of the first code box is reproducing the 15 PassIDs from my sample data file.

Sure, I'll break it down:

var passID = (passNum <= 10) ? FormatNumber("000", passNum) : "SP" + passNum % 10;

First, we're using the conditional operator (condition ? expr1 : expr2) as a shortcut for a compound if/then/else statement:
https://developer.mozilla.org/en/Cor...ional_Operator

If the number is less than or equal to ten, then we return it, padded to three digits with leading zeroes (001 through 010). If not, then we take the modulus of ten, which is the remainder left over when dividing the number by ten, and prepend "SP" to that:
https://developer.mozilla.org/en/Core_JavaScript_1.5_Reference/Operators/Arithmetic_Operators#.25_(Modulus)

You could write that same line thusly:

var passID = "";
if (passNum <= 10)
{
  passID = passNum.toString();
  while (passID.length < 3)
    passID = "0" + passID;
}
else
{
  var divResult = parseInt(passNum / 10);
  passID = "SP" + (passNum - (divResult * 10));
}

But the one-line version is just as valid, much more concise, and (to me) much more straightforward.

Originally Posted by esmith

3) In the 2nd snippet, I think I understand how passID is being assigned a variable from the passIDs array (since the first item has an index of 0), but I don't understand the purpose of the double pipes or the trailing passNum. What is "|| passNum" doing?

That's this line:

var passID = PassIDs[passNum-1] || passNum;

Yes, as you figured out, the reason to subtract one in the index to the array is because, unlike humans who start counting at one (and the FusionPro.Composition.repeatRecordNumber property), computers start counting at zero, which is called zero-based, so the first item in a JavaScript array is at index zero.

Now, to answer your question, the double pipes is the "OR" operator:
https://developer.mozilla.org/en/Cor...ical_Operators

It's ostensibly a Boolean operator, which would return either true or false, but in JavaScript, as the link above notes, the && (and) and || (or) operators have a special quality that they don't have in other similar languages (such as C/C++) where they actually return one of the operands (values) being evaluated. You can take advantage of this to take some handy shortcuts. For instance, these two snippets of code are equivalent:

if (Field("Title"))
  return Field("Title");
else if (Field("Nickname"))
  return Field("Nickname");
else if (Field("Name"))
  return Field("Name");
else
  return "No Name!"

and:

return Field("Title") || Field("Nickname") || Field("Name") || "No Name!";

Basically, the OR operator returns the first value which can be converted to "true", which applies if it's not zero, an empty string, null, or undefined. For arrays, if you specify an index beyond the bounds of the array, then the array index operator [] returns undefined. So in our statement, if the array index is not valid, then the index operator returns an undefined value, and the OR operator uses the next value, which defaults to the plain old pass (repeat) number. Of course, since in my example we're setting the upper limit of the repeat record count to the length of the array, the index will never be out of the array's bounds, but if you changed that second line and set the repeat count to some other value, then this whole default value with the OR operator would come into play. The intent of the FusionPro.Composition.repeatRecordCount property was that, most of the time, the repeat count would vary by input record, and would not be hard-coded as in this example, although this usage is perfectly valid.

Here's a blog post on another site which talks more about this feature of the "OR" operator:
http://blog.niftysnippets.org/2008/0...werful-or.html

Originally Posted by esmith

In addition to being grateful for the solutions you offer at no charge, I really like to know how the code works which makes it easier for me to reapply the logic in future jobs. Thanks again for taking the time to help.

I'm happy to help users learn how to use JavaScript effectively to accomplish their goals in their VDP jobs. Teach a man to fish, etc. Hopefully you will stick around on the forum and share knowledge with other users.