Exclude data from chart if field empty

[bkurzbuch]

I have a rather length piece of code for retuning a chart on a Ins. flyer. The problem I'm having is that the "Subscriber" is always listed because it's there account but may not have any service. The charts is populating by service date then family member (i.e. Subscriber first, then spouse, child 1 and so on). What was done and then amount payed by the Ins company. with a total returned at the end of the chart. I need to code it if field "total payed by xyz" is empty then do not populate chart with subscriber. Code is below.

Thanks for looking

 

f(FusionPro.inValidation)

Rule("OnJobStart");

 

var FirstRow = "";

var NumLinesInRec = "";

var CurServiceDate = "";

var PrevServiceDate = "";

var totalPaid = 0;

var frame1 = FindTextFrame("TablePg1");

Subscriber_Name = "";

Spouse_Name = "";

Child1_Name = "";

Child2_Name = "";

Child3_Name = "";

Child4_Name = "";

Child5_Name = "";

Child6_Name = "";

Child7_Name = "";

Child8_Name = "";

FirstRow = StartRow[CurrentRecordNumber()-1];

NumLinesInRec = (StartRow[CurrentRecordNumber()]) - (StartRow[CurrentRecordNumber()-1]);

 

 

for (var i=1;i<=NumLinesInRec;i++)

{

if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "SUBSCRIBER")

Subscriber_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "SPOUSE")

Spouse_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 1")

Child1_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 2")

Child2_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 3")

Child3_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 4")

Child4_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 5")

Child5_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 6")

Child6_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 7")

Child7_Name = CreateName(FirstRow-1+i);

else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 8")

Child8_Name = CreateName(FirstRow-1+i);

}

 

FusionPro.Composition.AddVariable("Rule_Employee",ToTitleCase(Subscriber_Name));

FusionPro.Composition.AddVariable("Rule_ADN",XDF.GetFieldValue(FirstRow, "ADN"));

FusionPro.Composition.AddVariable("Rule_Comp Name",XDF.GetFieldValue(FirstRow, "Company Name"));

FusionPro.Composition.AddVariable("Rule_Covered Since",FormatDate(XDF.GetFieldValue(FirstRow, "Covered Since"), "lm yyyy"));

FusionPro.Composition.AddVariable("Rule_AD_Line_1",XDF.GetFieldValue(FirstRow, "Current Member Addr line 1"));

FusionPro.Composition.AddVariable("Rule_AD_Line_2",XDF.GetFieldValue(FirstRow, "Current Member Addr line 2"));

FusionPro.Composition.AddVariable("Rule_City",XDF.GetFieldValue(FirstRow, "Current Member Addr City"));

FusionPro.Composition.AddVariable("Rule_St",XDF.GetFieldValue(FirstRow, "Current Member Addr State"));

FusionPro.Composition.AddVariable("Rule_Zip",XDF.GetFieldValue(FirstRow, "Zip Code"));

FusionPro.Composition.AddVariable("Rule_Bar_Code",XDF.GetFieldValue(FirstRow, "Intelligent Mail barcode"));

 

///////////////////////////////////////////////Sort Table Values///////////////////////////////////////

var Dates = [];

var UniqueDates = [];

var RowsInOrder = [];

var Dependent = ["SUBSCRIBER","SPOUSE","CHILD 1","CHILD 2","CHILD 3","CHILD 4","CHILD 5","CHILD 6","CHILD 7","CHILD 8"];

 

for(var i=1;i<=NumLinesInRec;i++)

{

Dates.push(XDF.GetFieldValue(FirstRow-1+i, "Service Date"));

}

 

Dates.sort(function(a, b){

var aa = a.split('/');

aa.splice(0,0,a.split('/').pop());

aa.pop();

aa.join();

 

var bb = b.split('/');

bb.splice(0,0,b.split('/').pop());

bb.pop();

bb.join();

 

return aa < bb ? -1 : (aa > bb ? 1 : 0);

});

 

for(var i=0;i<Dates.length;i++)

{

if(UniqueDates.indexOf(Dates) < 0)

UniqueDates.push(Dates);

}

 

for(var h=0;h<UniqueDates.length;h++)

{

for(var k=0;k<Dependent.length;k++)

{

for(var i=1;i<=NumLinesInRec;i++)

{

if((UniqueDates[h] == XDF.GetFieldValue(FirstRow-1+i, "Service Date")) && (Dependent[k] == Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info")))))

{

RowsInOrder.push(FirstRow-1+i); //Stores a list of XDF row numbers sorted by ascending date and level of dependent

}

}

}

}

 

//////////////////////////////////////////////////Create Table///////////////////////////////////////////

var table = new FPTable;

table.AddColumns(9089,21040,13537,13912);

table.AddRows(NumLinesInRec+3);

 

table.Rows[0].Type = "Header";

 

 

 

table.Rows[0].SetContents("Date of Service","Name","Benefit Category","Amount Paid by<br>Delta Dental Iowa");

 

for(var i=1;i<=NumLinesInRec;i++)

{

CurServiceDate = XDF.GetFieldValue(RowsInOrder[i-1]/*FirstRow-1+i*/, "Service Date");

table.Rows.SetContents(CurServiceDate,CreateName(RowsInOrder[i-1]/*FirstRow-1+i*/),XDF.GetFieldValue(RowsInOrder[i-1]/*FirstRow-1+i*/, "Benefit Category"),FormatNumber("#,###.00",XDF.GetFieldValue(RowsInOrder[i-1]/*FirstRow-1+i*/, "Total Paid by Delta Dental of Iowa")));

 

if(CurServiceDate != PrevServiceDate)

{

for(var j=0;j<4;j++)

table.Rows.Cells[j].SetBorders("Thin","Black","Top");

}

PrevServiceDate = CurServiceDate;

 

totalPaid += StringToNumber(XDF.GetFieldValue(FirstRow-1+i, "Total Paid by Delta Dental of Iowa"));

}

 

table.Rows[NumLinesInRec+2].SetContents("TOTAL DENTAL BENEFITS PAID BY DELTA DENTAL:<br>$"+FormatNumber("#,###.00",Round(totalPaid,2)));

 

 

 

for(var col=0;col<4;col++)

{

table.Rows[0].Cells[col].SetBorders("Thin","Black","Bottom");

table.Rows[0].Cells[col].VAlign = "Bottom";

for(var row=0;row<NumLinesInRec+1;row++)

table.Rows[row].Cells[col].HAlign = "Center";

}

for(var col=0;col<3;col++)

{

for(var row=0;row<NumLinesInRec+1;row++)

table.Rows[row].Cells[col].SetBorders("Thin","Black","Right");

}

table.Rows[NumLinesInRec+1].minHeight = 3924;

table.Rows[NumLinesInRec+2].Cells[0].HStraddle = 4;

table.Rows[NumLinesInRec+2].Cells[0].HAlign = "Center";

table.Rows[NumLinesInRec+2].Cells[0].VAlign = "Middle";

table.Rows[NumLinesInRec+2].minHeight = 6400;

table.Rows[NumLinesInRec+2].Cells[0].SetBorders("Thin","Black","Top","Bottom","Left","Right");

table.Rows[NumLinesInRec+2].Cells[0].Font = "HelveticaNeueLT Std Bold Cn";

table.Rows[NumLinesInRec+2].Cells[0].PointSize = 15;

 

FusionPro.Composition.AddVariable("Rule_table",table.MakeTags(),true);

return table.MakeTags();

[Dan Korn]

First, you keep mentioning the word "chart," but your rule is making a table. (A chart is made with a chart frame, where you select a line, bar, or pie chart. A table is made with a rule like what you have here.)

 

So you just want to skip a row in the table if there's no data for it, right? That's a very common thing to want to do.

 

The problem is, it's very hard to analyze a rule like this without having the data and the rest of the job to be able to actually compose it. But I'll give it a shot.

 

First, what does this code do?

for (var i=1;i<=NumLinesInRec;i++)
{
if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "SUBSCRIBER")
Subscriber_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "SPOUSE")
Spouse_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 1")
Child1_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 2")
Child2_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 3")
Child3_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 4")
Child4_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 5")
Child5_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 6")
Child6_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 7")
Child7_Name = CreateName(FirstRow-1+i);
else if(Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD 8")
Child8_Name = CreateName(FirstRow-1+i);
}

What does the CreateName function do? And are you actually using these variables such as "Child8_Name" somewhere? Because I don't see them referenced elsewhere in the code. Or is this code not at all relevant to the question about tables? (If not, then please don't include it, as it just makes things more confusing.)

 

Anyway, you could reduce that code quite a bit by making Child_Name an array and iterating to add items to it, like so:

Child_Name = ['']; // empty element 0 (zero)

for (var i=1;i<=NumLinesInRec;i++)
{
   for (var j = 1; j <= 8; j++)
   {
       if (Trim(ToUpper(XDF.GetFieldValue(FirstRow-1+i, "Dep Info"))) == "CHILD " + j)
           Child_Name[j] = CreateName(FirstRow-1+i);
   }
}

Then you can reference the items by index, such as Child_Name[2].

 

Please also note that, for code, you can click the "Go Advanced" button at the bottom of the edit box here on the forum, and then click the # button to put code in a Code block, as I've done above.

 

Anyway, to go back to the original question about table rows (that was the question, right), it's not clear to me exactly under which conditions in the data for a table row should be considered "empty" so that the row is excluded. But I think that, instead of adding all the rows at the start like so:

table.AddRows(NumLinesInRec+3);

And then referencing the rows by number like so:

table.Rows[i].SetContents(/* ... */);

You want to selectively call table.AddRow() and use the returned FPTableRow object, something like this:

for(var i=1;i<=NumLinesInRec;i++)
{
   CurServiceDate = XDF.GetFieldValue(RowsInOrder[i-1]/*FirstRow-1+i*/, "Service Date");
   if (Field("total payed by xyz")) // whatever you mean by <<if field "total payed by xyz" is [not] empty>>
   {
       var tableRow = table.AddRow();
       tableRow.SetContents(/* ... */);
       // ...
   }
   // else don't add a row
}

[bkurzbuch]

Hi Dan

My apologize for the mess. I know there's a proper way to post my code, but was unsure how to do it. Thank You.

I would love to explain the reason for the lengthy code, but this project was so far over my head that we had PTi build it for use. Of course after a couple of production runs there are some small edits. I've been able to make all of them accept for excluding the subscriber in the chart if "total paid by" field is empty. Due to HEPA regulations i can't really post much more. Was hoping for a insert this line here kind of fix. Building arrays and charts is still beyond my abilities. I will have to do some more research on building these to better understand on how to trouble shoot them. Thanks for taking a look Dan

[step]

I agree with Dan that it's really difficult to tell for sure from the very little information you've given. I understand that the data is sensitive but it's not that hard to create "dummy" data and it really goes a long way when you have code like yours that references functions that you haven't included.

 

Anyway, if you're looking for just a band-aid fix that will not return the table if the 'total' is 0, then you can just change your return line to this:

return totalPaid ? table.MakeTags() : '';

 

Of course, that's if you aren't referencing this 'Rule_Table' variable anywhere (but like I said - how would I know?). If you are then you probably want to make the last lines look like this:

var result = totalPaid ? table.MakeTags() : '';
FusionPro.Composition.AddVariable("Rule_table",result,true);
return result;

[bkurzbuch]

Thanks STU

 

To help me better understand this would you need just the data or the template also. With this kind of code I feel like a 12 year old with dads car keys. I understand the logic behind it just not how to get where I need to go. If you have time to look at it I'll post it later today.

 

Thanks again for all your help.

[step]

Template, data, and the external data file you're using to make the table. If you're running FusionPro 9, I won't personally be able to look at it for you (since I'm running an older version) but I'm sure someone on the forum would be happy to take a look.

[Dan Korn]

Template, data, and the external data file you're using to make the table. If you're running FusionPro 9, I won't personally be able to look at it for you (since I'm running an older version) but I'm sure someone on the forum would be happy to take a look.

If you are using FusionPro 9.2 or later, you can check the "Preserve 9.1 Collect Format" box on the Collect dialog, which should enable users of reasonably recent versions of FusionPro (8 or 9) to at least open the collected files.

 

But yes, someone looking at the job would basically need everything that is required to build out the tables and reproduce what you're seeing.

 

Step, for all the contributions you make here, I'm pretty sure we can swing an upgrade to FusionPro 9 for you. Contact me off-list.

[step]

Step, for all the contributions you make here, I'm pretty sure we can swing an upgrade to FusionPro 9 for you. Contact me off-list.

 

I appreciate that, Dan! I actually have a FP9 license, though. I just can't upgrade my desktop version until our SysAdmin upgrades our production servers to FP9. But, if you'd like to muscle him into making that upgrade, I'll DM you his phone number