MultiLine Record, return "N/A" if Blank content

[jwarz]

Hello,

We are very new the MultiLine record thing and were having through getting our data to display "N/A" if one of the cells are blank within the table.

var result = "";
var data = FusionPro.GetMultiLineRecords();
for (var r = 1; r <= data.recordCount; r++)
   result += data.GetFieldValue(r, "name") + " " + data.GetFieldValue(r, "observation") + " " + data.GetFieldValue(r, "interpretation") + "\n";

return result;

I have another rule that says... 

if (Field("observation") == "")
{   return "N/A"; }
   
else 
{    return Field("observation"); }

So I was thinking maybe we could tell the data.GetFieldValue(r, "observation") to somehow read a rule rather than field. But then I also got the idea of putting something in the table format that if it finds a blank, return N/A, but again I couldn't seem to figure that one out either. Either way would be fine, as long as we can get any cell in the table thats blank to display N/A??

[Douglas Cogan]

You could make a function in JavaScriptGlobals like this:
 

function ReturnNAIfEmpty(data)
{
  if (Len(Trim(data)) == 0)
    return "N/A";
  return data;
}

Then your line above could be changed to:

result += ReturnNAIfEmpty(data.GetFieldValue(r, "name")) + " " + ReturnNAIfEmpty(data.GetFieldValue(r, "observation")) + " " + ReturnNAIfEmpty(data.GetFieldValue(r, "interpretation")) + "\n";

I think that will do what  you want. The ReturnNAIfEmpty also handles the case where it is just a space in the data (that's the Trim part)

[jwarz]

Your are a life saver. So it is validating correctly on the multi line record rule, but its still displaying blanks in the table?

[Douglas Cogan]

Can you post the whole rule?

Or you could send the job to FusionProSupport@marcom.com and mention me (Douglas Cogan) in the message and I'll look at it. But if you're willing to post the rule here I can take a quick look, and might be faster (and help others.)

[jwarz]

sure not a problem!

below is the rule for the Table:

var DataSource = "Multi-line records"; // "Data Source:" (Required): DataSourceList
var Columns =  // "Column": MultiGroup with fields:
[
  { "Field": "name", "Heading": "Biomarker", "Width": HundredthsOfPointsFromText("2 in"), "HAlign": "Left", "VAlign": "Middle", "Format": { }, },
  { "Field": "observation", "Heading": "Result", "Width": HundredthsOfPointsFromText("1 in"), "HAlign": "Left", "VAlign": "Middle", "Format": { }, },
  { "Field": "interpretation", "Heading": "Finding", "Width": HundredthsOfPointsFromText("1.5 in"), "HAlign": "Left", "VAlign": "Middle", "Format": { }, },
];
var Style = "Biomarker Results Table Format";
var StretchColumnsToFrameWidth = true;

var table = new FPTable;
if (Style)
    table = Rule(Style);

for (var i in Columns)
    table.AddColumn(Columns[i].Width);

var header = table.AddRow();
header.Type = "header";
for (var c in Columns)
{
    var column = Columns[c];
    var cell = header.Cells[c];
    cell.Content = TaggedTextFromRaw(column.Heading || column.Field);
    cell.HAlign = column.HAlign;
    cell.VAlign = column.VAlign;
    if (table.style && table.style.headerStyle)
    {
        var HeaderStyle = table.style.headerStyle;
        cell.Font = HeaderStyle.family;
        cell.Bold = HeaderStyle.bold;
        cell.Italics = HeaderStyle.italic;
        cell.PointSize = HeaderStyle.pointsize;
        cell.TextColor = HeaderStyle.color;
    }
    if (table.style && table.style.each_cell.Rulings)
        cell.Rulings = table.style.each_cell.Rulings;
    if (table.style && table.style.each_cell.Margins)
        cell.Margins = table.style.each_cell.Margins;
}

// Create the main body from the data.
var data;
if (DataSource == "Multi-line records")
    data = FusionPro.GetMultiLineRecords();
else
    data = FusionPro.GetDataSource(DataSource);

for (var r = 1; r <= data.recordCount; r++)
{       
    var row = table.AddRow();
    for (var c in Columns)
    {
        var column = Columns[c];
        var cell = row.Cells[c];
        cell.Content = FormatGenericTagged(column.Format, data.GetFieldValue(r, column.Field));
        cell.HAlign = column.HAlign;
        cell.VAlign = column.VAlign;
        if (table.style && table.style.bodyStyle)
        {
            var BodyStyle = table.style.bodyStyle;
            cell.Font = BodyStyle.family;
            cell.Bold = BodyStyle.bold;
            cell.Italics = BodyStyle.italic;
            cell.PointSize = BodyStyle.pointsize;
            cell.TextColor = BodyStyle.color;
        }
        if (table.style && table.style.each_cell.Rulings)
            cell.Rulings = table.style.each_cell.Rulings;
        if (table.style && table.style.each_cell.Margins)
            cell.Margins = table.style.each_cell.Margins;
    }
}

if (StretchColumnsToFrameWidth)
    table.StretchColumnsToFrameWidth();

return table;

 

and we also have a Table Format Rule:
 

Quote

// Rule converted from XML Template "Biomarker Results Table Format":
// Begin XML Template selections //
var HasGrid = false; // "Grid": CheckGroup
var GridStyle = "All"; // "Style:" (Required): PickList ["Inside" (Inside), "Outside" (Outside), "All" (All)]
var Color = "Black"; // "Color:" (Required): ColorList
var Thickness = "Thin"; // "Thickness:" (Required): PickList ["Thin" (Thin), "Medium" (Medium), "Thick" (Thick)]
var HasHeaderShading = false; // "Header Shading": CheckGroup
var HeaderColor = "Black"; // "Color:" (Required): ColorList
var HeaderTint = Int("30"); // "Tint (%):" (Required): SingleLine (Integer)
var HasRowShading = false; // "Row Shading": CheckGroup
var RowColor1 = "Black"; // "First Color:" (Required): ColorList
var RowTint1 = Int("30"); // "Tint (%):" (Required): SingleLine (Integer)
var RowColor2 = "White"; // "Second Color:": ColorList
var RowTint2 = Int("30"); // "Tint (%):" (Required): SingleLine (Integer)
// Section "Margins":
var MarginLeft = HundredthsOfPointsFromText("6 pt"); // "Left:": Space (SmallUnits)
var MarginRight = HundredthsOfPointsFromText("6 pt"); // "Right:": Space (SmallUnits)
var MarginTop = HundredthsOfPointsFromText("0 pt"); // "Top:": Space (SmallUnits)
var MarginBottom = HundredthsOfPointsFromText("0 pt"); // "Bottom:": Space (SmallUnits)
// Section "Fonts":
var HeaderStyle = { "bold": "0", "color": "Black", "family": "Arial Black", "italic": "0", "pointsize": "11 pt", }; // "Header Font:": CharStyle
var BodyStyle = { "bold": "0", "color": "Black", "family": "Arial", "italic": "0", "pointsize": "11 pt", }; // "Body Font:": CharStyle
// End XML Template selections //

var table = new FPTable;
table.style = { each_cell: {}, headerStyle: HeaderStyle, bodyStyle: BodyStyle };

if (HasGrid)
{
    if (GridStyle != "Inside")
        table.SetBorders(Thickness, Color, "Top", "Bottom", "Left", "Right");
    
    if (GridStyle != "Outside")
    {
        var eachCellRuling = { Color: Color, Thickness: Thickness };
        // These need to be copied to each cell after the table is populated with data:
        table.style.each_cell.Rulings = { Left: eachCellRuling, Right: eachCellRuling, Top: eachCellRuling, Bottom: eachCellRuling };
    }
}

if (HasHeaderShading)
{
    table.HeaderShadingColor = HeaderColor;
    table.HeaderShadingPct = HeaderTint;    
}

if (HasRowShading)
{
    table.ShadingColor1 = RowColor1;
    table.ShadingPct1 = RowTint1;
    table.ShadingColor2 = RowColor2;
    table.ShadingPct2 = RowTint2;
}

// These need to be copied to each cell after the table is populated with data:
table.style.each_cell.Margins = { Left: MarginLeft, Right: MarginRight, Top: MarginTop / 10, Bottom: MarginBottom / 10 };

return table;

 

 

[Douglas Cogan]

I'm not seeing the function ReturnNAIfEmpty called in either of these rules.

For the first one, change this line:

 cell.Content = FormatGenericTagged(column.Format, data.GetFieldValue(r, column.Field));

to

 cell.Content = FormatGenericTagged(column.Format, ReturnNAIfEmpty(data.GetFieldValue(r, column.Field)));

 

For the second one, I think this is just the format rule, not the data rule. I'd need to see the data rule.

 

 

[jwarz]

that did the trick!!! Thank you so much you are a life saver! 😃❤️

[Douglas Cogan]

So glad it helped and glad you are able to use the multi-line data feature and table rules.