Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula for empty field by outer join

Status
Not open for further replies.

sharugan

Programmer
Jul 23, 2007
14
US
I get empty rows as shown below due to outer join and that record is not available in DB.

Table1 table2 table3
1 cat cow
2 monkey

I am get empty data in table2 / table2 since the data is not available and want to apply the formula on the data base field to check if the field = empty and display "No Data" .But when i apply formula in the data base field,
its not allowing and says that you have to return boolean field.
Can any one tell me how to apply formula in the data base field that has empty value not sure it could be null also since outer join the data is not there . the outer join just shows the empty value.
 
A little tricky to understand your requirement, but I'd try using a formula in lieu of the table3 field, as in:

if isnull({table3.field}) then
"No data"
else
{table3.field}

-k
 
Thanks for the reply.
When i use the formula in the database field , it says that the return type of the formula should be boolean
Should i remove the database field and then use the formula field.If so how do i display the database field and add the formula .
Could you please explain in details.
 
There are two questions
1)I have data base fields to dispaly the field data from three different tables.Since i have used outer join , i get some field in the table 1/tabl2/table3 emtpy , I want to check the field and display "No data"
i tried this in the foumula selection in the data base level , but it says I can only return boolean I did what you mentioned here
2) I have also added one more formula field to check if all the fields have data , them display "M" means match data else "UM" un matched data.If i have data in all the colums in displays "M" but if i don't have data , its not displaying "UM" don't know why
Please help me reolve the issue
I appreciate it
 
I think synapsevampire meant a Formula Field. If you wanted to use it to control display of a database field, you'd put it as isnull({table3.field}). But this would only make sense if you set it as the property of a field that had data.

For your second question, I suspect you are testing for a value and then for a Null. That doesn't work in Crystal, where formulas always stop when they hit a null, unless IsNull is used.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top