Jump to content
The latest version of FusionPro® VDP is 13.1.11 (Windows and Mac).

with merging multiple data tables

Landisa

By Landisa
in Issues, Questions, Etc.

 Share

Recommended Posts

Landisa Contributor

Landisa

Posted
Posted (edited)

====================

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

Edited by Landisa
Solved
Link to comment
Share on other sites
step Mentor

step

Posted
Posted

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

Link to comment
Share on other sites
Landisa Contributor

Landisa

Posted
  • Author
Posted (edited)

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)", "$ 5.85", "");
       myTable.Rows[4].SetContents(data.GetFieldValue(i, "Cattle"), "", "Cattle (cows, bulls, yearling)", "$ 2.29", "");
       myTable.Rows[5].SetContents(data.GetFieldValue(i, "Bison"), "", "Domestic Bison", "$ 6.38", ""); 
       myTable.Rows[6].SetContents(data.GetFieldValue(i, "Sheep"), "", "Sheep", "$ 0.54", ""); 
       myTable.Rows[7].SetContents(data.GetFieldValue(i, "Swine"), "", "Swine", "$ 0.78", ""); 
       myTable.Rows[8].SetContents(data.GetFieldValue(i, "Goats"), "", "Goats", "$ 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", "$ 0.05", ""); 
       myTable.Rows[10].SetContents(data.GetFieldValue(i, "Bees"), "", "Bees (number of hives or boards)", "$ 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", "$ 9.37", ""); 
       myTable.Rows[13].SetContents(data.GetFieldValue(i, "Llamas"), "", "Llamas and Alpacas", "$ 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.

Edited by Landisa
Link to comment
Share on other sites
step Mentor

step

Posted
Posted

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 = [];
[color="Red"]tbl = '';[/color]

//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)", "$ 5.85", "");
       myTable.Rows[4].SetContents(data.GetFieldValue(i, "Cattle"), "", "Cattle (cows, bulls, yearling)", "$ 2.29", "");
       myTable.Rows[5].SetContents(data.GetFieldValue(i, "Bison"), "", "Domestic Bison", "$ 6.38", ""); 
       myTable.Rows[6].SetContents(data.GetFieldValue(i, "Sheep"), "", "Sheep", "$ 0.54", ""); 
       myTable.Rows[7].SetContents(data.GetFieldValue(i, "Swine"), "", "Swine", "$ 0.78", ""); 
       myTable.Rows[8].SetContents(data.GetFieldValue(i, "Goats"), "", "Goats", "$ 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", "$ 0.05", ""); 
       myTable.Rows[10].SetContents(data.GetFieldValue(i, "Bees"), "", "Bees (number of hives or boards)", "$ 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", "$ 9.37", ""); 
       myTable.Rows[13].SetContents(data.GetFieldValue(i, "Llamas"), "", "Llamas and Alpacas", "$ 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','$  ');
           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('');

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.

×
 Share
Go to topic listing
×
×
  • Create New...