Jump to content

Adding rows to a table on the fly using external Data


Eric Patrick

Recommended Posts

I 've been working on this project with a CSR looming over me and can't come up with the right fix.

I have a table that will have a variable number of rows based on an external data file. I've seen all the posted threads and have tried parts of a lot of them.

I can get the table to look like I want it to, but i can't get the variable row part to work. I have attached the project where I have it right now.

I'm just stumped as to where to go from here. Any insight or fixes would be highly appreciated.

 

Thanks!

TABLE_SETUP.zip

Link to comment
Share on other sites

I'm sure someone else can give you a cleaner solution, but I think this is what you're looking for:

 


if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)
{
   Rule("OnJobStart");
}


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

/*=============================================================================
||  Create arrays to hold values that match the CID of the client's record
||=============================================================================*/
   var clientMatch = [];
   var invoiceMatch = [];
   var dateMatch = [];
   var jobMatch = [];
   var amtMatch = [];

   // Step through the external data file and push matches into their respective variables if there is a match
   for (var i=1; i <= numRecsExtDF; i++) {
       if (externalDF.GetFieldValue(i, 'CID') == Field("Client Number")) {
           clientMatch.push(externalDF.GetFieldValue(i, 'CID'));
           invoiceMatch.push(externalDF.GetFieldValue(i, 'Inv #'));
           dateMatch.push(externalDF.GetFieldValue(i, 'Inv Date'));
           jobMatch.push(externalDF.GetFieldValue(i, 'Job Type'));
           amtMatch.push(externalDF.GetFieldValue(i, 'Client Inv Balance'));
       }
   }

/*=============================================================================
||  Create the table 
||=============================================================================*/
   new FPTable;
   var myTable = new FPTable;
   myTable.AddColumns(14000, 14000, 14000, 14000);     // 1 inch = 7200 pts
   myTable.AddRows(clientMatch.length+2);              // add 2 additional rows (Header and summary lines)

   // HEADER ROW FORMATTING 
       myTable.Rows[0].Cells[0].Font = "Arial";
       myTable.Rows[0].Cells[0].PointSize = "10";
       myTable.Rows[0].Cells[0].Font = "Arial Bold"; 
       myTable.Rows[0].Cells[0].TextColor = "White";
       myTable.Rows[0].Cells[0].ShadeColor = "BLUE_HEADER_LINES"; 
       myTable.Rows[0].Cells[0].ShadePct = 100;
       myTable.Rows[0].Cells[0].Margins = new FPTableMargins;
       myTable.Rows[0].Cells[0].Margins.Top = 50;
       myTable.Rows[0].Cells[0].Margins.Right = 500;
       myTable.Rows[0].Cells[0].HAlign = "Center";
       myTable.Rows[0].CopyCells(0, 1, 2, 3); // Apply the same formating to each cell in this row
   // HEADER ROW CONTENT 
       myTable.Rows[0].SetContents("Invoice #", "Invoice Date", "Job Type", "Amount");

       // interate through the length of the arrays (data matches from external data file) and create rows
       for (var i=1; i<=clientMatch.length; i++) {
           // TABLE CONTENT FORMATTING 
               myTable.Rows[i].Cells[0].Font = "Georgia";
               myTable.Rows[i].Cells[0].PointSize = "8";
               myTable.Rows[i].Cells[0].HAlign = "Left";
               myTable.Rows[i].Cells[0].Margins = new FPTableMargins;
               myTable.Rows[i].Cells[0].Margins.Top = 40;
               myTable.Rows[i].Cells[0].Margins.Bottom = 40;
               myTable.Rows[i].Cells[0].Margins.Right = 500;
               myTable.Rows[i].Cells[0].SetBorders("Thin", "BLUE_HEADER_LINES", "Bottom");
               myTable.Rows[i].Cells[0].HAlign = "Center";
               myTable.Rows[i].CopyCells(0,1,2,3); // Apply the same formating to each cell in this row
           // CREATE CONTENT FOR EXTERNAL DATA FILE RECORDS
               myTable.Rows[i].SetContents(invoiceMatch[i-1], dateMatch[i-1], jobMatch[i-1], amtMatch[i-1]);
       }

   // FOOTER ROW FORMATTING 
       myTable.Rows[clientMatch.length+1].Cells[0].Font = "Arial";
       myTable.Rows[clientMatch.length+1].Cells[0].PointSize = "10";
       myTable.Rows[clientMatch.length+1].Cells[0].Font = "Arial Bold"; 
       myTable.Rows[clientMatch.length+1].Cells[0].TextColor = "Black";
       myTable.Rows[clientMatch.length+1].Cells[0].ShadeColor = "White"; 
       myTable.Rows[clientMatch.length+1].Cells[0].ShadePct = 100;
       myTable.Rows[clientMatch.length+1].Cells[0].Margins = new FPTableMargins;
       myTable.Rows[clientMatch.length+1].Cells[0].Margins.Top = 50;
       myTable.Rows[clientMatch.length+1].Cells[0].Margins.Right = 500;
       myTable.Rows[clientMatch.length+1].Cells[0].SetBorders("Thin", "BLUE_HEADER_LINES", "Top", "Bottom");
       myTable.Rows[clientMatch.length+1].Cells[0].HAlign = "Center";
       myTable.Rows[clientMatch.length+1].CopyCells(0, 1, 2, 3); // Apply the same formating to each cell in this row
   // CREATE FOOTER CONTENT
       myTable.Rows[clientMatch.length+1].SetContents("TOTAL","","", Field("CLIENT SUMMARY1"));

   return myTable.MakeTags();

Edited by step
Link to comment
Share on other sites

So I was messing with this job trying to teach myself some things about tables. I have a question. Why are my row borders extended slightly larger than the shaded header row? Do I need to change a margin of the rows somewhere? Here is the rule as I have it now.

if(FusionPro.Composition.isPreview == true || FusionPro.inValidation == true)
{
   Rule("OnJobStart");
}

//Get a count of the total number of records in the external data file
numRecsExtDF = externalDF.recordCount;
/*=============================================================================
||  Create arrays to hold values that match the CID of the client's record
||=============================================================================*/
   var clientMatch = [];
   var invoiceMatch = [];
   var dateMatch = [];
   var jobMatch = [];
   var amtMatch = [];
   // Step through the external data file and push matches into their respective variables if there is a match
   for (var i=1; i <= numRecsExtDF; i++) {
       if (externalDF.GetFieldValue(i, 'CID') == Field("Client Number")) {
           clientMatch.push(externalDF.GetFieldValue(i, 'CID'));
           invoiceMatch.push(externalDF.GetFieldValue(i, 'Inv #'));
           dateMatch.push(externalDF.GetFieldValue(i, 'Inv Date'));
           jobMatch.push(externalDF.GetFieldValue(i, 'Job Type'));
           amtMatch.push(externalDF.GetFieldValue(i, 'Client Inv Balance'));
       }
   }

/*=============================================================================
||  Create the table 
||=============================================================================*/
   new FPTable;
   var myTable = new FPTable;
   myTable.AddColumns(13850, 13850, 13850, 13850);     // 1 inch = 7200 pts
   myTable.AddRows(clientMatch.length+2);              // add 2 additional rows (Header and summary lines)
   // HEADER ROW FORMATTING 
       myTable.Rows[0].Cells[0].Font = "Arial Black";
       myTable.Rows[0].Cells[0].PointSize = "10";
       myTable.Rows[0].Cells[0].TextColor = "White";
       myTable.Rows[0].Cells[0].ShadeColor = "BLUE_HEADER_LINES"; 
       myTable.Rows[0].Cells[0].ShadePct = 100;
       myTable.Rows[0].Cells[0].Margins = new FPTableMargins;
       myTable.Rows[0].Cells[0].Margins.Top = 100;
       myTable.Rows[0].Cells[0].Margins.Right = 750;
       myTable.Rows[0].Cells[0].HAlign = "Center";
       myTable.Rows[0].Cells[0].VAlign = "Bottom";
       myTable.Rows[0].CopyCells(0, 1, 2, 3); // Apply the same formating to each cell in this row
   // HEADER ROW CONTENT 
       myTable.Rows[0].SetContents("Invoice #", "Invoice Date", "Job Type", "Amount");
       // interate through the length of the arrays (data matches from external data file) and create rows
       for (var i=1; i<=clientMatch.length; i++) {
           // TABLE CONTENT FORMATTING 
               myTable.Rows[i].Cells[0].Font = "Georgia";
               myTable.Rows[i].Cells[0].PointSize = "8";
               myTable.Rows[i].Cells[0].Margins = new FPTableMargins;
               myTable.Rows[i].Cells[0].Margins.Top = 50;
               myTable.Rows[i].Cells[0].Margins.Bottom = 50;
               myTable.Rows[i].Cells[0].Margins.Right = 750;
               myTable.Rows[i].Cells[0].SetBorders("Thin", "BLUE_HEADER_LINES", "Top", "Bottom", "Left", "Right");
               myTable.Rows[i].Cells[0].HAlign = "Center";
               myTable.Rows[i].CopyCells(0,1,2,3); // Apply the same formating to each cell in this row
           // CREATE CONTENT FOR EXTERNAL DATA FILE RECORDS
               myTable.Rows[i].SetContents(invoiceMatch[i-1], dateMatch[i-1], jobMatch[i-1], amtMatch[i-1]);
       }

   // FOOTER ROW FORMATTING 
       myTable.Rows[clientMatch.length+1].Cells[0].Font = "Arial Black";
       myTable.Rows[clientMatch.length+1].Cells[0].PointSize = "10";
       myTable.Rows[clientMatch.length+1].Cells[0].TextColor = "Black";
       myTable.Rows[clientMatch.length+1].Cells[0].ShadeColor = "White"; 
       myTable.Rows[clientMatch.length+1].Cells[0].ShadePct = 100;
       myTable.Rows[clientMatch.length+1].Cells[0].Margins = new FPTableMargins;
       myTable.Rows[clientMatch.length+1].Cells[0].Margins.Top = 80;
       myTable.Rows[clientMatch.length+1].Cells[0].Margins.Right = 750;
       myTable.Rows[clientMatch.length+1].Cells[0].SetBorders("Thin", "BLUE_HEADER_LINES", "Top", "Bottom", "Left", "Right");
       myTable.Rows[clientMatch.length+1].Cells[0].HAlign = "Center";
       myTable.Rows[clientMatch.length+1].Cells[0].VAlign = "Bottom";
       myTable.Rows[clientMatch.length+1].CopyCells(0, 1, 2, 3); // Apply the same formating to each cell in this row
   // CREATE FOOTER CONTENT
       myTable.Rows[clientMatch.length+1].SetContents("TOTAL","","", "$" + Rule("Total Summary"));
   return myTable.MakeTags();

 

Again, just playing around, but if someone knows how to fix that would be helpful for future use. Thanks.

Edited by dreimer
Link to comment
Share on other sites

It's because you don't have a border set on the header row. You'd have to add this line:

// HEADER ROW FORMATTING 
       myTable.Rows[0].Cells[0].Font = "Arial Black";
       myTable.Rows[0].Cells[0].PointSize = "10";
       myTable.Rows[0].Cells[0].TextColor = "White";
       myTable.Rows[0].Cells[0].ShadeColor = "BLUE_HEADER_LINES"; 
       myTable.Rows[0].Cells[0].ShadePct = 100;
       myTable.Rows[0].Cells[0].Margins = new FPTableMargins;
       myTable.Rows[0].Cells[0].Margins.Top = 100;
       myTable.Rows[0].Cells[0].Margins.Right = 750;
       [color="Red"]myTable.Rows[0].Cells[0].SetBorders("Thin", "BLUE_HEADER_LINES", "Top", "Bottom", "Left", "Right");[/color]
       myTable.Rows[0].Cells[0].HAlign = "Center";
       myTable.Rows[0].Cells[0].VAlign = "Bottom";
       myTable.Rows[0].CopyCells(0, 1, 2, 3); // Apply the same formating to each cell in this row
   // HEADER ROW CONTENT 
       myTable.Rows[0].SetContents("Invoice #", "Invoice Date", "Job Type", "Amount");

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