My First Table - help!

[Kim]

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

[step]

Originally Posted by Kim

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.

Originally Posted by Kim

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

[Kim]

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

[step]

Originally Posted by Kim

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.

Originally Posted by Kim

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.

Originally Posted by Kim

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.

[Kim]

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

[step]

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

[Kim]

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

[Kim]

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.

[step]

Originally Posted by Kim

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');
cell.HAlign = 'Center';
header.CopyCells(0, 1);
header.SetContents('Date', 'Amount');

Originally Posted by Kim

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.

[Kim]

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?