Community
Showing results for 
Search instead for 
Do you mean 
Reply

Need to stop report from printing section if one field is blank.

Loyal Listener
Posts: 7
Country: United States

Need to stop report from printing section if one field is blank.

I have modified the "Opportunities Referred By" report slightly, using VBScript to display the data fields as blanks if there is no information in a certain field. Is there a way to instead stop the report from printing that section at all? With the way I have scripted it the pages will still print with the headers and footers, but without the data fields printing. I don't want to set the max pages to 1 or 2 because the report can vary heavily on page range from week to week.
Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: Need to stop report from printing section if one field is blank.

You have a couple of problems with what you are trying to do. The VB script you are using only blanks the display of the unwanted record, it doesn't block the processing of the record. You would find that if you made the border of the fields solid, it would print empty fields. Another problem is that you are using an opportunity based report which means you can only limit the records processed by date range or user. There isn't any way to create a lookup and run the report for only those contacts and opportunities you want to include in the report. If you created a contact based report with an opportunity subreport you would have the option of running the report for a lookup.

 

Bottom line, what you are trying to do would not be easy to accomplish. There is likely a couple of different ways to accomplish what you want but likely would require a different report structure (contact rather than opportunity) and some complex scripting.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Loyal Listener
Posts: 7
Country: United States

Re: Need to stop report from printing section if one field is blank.

The only criteria I actually want to filter by is date range and whether or not one opportunity field contains data. Here's the code I've got to blank the fields.

 

if LeadGenerator1 = "" THEN
    custom7.text = "N/A"
ELSE
    custom7.text = LeadGenerator1.text
END IF

if LeadGenerator1 = "" THEN
    custom10.text = ""
ELSE
    custom10.text = Field6.text
END IF

if LeadGenerator1 = "" THEN
    custom11.text = ""
ELSE
    custom11.text = Field5.text
END IF


if LeadGenerator1 = "" THEN
    custom12.text = ""
ELSE
    custom12.text = Field1.text
END IF


if LeadGenerator1 = "" THEN
    custom13.text = ""
ELSE
    custom13.text = Field8.text
END IF


if LeadGenerator1 = "" THEN
    custom14.text = ""
ELSE
    custom14.text = Custom5.text
END IF


if LeadGenerator1 = "" THEN
    custom15.text = ""
ELSE
    custom15.text = Custom4.text
END IF

Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: Need to stop report from printing section if one field is blank.

I knew what your scripts looked like, I tried that over four years ago with the same results. When a record within your date range is processed, the report generator is triggered to include your custom fields, whether they are filled with data to display or not. As I suggested, try setting the border of your custom fields to solid and you will see that the empty fields are displaying. Because the custom fields do not have an available property for Can Shrink, the empty field will display at full height.

 

I have experimented with using VB scripts to modify the actual template code during the execution of the report but it's very tricky and I've found that it isn't consistently reliable.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 48
Country: USA

Re: Need to stop report from printing section if one field is blank.

Sorry -- old message, but I have a question about a potential work around. Is there any way to set the Detail section's height to 0 based on a field value?

Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: Need to stop report from printing section if one field is blank.

Would it be possible? Possibly. It is possible to modify field/section parameters at run time using embedded Visual Basic script programming. However to set a section to minimum height you would also need to set all the fields in the section to minimum height and have their top position be 0.0 inches. 

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 48
Country: USA

Re: Need to stop report from printing section if one field is blank.

I got that far. But where I got hung up was on date and number fields. I had to set the values of the controls to "" to be able to "faltten" them.  However, the date and number fields wouldn't flatten.  With the character based controls, I could put:

 

If fieldofreference = "" then

 fieldiwanttohide.text = ""

 ...

 ...

 ...

 fieldiwanttohide.height=0

 ...

 ...

 ...

 section.height = 0 

End if

 

But when I tried that with the date and number fields, the result was a blank line across except for a date field with mm/dd/yyyy and zeroes in the two number fields. They wouldn't go to 0 height. I tried .value, too, but it didn't make any difference.

 

Any ideas?

Platinum Elite Contributor
Posts: 6,651
Country: USA

Re: Need to stop report from printing section if one field is blank.

That's because the database date and time fields don't store the date or time you see but references to a standard and the fields presented to the report are formatted from the database data and won't be blank but will at minimum display the formatting.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129