printing "No details in Field'" when field empty 2

[Bryan - Gendev]

In my report I want to print "No details in Field'" when field empty in underlying table.

Where would I place the Alternate text please?

is it an iif empty(fieldname,data,alttext) somewhere?

Thanks

GenDev

 

[Mike Lewis]

There are two ways you can do this:

First, in the field expression (within the report), you can do this:

[tt]IIF(EMPTY(MyField), "No details in Field", MyField)[/tt]

However, if MyField is not a character field, you will have to convert it to character, like this:

[tt]IIF(EMPTY(MyField), "No details in Field", TANSFORM(MyField))[/tt]

The second option is to add a new field to the report. Place it directly over the first one (the one that contains the field itself). In this new field, make the expression equal to "No details in Field" (including the quotes). Then use the Print When of both fields to control the printing. So, in the first field, the Print When would be [tt]NOT EMPTY(MyField)[/tt], and in the second field it would be [tt]EMPTY(MyField)[/tt].

But I think the first method would be easier.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

If you have NULL values, instead of empty fields, which could also result from a left outer join with no join, then it's even easier and you simple SET NULLDISPLAY TO "No Details in Field".
If you have no result record at, like in your previous join thread, then you have a problem, as you can't run a report with an empty cursor. I often enough then simply bring up a messagebox or display "no results" in a form instead of doing the REPORT FORM.

Another thing you can do is display a list of head data, eg customers, with a count, eg count of orders, if the report is about an order summary, so you'll know there is nothing to report for a certain customer in advance.

Bye, Olaf.

 

[TamarGranor]

Just want to point out that you don't need IIF() here if you're in VFP 8 or 9. You can use EVL(), which works like NVL(), but for empty values:

EVL(MyField, "Nothing to report")

returns the value of MyField, except when it's empty; in that case, it returns "Nothing to report".

Tamar

 

[Dan Freeman]

Thanks, Tamar! I'd forgotten about EVL() and will probably use it tomorrow.

But to Olaf's point, EVL() will just return .NULL. because .NULL. is NOT nothing or empty.

 

[vgulielmus]

Alternative solution.
In VFP9 SP2 there is a Dynamics tab. Add a condition, name it somehow and:
Apply when condition is true: = empty(MyField)
Replace expresion result with: = "No details in Field

Also somehow related to this subject.

http://www.tek-tips.com/faqs.cfm?fid=7720

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

You could also combine NVL and EVL, eg EVL(NVL(myfield,""),"no data") will turn NULL to an empty string in the inner NVL and turn that to "no data" in the outer EVL, if the field is not NULL at the start, the original value is coming out of the inner NVL and EVL will turn empty values. For numeric fields use 0 instead of "", for dates use {} or CTOD(""), etc, etc. There is at least one empty value for every data type, and this way you have a ENVL() function.

Bye, Olaf.

 

[Bryan - Gendev]

I now want to modify the output again.

I have 2 dates - edate and srtdate. If edate is empty and there is a sort date I want the ouitput to be 'Sort'+srtdate.
If both are empty I want to leave the field clear.

I tried

iif  (empty(edate),iif!(empty(srtdate),'Sort '+srtdate,edate))

but I have the syntax mixed up.

Can someone show me a correct version please?

GenDev

 

[mjcmkrsr]

You may try the code below and replace the "" with your expression to complete your statement

iif(empty(edate),iif(!empty(srtdate),'Sort '+ DTOC(srtdate),""), DTOC(edate))

hth

MK

 

[vgulielmus]

Alternative solution : use SET REPORTBEHAVIOR 90 and the Dynamics tab.
After run the following demo, double click the edate field and check the dynamics tab.
There are two conditions, named empty_both and empty_edate. The order is important.
Select on each one of them and then click Edit.

* Preparation: create the cursor and add a few rows
CREATE CURSOR aa (nId I AUTOINC,edate D,srtdate D)
INSERT INTO aa (edate,srtdate) VALUES ({},{})
INSERT INTO aa (edate,srtdate) VALUES ({},DATE(2010,01,31))
INSERT INTO aa (edate,srtdate) VALUES (DATE(2011,02,28),{})
INSERT INTO aa (edate,srtdate) VALUES (DATE(2012,03,31),DATE(2013,04,30))

* Preparation: two dynamics conditions: first when both edate and srtdate are empty, second when only edate is empty
LOCAL lcStyle
TEXT TO m.lcStyle NOSHOW
<VFPData>
	<reportdata name="Microsoft.VFP.Reporting.Builder.EvaluateContents" type="R" script="&quot;&quot;" execute="empty_both" execwhen="empty(edate) and empty(srtdate)" class="" classlib="" declass="" declasslib="" penrgb="-1" fillrgb="-1" pena="255" filla="0" fname="Courier New" fsize="10" fstyle="0"/>
	<reportdata name="Microsoft.VFP.Reporting.Builder.EvaluateContents" type="R" script="'Sort '+ TRANSFORM(srtdate)" execute="empty_edate" execwhen="empty(edate)" class="" classlib="" declass="" declasslib="" penrgb="-1" fillrgb="-1" pena="255" filla="0" fname="Courier New" fsize="10" fstyle="0"/>
</VFPData>
ENDTEXT

* Create and change the report
SET REPORTBEHAVIOR 90
CREATE REPORT aa FROM aa			&& create the report
SELECT 0
USE aa.frx EXCLUSIVE				&& change the report
DELETE FOR expr="srtdate" OR expr='"Srtdate"'	&& delete the label and field for srtdate
LOCATE FOR expr="nid"
replace stretch WITH .F.			&& uncheck stretch with overflow for the field nid
LOCATE FOR expr="edate"
replace width WITH 22300,stretch WITH .F.	&& uncheck stretch with overflow for the field edate and increase the width
replace style WITH m.lcStyle			&& add the dynamcis conditions
PACK
USE						&& end report alteration
SELECT aa

* Preview the results
REPORT FORM aa prev

MODIFY REPORT aa				&& double click the edate field and check the dynamics tab

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

I apologize. Please change in the first condition script="""" with script="''" (In Text...Endtext)

* Preparation: create the cursor and add a few rows
CREATE CURSOR aa (nId I AUTOINC,edate D,srtdate D)
INSERT INTO aa (edate,srtdate) VALUES ({},{})
INSERT INTO aa (edate,srtdate) VALUES ({},DATE(2010,01,31))
INSERT INTO aa (edate,srtdate) VALUES (DATE(2011,02,28),{})
INSERT INTO aa (edate,srtdate) VALUES (DATE(2012,03,31),DATE(2013,04,30))

* Preparation: two dynamics conditions: first when both edate and srtdate are empty, second when only edate is empty
LOCAL lcStyle
TEXT TO m.lcStyle NOSHOW
<VFPData>
	<reportdata name="Microsoft.VFP.Reporting.Builder.EvaluateContents" type="R" script="''" execute="empty_both" execwhen="empty(edate) and empty(srtdate)" class="" classlib="" declass="" declasslib="" penrgb="-1" fillrgb="-1" pena="255" filla="0" fname="Courier New" fsize="10" fstyle="0"/>
	<reportdata name="Microsoft.VFP.Reporting.Builder.EvaluateContents" type="R" script="'Sort '+ TRANSFORM(srtdate)" execute="empty_edate" execwhen="empty(edate)" class="" classlib="" declass="" declasslib="" penrgb="-1" fillrgb="-1" pena="255" filla="0" fname="Courier New" fsize="10" fstyle="0"/>
</VFPData>
ENDTEXT

* Create and change the report
SET REPORTBEHAVIOR 90
CREATE REPORT aa FROM aa			&& create the report
SELECT 0
USE aa.frx EXCLUSIVE				&& change the report
DELETE FOR expr="srtdate" OR expr='"Srtdate"'	&& delete the label and field for srtdate
LOCATE FOR expr="nid"
replace stretch WITH .F.			&& uncheck stretch with overflow for the field nid
LOCATE FOR expr="edate"
replace width WITH 22300,stretch WITH .F.	&& uncheck stretch with overflow for the field edate and increase the width
replace style WITH m.lcStyle			&& add the dynamcis conditions
PACK
USE						&& end report alteration
SELECT aa

* Preview the results
REPORT FORM aa prev

MODIFY REPORT aa				&& double click the edate field and check the dynamics tab

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Bryan - Gendev]

Vilhelm,

I get a comprehensive error dialogue when I run your code ...

XML Parse error Required white space was missing.
Line 2,Position 88. <reportdata
etc,etc

Do you get this and what does it mean?

Regards

GenDev

 

[vgulielmus]

The error is caused by script="""" from :

TEXT TO m.lcStyle NOSHOW
<VFPData>
	<reportdata name="Microsoft.VFP.Reporting.Builder.EvaluateContents" type="R" script=""""....

Please change into script="''" or script="SPACE(0)"

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania