I have a need to limit the number of rows in a dynamic table so it will fit on the page. All of the extra rows need to be added together and then displayed as the last row.
All of the columns are currency or numbers except for the description column and a date column. The description and date columns on the combined row would become static values.
For example the page will fit 3 rows (plus the last one), but there are 6 rows.
In case the answer on @hamelj’s question is “yes” then I assume that you can add for example the following HTML code (in Source mode) to your Print Context Section:
For the extra lines added together, the Description will be set to a static value and the 2 dates will be left blank. For the currency and float values, how do I format them correctly. Currency with 2 digits after the decimal and with float up to 2 digits. I’m playing around with toPrecision, but with mixed results.
Also would this be correct if (totals.description.length == 0) { instead of if (totals.description.length > 0) { that I changed to get it to display the extra line?
I believe I’ve figured out the formatting piece. I had inadvertently mixed up rate and hours data versus the column headings so things were not being applied as expected. I’m using formatter.currencyNoSymbol(rate) to handle the formatting.
This is where I’ve landed. Do you see anything troubling with it?
I need get some clarification from the business owners of what they want regarding the addition on the rate and multiple. To me simple addition does not make sense on these two columns. The hours, current and ytd columns make sense, but the others should be left blank or have a static value in my opinion.
var result = "", maxRows = 14;
var totals = {
description: "",
startdate: "",
enddate: "",
hours: 0,
rate: 0,
multiple: 0,
current: 0,
ytd: 0
};
if ("earnings" in record.tables) {
for (var i = 0; i < record.tables["earnings"].length; i++) {
var description = record.tables["earnings"][i].fields["BASE_BALANCE_NAME"];
var startdate = record.tables["earnings"][i].fields["ORIGINATING_START_DATE"];
var enddate = record.tables["earnings"][i].fields["ORIGINATING_END_DATE"];
var hours = record.tables["earnings"][i].fields["EARNINGS_HOURS"];
var rate = record.tables["earnings"][i].fields["EARNINGS_RATE"];
var multiple = record.tables["earnings"][i].fields["EARNINGS_MULTIPLE"];
var current = record.tables["earnings"][i].fields["EARNINGS_AMOUNT"];
var ytd = record.tables["earnings"][i].fields["EARNINGS_HOURS_YEAR"];
if (i < maxRows) {
result += "<tr>";
result += ("<td style=\"text-align: left; width: 32.98%;\">" + description + "</td>");
result += ("<td style=\"text-align: left; width: 8.63%;\">" + startdate + "</td>");
result += ("<td style=\"text-align: left; width: 8.68%;\">" + enddate + "</td>");
result += ("<td style=\"text-align: right; width: 8.11%;\">" + hours + "</td>");
result += ("<td style=\"text-align: right; width: 8.51%;\">" + formatter.currencyNoSymbol(rate) + "</td>");
result += ("<td style=\"text-align: right; width: 9.66%;\">" + multiple + "</td>");
result += ("<td style=\"text-align: right; width: 9.98%;\">" + formatter.currencyNoSymbol(current) + "</td>");
result += ("<td style=\"text-align: right; width: 13.45%;\">" + ytd + "</td>");
result += "</tr>";
} else {
if (totals.description.length > 0) {
if (typeof description == "number") totals.description += ", ";
}
if (typeof description == "number") totals.description += description;
if (typeof startdate == "number") totals.startdate += startdate;
if (typeof enddate == "number") totals.enddate += enddate;
if (typeof hours == "number") totals.hours += hours;
if (typeof rate == "number") totals.rate += rate;
if (typeof multiple == "number") totals.multiple += multiple;
if (typeof current == "number") totals.current += current;
if (typeof ytd == "number") totals.ytd += ytd;
}
}
}
if (record.tables["earnings"].length > maxRows){
//if (totals.description.length == 0) {
result += "<tr>";
result += ("<td style=\"text-align: left; width: 32.98%;\">" + "Other Earnings - Combined for reporting" + "</td>");
result += ("<td style=\"text-align: left; width: 8.63%;\">" + "" + "</td>");
result += ("<td style=\"text-align: left; width: 8.68%;\">" + "" + "</td>");
result += ("<td style=\"text-align: right; width: 8.11%;\">" + totals.hours + "</td>");
result += ("<td style=\"text-align: right; width: 8.51%;\">" + formatter.currencyNoSymbol(totals.rate) + "</td>");
result += ("<td style=\"text-align: right; width: 9.66%;\">" + totals.multiple + "</td>");
result += ("<td style=\"text-align: right; width: 9.98%;\">" + formatter.currencyNoSymbol(totals.current) + "</td>");
result += ("<td style=\"text-align: right; width: 13.45%;\">" + totals.ytd + "</td>");
result += "</tr>";
}
results.html(result);
The only question you have to think about is if the height of the “combined” table row might change. The reason why I am asking this is because I assume that the height of the specific table row will increase when the content of the TD-element “description” doesn’t fit on one line.
The length of the description poses the largest risk. I have not seen the other columns approach line wrapping. Perhaps truncating the Description something like this when I extract the values in the datamapper?
I just discovered that they told me the wrong location to collect the Current and YTD values. The data is an XML file and is located in a different node structure than the rest of the columns. Structured like this example.
In the data mapper it’s easy enough to add a repeat and create another detail table. There will be a string value in <RECORD_TYPE> that matches in both two detail tables to identify the two records.
Given this new twist, what would be the best approach? Do I try to do this in the datamapper or in the dynamic table script?
The other thing to consider is that these XML files are really huge with lots of records and lots of nodes per record.
You would like to combine the child nodes of the NODE_EARNINGS node with the child nodes of the NODE_HOURS node when the node RECORD_TYPE does contain the same value as the node RECORD_BASE, is that correct? In case this is correct then I assume that the best option will be to solve this on the Data Mapping Configuration side.
I’m extracting both into separate detail tables (i.e, Expenses and Expenses_Amts), then using an action I loop though the Expenses detail table and find the matching values to insert into two javascript fields I added to the Expenses detail table.
