Jump to content

My First Table - help!


Kim

Recommended Posts

Hi everyone,

 

So I know it's right before Christmas and the last thing anyone is going to do is look at this board, but maybe Dan or Ste or somebody is out there?? ;-)

 

I'm attempting my first table with multi-line record use. I've got a letter that's going to include a section with a report of dates and amounts in columns. There will be varying numbers of dates and amounts for each letter.

 

I've worked out the easy part of telling Fusion I've got multi-line and telling it to change based on the Company name field. And I've got the basics of a table set.

 

What I am trying to figure out is can I make it flow the data into columns...

My letter layout allows 6 columns (so 3 groups of Date Amount columns).

One company may have 25 lines of dates and amounts while another has 6 or 10, etc.

 

Can Fusion flow that info into columns, or will I have to massage the data into columns?

 

I'm attaching a screen grab to illustrate what I need and here's the start of my table code: (since I don't know how to flow the info, it's just repeating the same info in each column group right now - but I wanted to give you what I've got so far...)

 

var table = new FPTable();
table.AddColumns(7200,8500,7200,8500,7200,8500);

var data = FusionPro.GetMultiLineRecords();
for (var rec = 1; rec <= data.recordCount; rec++)
{
function ExField(str) 
{ return TaggedTextFromRaw(data.GetFieldValue(rec, str)); }


var content = ["Date","Amount","Date","Amount","Date","Amount"].map(ExField);

var row = table.AddRow();

row.SetContents.apply(row, content);

// row.Cells[0].Margins = row.Cells[1].Margins = { Top:0, Bottom:0, Left:0, Right:0 };

}


return table.MakeTags();

 

Thanks!

Kim

TableSample.jpg.f5b71e4a24d11fcb30ed64171b012727.jpg

Link to comment
Share on other sites

So I know it's right before Christmas and the last thing anyone is going to do is look at this board, but maybe Dan or Ste or somebody is out there?? ;-)

You know it.

 

What I am trying to figure out is can I make it flow the data into columns...

My letter layout allows 6 columns (so 3 groups of Date Amount columns).

One company may have 25 lines of dates and amounts while another has 6 or 10, etc.

 

Can Fusion flow that info into columns, or will I have to massage the data into columns?

It depends on how you're wanting the data to "flow." If you want the data to fill the first column, then fill the second column, then the third, you can set up a two column table with a header row, and return it in a 3 column text frame:

var table = new FPTable();
table.AddColumns(7200, 8500);
var header = table.AddRow();
header.Type = 'Header';
var cell = header.Cells[0];
cell.SetBorders('Thin', 'Black', 'Bottom');
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');

var data = FusionPro.GetMultiLineRecords();
for (var rec in data) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(data[rec], str)); }
 var [date, amt] = ['Date', 'Amount'].map(ExField);
 table.AddRow().SetContents(date, amt);
}

return table.MakeTags();

If you want the data to flow across all 6 columns before moving on to the next row, that can be done too:

var table = new FPTable();
table.AddColumns(7200, 8500, 7200, 8500, 7200, 8500);

var header = table.AddRow();
header.Type = 'Header';
var cell = header.Cells[0];
cell.SetBorders('Thin', 'Black', 'Bottom')
header.CopyCells(0, 1, 2, 3, 4, 5);
header.SetContents('Date', 'Amount', 'Date', 'Amount', 'Date', 'Amount');

var data = FusionPro.GetMultiLineRecords();
while (data.length) {
 var row = table.AddRow();
 var contents = [];
 data.splice(0, 3).forEach(function(rec) {
   function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(data[rec], str)); }
   contents.push(ExField('Date'), ExField('Amount'))
 });
 row.SetContents.apply(row, contents);
}

return table.MakeTags();

Link to comment
Share on other sites

Hey Ste!

 

Thanks so much for the reply! You're like Santa Claus!! ;)

 

So, I tried both of these. The first one gives me weird results with the first record info twice and then the last and one in the middle, then Date and Time repeated a number of times.

 

The other one just gives me a big blank with the red bar indicating not fitting. I'm not able to make my box big enough to get anything to show.

 

I'm sure it's something I'm missing or maybe I don't have my data defined properly. Also, I do have a paragraph of text below the table and then a name and signature. I was trying to set all up in one block so that the final paragraph and sig line move up or down according to the number of rows (so maybe that's contributing to things breaking or maybe something is off with my data file).

 

See grabs below when you get a chance. Please note that the grab showing sample data just has example entries in the date and amount columns, but rest assured my real data has all info in all fields, I just removed anything real.

 

Thanks again!

Kim

FirstRule.jpg.7378fe9fa2765bbd9d1482df520f9b39.jpg

SampleData.jpg.c80cd5d92d4936b1f8dd3c68c99aef52.jpg

Edited by Kim
Link to comment
Share on other sites

So, I tried both of these. The first one gives me weird results with the first record info twice and then the last and one in the middle, then Date and Time repeated a number of times.

 

The other one just gives me a big blank with the red bar indicating not fitting. I'm not able to make my box big enough to get anything to show.

Interesting. Maybe you could post your template with some sample data? I think it would be easier to see what you're dealing with that way because the screenshots you posted are tiny and I have bad eyes.

 

I'm sure it's something I'm missing or maybe I don't have my data defined properly.

For full disclosure, as my signature indicates, I'm not running FP 10 and therefore don't have access to the GetMultiLineRecords function so I just sort of guessed at that part in the code snippets I posted. But if you post your template, I think if we could get it working using the input as an external data file (the way I would do it in FP9), I think you should be able to translate that to FP10's multi-line record function.

Also, I do have a paragraph of text below the table and then a name and signature. I was trying to set all up in one block so that the final paragraph and sig line move up or down according to the number of rows (so maybe that's contributing to things breaking or maybe something is off with my data file).

Oh okay, I see. Well, the 3-column text frame approach won't work then.

Link to comment
Share on other sites

Thanks again for looking at this, Ste.

 

Sorry for the tiny grabs, I've collected files from the start I made - again, the data repeats itself right now since I don't have columns set yet. I just repeated the same info to give the idea for now.

 

Hopefully Dan isn't off on an exotic holiday trip and will be able to chime in too. I usually find the 2 of you quite helpful.

 

Thanks!

Kim

MultiLine_Sample.zip

Link to comment
Share on other sites

So, there's probably an easier way to do this in FusionPro 10 but here's how I would do it in FusionPro 9 (add the following to an OnRecordStart callback):

var numOfCols = 3;
var data = new ExternalDataFileEx(PrimaryInputFile(), FusionPro.inputFileDelimiter);

FusionPro.Composition.composeThisRecord = Field('company');

var rec = FusionPro.Composition.inputRecordNumber;

var table = new FPTable();
table.AddColumns(7200, 8500);

var header = table.AddRow();
header.Type = 'Header';
var cell = header.Cells[0];
cell.SetBorders('Thin', 'Black', 'Bottom');
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');


for (var i = rec; i <= data.recordCount; i++) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(i, str)); }
 if (ExField('company') && i > rec) break;

 var row = table.AddRow();
 row.SetContents.apply(row, ['Date', 'Amount'].map(ExField));
}

// Number of rows needed to evenly distribute across numOfCols.
// Minus 1 for the header row.
var rows = Math.ceil((table.Rows.length - 1) / numOfCols);

// Duplicate column 1 and 2 (and their headers) to make up numOfCols.
for (var i = 0; i < (numOfCols - 1) * 2; i++) {
 table.Columns.push(table.Columns[i % 2]);
 table.Rows[0].Cells.push(table.Rows[0].Cells[i % 2])
}

// Concatenate the rows cells in order to reduce our total rows
// and fill up the extra columns. 
var rowCounter = 1;
while ((table.Rows.length - 1) > rows) {
 var index = rowCounter % rows || rows;
 var row = table.Rows.splice(rows + 1, 1).pop();
 table.Rows[index].Cells = table.Rows[index].Cells.concat(row.Cells)
 rowCounter++
}

// Create the table tags.
table = table.MakeTags();

// Return the table.
FusionPro.Composition.AddVariable('RUL_table', table, true);

Link to comment
Share on other sites

Thanks so much, Ste!

 

I'm sure you're right and there's a way to do this differently with v10 that's possibly easier, but this does the trick.

 

The only other thing I'd like is to center just the headers (so center the words Date and Amount over the columns). There's a way to just center headers isn't there?

 

Thanks again,

Kim

Link to comment
Share on other sites

The only other thing I'd like is to center just the headers (so center the words Date and Amount over the columns). There's a way to just center headers isn't there?

Sure, just add the line in red to the existing code:

var header = table.AddRow();
header.Type = 'Header';
var cell = header.Cells[0];
cell.SetBorders('Thin', 'Black', 'Bottom');
[color="Red"]cell.HAlign = 'Center';[/color]
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');

Hmmm...although I just realized it's not parsing the data properly for the different companies. It did the first record group correctly but then skips records in the following companies.

It works for me so I imagine it has something to do with multi-line records and FusionPro 10. I would make sure that you don't still have your template configured to use multi-line records.

Link to comment
Share on other sites

Doh! I missed that multi-line was still checked. That fixed the problem with the records getting jumbled.

 

That HAlign centers ALL the cells. I was wondering if it's possible to just center the header and leave the actual dates and amounts under those headers flush left?

Link to comment
Share on other sites

That HAlign centers ALL the cells. I was wondering if it's possible to just center the header and leave the actual dates and amounts under those headers flush left?

It's done the same way:

for (var i = rec; i <= data.recordCount; i++) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(i, str)); }
 if (ExField('company') && i > rec) break;

 var row = table.AddRow();
 [color="Red"]row.Cells[0].HAlign = 'Left';
 row.CopyCells(0, 1);[/color]
 row.SetContents.apply(row, ['Date', 'Amount'].map(ExField));
}

Link to comment
Share on other sites

Here's Ste's rule adapted to use the multi-line record feature in FP 10. (It's back to a regular rule named "RUL_table", as in the original template you posted, not an OnRecordStart rule.)

var numOfCols = 3;
var data = FusionPro.GetMultiLineRecords();

var table = new FPTable();
table.AddColumns(7200, 8500);

var header = table.AddRow();
header.Type = 'Header';
var cell = header.Cells[0];
cell.SetBorders('Thin', 'Black', 'Bottom');
cell.HAlign = 'Center';
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');


for (var i = 1; i <= data.recordCount; i++) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(i, str)); }
 var row = table.AddRow();
 row.Cells[0].HAlign = 'Left';
 row.CopyCells(0, 1);
 row.SetContents.apply(row, ['Date', 'Amount'].map(ExField));
}

// Number of rows needed to evenly distribute across numOfCols.
// Minus 1 for the header row.
var rows = Math.ceil((table.Rows.length - 1) / numOfCols);

// Duplicate column 1 and 2 (and their headers) to make up numOfCols.
for (var i = 0; i < (numOfCols - 1) * 2; i++) {
 table.Columns.push(table.Columns[i % 2]);
 table.Rows[0].Cells.push(table.Rows[0].Cells[i % 2])
}

// Concatenate the rows cells in order to reduce our total rows
// and fill up the extra columns. 
var rowCounter = 1;
while ((table.Rows.length - 1) > rows) {
 var index = rowCounter % rows || rows;
 var row = table.Rows.splice(rows + 1, 1).pop();
 table.Rows[index].Cells = table.Rows[index].Cells.concat(row.Cells)
 rowCounter++
}

// Return the table tags.
return table.MakeTags();

Link to comment
Share on other sites

You could also accomplish this with a multi-column text frame, which makes the rule much simpler, since you only need a two-column table to flow into those three text frame columns. The only tricky part is the text that spans all columns after the table, but that's just a matter of using the <p paragraphplacement="straddleall"> tag. See the attached template.

MultiLine_Sample-Dan-2.pdf

Link to comment
Share on other sites

  • 2 weeks later...

Hi Dan and Ste,

 

Remember this thoroughly beautiful table you helped me work out?

 

I wound up using the code below for v10 and it's great and just what I asked for. But now that I've got some real data, I've got a little wrinkle. Sometimes there is only 1 entry for Date and Amount. So, I don't need 3 columns in those cases, but it still outputs the Date Amount headers for all 3 columns.

 

Any ideas on how to show the Date and Amount headers only if there is content to warrant them?

 

var numOfCols = 3;
var data = FusionPro.GetMultiLineRecords();

var table = new FPTable();
table.AddColumns(6000, 7000, 3000);

var header = table.AddRow();
header.Type = 'Header';
var cell = header.Cells[0];
cell.SetBorders('Thin', 'Black', 'Bottom');
cell.HAlign = 'Left';
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');


for (var i = 1; i <= data.recordCount; i++) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(i, str)); }
 var row = table.AddRow();
 row.Cells[0].HAlign = 'Left';
 row.CopyCells(0, 1);
 row.SetContents.apply(row, ['Date', 'Amount'].map(ExField));
}

// Number of rows needed to evenly distribute across numOfCols.
// Minus 1 for the header row.
var rows = Math.ceil((table.Rows.length - 1) / numOfCols);

// Duplicate column 1 and 2 (and their headers) to make up numOfCols.
for (var i = 0; i < (numOfCols - 1) * 3; i++) {
 table.Columns.push(table.Columns[i % 3]);
 table.Rows[0].Cells.push(table.Rows[0].Cells[i % 3])
}

// Concatenate the rows cells in order to reduce our total rows
// and fill up the extra columns. 
var rowCounter = 1;
while ((table.Rows.length - 1) > rows) {
 var index = rowCounter % rows || rows;
 var row = table.Rows.splice(rows + 1, 1).pop();
 table.Rows[index].Cells = table.Rows[index].Cells.concat(row.Cells)
 rowCounter++
}

// Return the table tags.
return table.MakeTags();

 

Thanks!

Kim

Link to comment
Share on other sites

I haven't tested this but it seems like it would give you what you're looking for:

var numOfCols = 3;
var data = FusionPro.GetMultiLineRecords();

var table = new FPTable();
table.AddColumns(6000, 7000, 3000);

var header = table.AddRow();
header.Type = 'Header';
header.Cells[0].SetBorders('Thin', 'Black', 'Bottom');
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');


for (var i = 1; i <= data.recordCount; i++) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(i, str)); }  
 [color="Red"]
 // Skip this record if 'Date' & 'Amount' fields are both empty.
 var contents = ['Date', 'Amount'].map(ExField);
 if (!Trim(contents.join(''))) continue;[/color]
 var row = table.AddRow();
 row.SetContents.apply(row, contents);
}

[color="red"]// Determine how many columns are needed based on how many rows
// were added to the table (minus 1 for the header row).
var colsNeeded = table.Rows.length - 1;[/color]

[color="red"]// Update the numOfCols variable if we need less than 3.
numOfCols = colsNeeded < numOfCols ? colsNeeded : numOfCols;[/color]

// Number of rows needed to evenly distribute across numOfCols.
var rows = Math.ceil(colsNeeded / numOfCols);

// Duplicate column 1 and 2 (and their headers) to make up numOfCols.
for (var i = 0; i < (numOfCols - 1) * 3; i++) {
 table.Columns.push(table.Columns[i % 3]);
 table.Rows[0].Cells.push(table.Rows[0].Cells[i % 3])
}

// Concatenate the rows cells in order to reduce our total rows
// and fill up the extra columns. 
var rowCounter = 1;
while (colsNeeded > rows) {
 var index = rowCounter % rows || rows;
 var row = table.Rows.splice(rows + 1, 1).pop();
 table.Rows[index].Cells = table.Rows[index].Cells.concat(row.Cells)
 rowCounter++
}

// Return the table tags.
return table.MakeTags();

Link to comment
Share on other sites

Hi Ste!

 

Thanks for the response. This is almost perfect. I thought it was doing it exactly as needed, but have found that it behaves a little weird when there are 4 records - it makes 2 columns with 2 lines each and puts a blank 3rd column with just headers. If I have 3 records it is fine and does 3 columns across. It just seems to get confused by 4 records.

 

Examples of the one scenario that doesn't work attached along with examples of other record counts working as expected.

good1.png.516af0c984e8d28d5486a6da896e9f8a.png

bad.png.e0f6b0e7df0c57781cf6549be520916f.png

good2.png.a0f6c61510b980f2a14be37a8a6c9b38.png

good3.png.1fe4c5cf4b0b94f7ceb8614354c04c39.png

good4.png.3365579e36c86db7cdc8a62ac7612480.png

Edited by Kim
Link to comment
Share on other sites

Have I stumped you guys?

No, sorry, I've just been busy. Try this:

var numOfCols = 3;
var data = FusionPro.GetMultiLineRecords();

var table = new FPTable();
table.AddColumns(6000, 7000, 3000);

var header = table.AddRow();
header.Type = 'Header';
header.Cells[0].SetBorders('Thin', 'Black', 'Bottom');
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');


for (var i = 1; i <= data.recordCount; i++) {
 function ExField(str) { return TaggedTextFromRaw(data.GetFieldValue(i, str)); }

 // Skip this record if 'Date' & 'Amount' fields are both empty.
 var contents = ['Date', 'Amount'].map(ExField);
 if (!Trim(contents.join(''))) continue;
 var row = table.AddRow();
 row.SetContents.apply(row, contents);
}

// Determine how many columns are needed based on how many rows
// were added to the table (minus 1 for the header row).
var colsNeeded = table.Rows.length - 1;

// Number of rows needed to evenly distribute across numOfCols.
var rows = Math.ceil(colsNeeded / numOfCols);

// Duplicate column 1 and 2 (and their headers) to make up numOfCols.
for (var i = 0; i < (numOfCols - 1) * 3; i++) {
 table.Columns.push(table.Columns[i % 3]);
 table.Rows[0].Cells.push(table.Rows[0].Cells[i % 3])
}

// Concatenate the rows cells in order to reduce our total rows
// and fill up the extra columns.
var rowCounter = 1;
while (colsNeeded > rows) {
 var index = rowCounter % rows || rows;
 var row = table.Rows.splice(rows + 1, 1).pop();
 table.Rows[index].Cells = table.Rows[index].Cells.concat(row.Cells)
 rowCounter++
}

[color="Red"]// Create reference variables to the cells of the first row (header)
// and second row (the date/amount values).
var [headerCells, contentCells] = table.Rows.slice(0, 2).map(function(s) { return s.Cells });

// We only need as many header cells as there are content cells. So, if
// there are more header cells than content cells, we can begin removing
// headers until the columns are equal.
while (headerCells.length > contentCells.length)
 headerCells.pop();[/color]

// Return the table tags.
return table.MakeTags();

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