I'm Having Trouble with merging multiple data tables

[Landisa]

====================
[UPDATE] January 14, 2016
====================
Solution has been found (by Step) and a cleaner code was also provided.
I am leaving this thread as it is so others can get help with similar problems. Attachments have been removed.

=====================
[ORIGINAL] January 13, 2016
=====================
We have a request to merge one user's letter with their property tables. These tables will vary in quantity per user.

Currently I am able to populate the data tables and create an overflow.
However, it seems that the first letter for "Johnny" works out and he gets what he is supposed to have. Then the second letter for "Beth" ends up with Johnny's table and both of her tables. The problem continues on from there.

I believe it has to do with the array the data is being stored into and how we are calling it to run. Can someone look over the code and see how I get the tables to stay with their owners and not continue on to the next owner?

Since the form is for our state government I will be unable to post the original for download. However, I have attached a sample build and test resources used to build the item.

Please keep in mind that this is a very large mailing. If there is an issue with memory recall on larger items let me know.

[step]

Can you post the rule in question or upload your template in a way that's compatible with FusionPro 8? Unfortunately, I am unable to view your file since I'm using an older version of FusionPro than you are.

That being said, it sounds to me as if you're defining your array in OnJobStart (or JavaScript Globals) – essentially creating it once and pushing data into it per record. Does the third record's table have the contents of the first, second, and third record in it? If so, the solution could be as simple as resetting your array at the beginning of each record:

var yourArray = []; // set yourArray to an empty array

// code
yourArray.push('your content');

Or more specifically to your scenario:

var data = new ExternalDataFileEx('./property.txt','\t');
var yourArray = [];

for (var i=1; i<=data.recordCount; i++)
    if (Field("Customer ID") == data.GetFieldValue(i, 'CID'))
        yourArray.push(i);

[Landisa]

To answer your question, yes. The third letter has the first and seconds tables as well as its own tables.

If I knew how to save it in a backwards compatible mode I would do that.
However, I have attached the other two rules and will post OnRecordStart Rule below. Some formatting has been snipped in order to post (it was too large).

OnRecordStart:

//The following if statement will detect if we are currently in preview mode or editing this rule (versus composing output).  
if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)
{
    Rule("OnJobStart");
}

// Create empty
var ArraySize = 0;
var tables = [];

//Get a count of the total number of records in the external data file
NumberOfRecords = data.recordCount;

//Now, loop through all records in the external data file and find the records that belong to the customer.
for (var i = 1; i <= NumberOfRecords; i++)
{
    if (data.GetFieldValue(i, "CID") == Field("Customer ID"))
    {
        //Create Table
        var myTable = new FPTable;
        myTable.AddColumns(5825, 5400, 27200, 7200, 8100);
        myTable.AddRows(15);
   
        myTable.Rows[0].Cells[3].HStraddle = 2;
        myTable.Rows[1].Cells[0].HStraddle = 2;
        myTable.Rows[1].Cells[2].VStraddle = 2;  
        myTable.Rows[1].Cells[3].VStraddle = 2;
        myTable.Rows[1].Cells[4].VStraddle = 2;

        myTable.Rows[0].Cells[0].PointSize=10;
        myTable.Rows[1].Cells[0].PointSize=10;
        myTable.Rows[2].Cells[0].PointSize=8;
        myTable.Rows[3].Cells[0].PointSize=10;
        myTable.Rows[4].Cells[0].PointSize=10;
        myTable.Rows[5].Cells[0].PointSize=10;
        myTable.Rows[6].Cells[0].PointSize=10;
        myTable.Rows[7].Cells[0].PointSize=10;
        myTable.Rows[8].Cells[0].PointSize=10;
        myTable.Rows[9].Cells[0].PointSize=10;
        myTable.Rows[10].Cells[0].PointSize=10;
        myTable.Rows[11].Cells[0].PointSize=10;
        myTable.Rows[12].Cells[0].PointSize=10;
        myTable.Rows[13].Cells[0].PointSize=10;
        

        //Title Row
        myTable.Rows[0].SetContents("County", data.GetFieldValue(i, "County"), "Property ID", data.GetFieldValue(i, "PropertyID"), ""); 

        //Header Rows
        myTable.Rows[1].Cells[0].Content = "Head Count";
        myTable.Rows[1].Cells[2].Content = "Livestock Type";
        myTable.Rows[1].Cells[3].Content = "Fee Amount Per Head";
        myTable.Rows[1].Cells[4].Content = "Per Capita Fee Calculated Totals";
        myTable.Rows[2].Cells[0].Content = "Previous Year";
        myTable.Rows[2].Cells[1].Content = "Current Year";

        //Variable Data Rows
        myTable.Rows[3].SetContents(data.GetFieldValue(i, "Horses"), "", "Horses, Mules and Asses (ponies, donkeys, burros)", "$&nbsp;5.85", "");
        myTable.Rows[4].SetContents(data.GetFieldValue(i, "Cattle"), "", "Cattle (cows, bulls, yearling)", "$&nbsp;2.29", "");
        myTable.Rows[5].SetContents(data.GetFieldValue(i, "Bison"), "", "Domestic Bison", "$&nbsp;6.38", ""); 
        myTable.Rows[6].SetContents(data.GetFieldValue(i, "Sheep"), "", "Sheep", "$&nbsp;0.54", ""); 
        myTable.Rows[7].SetContents(data.GetFieldValue(i, "Swine"), "", "Swine", "$&nbsp;0.78", ""); 
        myTable.Rows[8].SetContents(data.GetFieldValue(i, "Goats"), "", "Goats", "$&nbsp;0.54", ""); 
        myTable.Rows[9].SetContents(data.GetFieldValue(i, "Poultry"), "", "Poultry (chickens, turkeys, gees, ducks and other domestic birds raised as food or to produce feathers", "$&nbsp;0.05", ""); 
        myTable.Rows[10].SetContents(data.GetFieldValue(i, "Bees"), "", "Bees (number of hives or boards)", "$&nbsp;0.41", ""); 
        myTable.Rows[11].SetContents(data.GetFieldValue(i, "Domestic"), "", "Alternative Livestock (privately owned caribou, mule deer, whitetail deer, elk, moose, antelope, mountain sheep, mountain goats indigenous to Montana)", "$26.23", ""); 
        myTable.Rows[12].SetContents(data.GetFieldValue(i, "Ratites"), "", "Ratites (includes all ostriches, rheas and emus", "$&nbsp;9.37", ""); 
        myTable.Rows[13].SetContents(data.GetFieldValue(i, "Llamas"), "", "Llamas and Alpacas", "$&nbsp;9.37", ""); 

        // Footer Row
        myTable.Rows[14].Cells[0].HStraddle = 4;
    myTable.Rows[14].Cells[4].HStraddle = 4;

    myTable.Rows[14].Cells[4].PointSize=10;  
        
        myTable.Rows[14].Cells[0].Content = "Total Amount Due for 2016 Per Capita Fee";
      
        myTable.Rows[14].Cells[4].Content = "$";


    // Create variable for holding table tags
        text = myTable.MakeTags();

    // Push variable into Array
        tables.push(text);
    }
    // Get Array Length
    ArraySize = tables.length; 
}

// Cycle through Array until length is reached and print lines
for (t = 0; t < ArraySize; t++)
{    
    tbl += tables[t];
}

Removing them from Global doesn't seem to correct the issue.

[step]

Okay thanks that clears things up a bit. You're returning the tables from a global string (tbl) which you need to reset at the beginning of each record:

//The following if statement will detect if we are currently in preview mode or editing this rule (versus composing output).  
if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)
{
    Rule("OnJobStart");
}

// Create empty
var ArraySize = 0;
var tables = [];
tbl = '';

//Get a count of the total number of records in the external data file
NumberOfRecords = data.recordCount;

//Now, loop through all records in the external data file and find the records that belong to the customer.
for (var i = 1; i <= NumberOfRecords; i++)
{
    if (data.GetFieldValue(i, "CID") == Field("Customer ID"))
    {
        //Create Table
        var myTable = new FPTable;
        myTable.AddColumns(5825, 5400, 27200, 7200, 8100);
        myTable.AddRows(15);
   
        myTable.Rows[0].Cells[3].HStraddle = 2;
        myTable.Rows[1].Cells[0].HStraddle = 2;
        myTable.Rows[1].Cells[2].VStraddle = 2;  
        myTable.Rows[1].Cells[3].VStraddle = 2;
        myTable.Rows[1].Cells[4].VStraddle = 2;

        myTable.Rows[0].Cells[0].PointSize=10;
        myTable.Rows[1].Cells[0].PointSize=10;
        myTable.Rows[2].Cells[0].PointSize=8;
        myTable.Rows[3].Cells[0].PointSize=10;
        myTable.Rows[4].Cells[0].PointSize=10;
        myTable.Rows[5].Cells[0].PointSize=10;
        myTable.Rows[6].Cells[0].PointSize=10;
        myTable.Rows[7].Cells[0].PointSize=10;
        myTable.Rows[8].Cells[0].PointSize=10;
        myTable.Rows[9].Cells[0].PointSize=10;
        myTable.Rows[10].Cells[0].PointSize=10;
        myTable.Rows[11].Cells[0].PointSize=10;
        myTable.Rows[12].Cells[0].PointSize=10;
        myTable.Rows[13].Cells[0].PointSize=10;
        

        //Title Row
        myTable.Rows[0].SetContents("County", data.GetFieldValue(i, "County"), "Property ID", data.GetFieldValue(i, "PropertyID"), ""); 

        //Header Rows
        myTable.Rows[1].Cells[0].Content = "Head Count";
        myTable.Rows[1].Cells[2].Content = "Livestock Type";
        myTable.Rows[1].Cells[3].Content = "Fee Amount Per Head";
        myTable.Rows[1].Cells[4].Content = "Per Capita Fee Calculated Totals";
        myTable.Rows[2].Cells[0].Content = "Previous Year";
        myTable.Rows[2].Cells[1].Content = "Current Year";

        //Variable Data Rows
        myTable.Rows[3].SetContents(data.GetFieldValue(i, "Horses"), "", "Horses, Mules and Asses (ponies, donkeys, burros)", "$&nbsp;5.85", "");
        myTable.Rows[4].SetContents(data.GetFieldValue(i, "Cattle"), "", "Cattle (cows, bulls, yearling)", "$&nbsp;2.29", "");
        myTable.Rows[5].SetContents(data.GetFieldValue(i, "Bison"), "", "Domestic Bison", "$&nbsp;6.38", ""); 
        myTable.Rows[6].SetContents(data.GetFieldValue(i, "Sheep"), "", "Sheep", "$&nbsp;0.54", ""); 
        myTable.Rows[7].SetContents(data.GetFieldValue(i, "Swine"), "", "Swine", "$&nbsp;0.78", ""); 
        myTable.Rows[8].SetContents(data.GetFieldValue(i, "Goats"), "", "Goats", "$&nbsp;0.54", ""); 
        myTable.Rows[9].SetContents(data.GetFieldValue(i, "Poultry"), "", "Poultry (chickens, turkeys, gees, ducks and other domestic birds raised as food or to produce feathers", "$&nbsp;0.05", ""); 
        myTable.Rows[10].SetContents(data.GetFieldValue(i, "Bees"), "", "Bees (number of hives or boards)", "$&nbsp;0.41", ""); 
        myTable.Rows[11].SetContents(data.GetFieldValue(i, "Domestic"), "", "Alternative Livestock (privately owned caribou, mule deer, whitetail deer, elk, moose, antelope, mountain sheep, mountain goats indigenous to Montana)", "$26.23", ""); 
        myTable.Rows[12].SetContents(data.GetFieldValue(i, "Ratites"), "", "Ratites (includes all ostriches, rheas and emus", "$&nbsp;9.37", ""); 
        myTable.Rows[13].SetContents(data.GetFieldValue(i, "Llamas"), "", "Llamas and Alpacas", "$&nbsp;9.37", ""); 

        // Footer Row
        myTable.Rows[14].Cells[0].HStraddle = 4;
    myTable.Rows[14].Cells[4].HStraddle = 4;

    myTable.Rows[14].Cells[4].PointSize=10;  
        
        myTable.Rows[14].Cells[0].Content = "Total Amount Due for 2016 Per Capita Fee";
      
        myTable.Rows[14].Cells[4].Content = "$";


    // Create variable for holding table tags
        text = myTable.MakeTags();

    // Push variable into Array
        tables.push(text);
    }
    // Get Array Length
    ArraySize = tables.length; 
}

// Cycle through Array until length is reached and print lines
for (t = 0; t < ArraySize; t++)
{    
    tbl += tables[t];
}

For what it's worth, I think you could simplify things a little bit by getting rid of the OnRecordStart rule all together and just do it all within your text rule:

//The following if statement will detect if we are currently in preview mode or editing this rule (versus composing output).  
if(IsPreview() || FusionPro.inValidation)
    Rule("OnJobStart");

// Create empty
var tables = [];
var tbl = [];

//Get a count of the total number of records in the external data file
NumberOfRecords = data.recordCount;

var type = [
    ['Horses', 'Horses, Mules and Asses (ponies, donkeys, burros)', 5.85],
    ['Cattle','Cattle (cows, bulls, yearling)', 2.29],
    ['Bison','Domestic Bison', 6.38],
    ['Sheep','Sheep', 0.54],
    ['Swine','Swine', 0.78], 
    ['Goats','Goats', 0.54],
    ['Poultry','Poultry (chickens, turkeys, gees, ducks and other domestic birds raised as food or to produce feathers', 0.05],
    ['Bees','Bees (number of hives or boards)', 0.41],
    ['Domestic','Alternative Livestock (privately owned caribou, mule deer, whitetail deer, elk, moose, antelope, mountain sheep, mountain goats indigenous to Montana)', 26.23],
    ['Ratites','Ratites (includes all ostriches, rheas and emus', 9.37],
    ['Llamas','Llamas and Alpacas' , 9.37]
];


//Now, loop through all records in the external data file and find the records that belong to the customer.
for (var n = 1; n <= NumberOfRecords; n++) {
    function ExField(field) { return data.GetFieldValue(n, field); }
    if (ExField("CID") == Field("Customer ID")) {
        
        //Create Table
        var myTable = new FPTable;
        
        myTable.AddColumns(5825, 5400, 27200, 7200, 8100);
        
        tbl.push(["County", ExField("County"), "Property ID", ExField("PropertyID"), ""]); // Title
        tbl.push(["Head Count", "", "Livestock Type", "Fee Amount Per Head", "Per Capita Fee Calculated Totals"]); // Header
        tbl.push(["Previous Year", 'Current Year', '', '', '']); // Header
        
        type.forEach(function(s) {
            var [field, description, price] = s;
            field = ExField(field);
            price = FormatNumber('$00.00', price).replace('$0','$ &nbsp;');
            tbl.push([field, '', description, price,'']);
        });

        // Footer
        tbl.push(["Total Amount Due for 2016 Per Capita Fee", "", "", "", "$"]); 
        
        // Formatting 
        for (var i=0; i<tbl.length; i++) {
            var row = myTable.AddRow();
            var cell = row.Cells[0];
            cell.PointSize = (i == 2) ? 8 : 10;
            if (!i) row.Cells[3].HStraddle = 2;
            if (i == 1) {
                cell.HStraddle = 2;
                for (var c = 2; c<=4; c++)
                    row.Cells[c].VStraddle = 2;
            }
            if (i == 14) cell.HStraddle = 4;
            var [col1,col2,col3,col4,col5] = tbl[i];
            row.SetContents(col1,col2,col3,col4,col5);
        }

        // Push variable into Array
        tables.push(myTable.MakeTags());
    }
}

return tables.join('');

[Landisa]

Thanks! The code got it working.

I will look into the shorter code as well.
Would be much simpler not to have so many rules.