If Not Exist 1

[aus0137]

Does Crystal have a command similar to "if Not Exist".

I am selecting field A from one database and Field B from another database. In some cases field A does not exist in the database.

If both A and B exist then both show on report. If B exists but A doesn't, then B does not show. I always want B to show, regardless of whether A is present or not.

I want to have a formula that says something like

If not exist A then 0
else
A

Therefore if A doesn't exist it will default to a zero, and then will report both A and B.

A will be Zero and B will be correct value.

Or is there another better way to handle this?

Hope this makes sense, any help appreciated.

 

[dgillz]

if isnull({YourField}) then <<something>> else <something else>> Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Madawc]

You also need to put the ISNULL test first. Crystal has the helpful habit of giving up completely and returning no result if it encounters a NULL where it wasn't expected. Madawc Williams
East Anglia
Great Britain

 

[Naith]

Aus,

Can you confirm that you are joining B to A with a left outer join? Because that should take care of this for you, without the need for any formulae.

Naith

 

[kenhamady]

Naith is correct, as long as you don't add any critieria that evaluate fields from A. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[aus0137]

Sorry Naith and Ken, but I am unfamiliar with a &quot;left outer join&quot;.

I tried the if isnull and it did not resolve the issue.

I have a debtors master file with debtor code and MTD sales in one database.

In another database I have debtor budgets. This file holds budgets for different years distinguised by a field as 0 for current, -1 for last year etc. The databases are linked via the debtor code.

My selection is show all debtors with mtd not equal to zero, and where budget year = 0.

actual formula looks like this;
{ARMASTER.SALES_MTD} + {ARMASTER.SALES_YTD} + {ARMASTER.SALES_LY} <> 0.00 and
{SYSBUD.YEARS_AHEAD} = 0

What happens is that if the debtor has sales and a budget figure then records show.
If debtor has sales, but no budget has been entered, then the record does not show.

I used the formula
if isnull ({SYSBUD.BUDGET_07})then 0 else {SYSBUD.BUDGET_07}
for the budget column but this made no difference.

If a budget of zero has been entered then all is well, it is only where no budget has been enterd at all. ie there is no line in the database for the debtor for current year.

Thanks

 

[Naith]

You need to correct your joins, if possible. When you say that you are querying across two databases, how are you doing this?

Are you having Crystal actually query the two databases across a database link, or are you having Crystal query a query which accesses the two databases instead.

If you're doing the former, click the Database menu, and go to the Visual Linking Expert. On the line illustrating the link between the two tables, right click, and choose Options. Here, you need to change your link from Equal to Left Outer Join. A left outer join takes all the records from the first table, and only those which match from the second table. However, all your selection criteria must be imposed on the left table. If any conditions are applied to the right table, the left join is nullified, and becomes an equal instead - which is what you have at the moment.

Currently, your last criteria is such a criteria which would nullify such a join:

{ARMASTER.SALES_MTD} + {ARMASTER.SALES_YTD} + {ARMASTER.SALES_LY} <> 0.00 and
{SYSBUD.YEARS_AHEAD} = 0

I don't know your data, but does {ARMASTER.SALES_YTD} not assume that the year is a current year, or can you use this field historically?

There's a help file on Left Outer Joins in Crystal, if you press F1.

 

[aus0137]

I found the info on the join, but our files are pc format and not sql, and therefore an equal join is the only option allowed.

Our files are from our accounting system which is written in Dataflex/Powerflex. We are provided with two DLL files which allows Crystal to view the files.

Both files are selected in Crystal and linked via the debtor code. You can only link one-way, from the budget file to the master file, because the debtor field in the budget file is not indexed.

The MTD field from the master file is a fixed field and not related to the year.

Where there has been no budget amount entered for the debtor in the budget file, then the debtors record will not display. i.e. there is no record in the budget file for that debtor code. The debtor code is only added to the budget file when a budget figure is entered.

Therefore you can have 4,000 debtors in the master file but only 2,000 in the budget file. Therefore I assume that Crystal is ignoring the debtor if it does not find it in the budget file.

I figure that a non-existant record is different to a null record. Hence my original question about a &quot;ifnotexist&quot; type function.

It looks like my easiest option is to enter a zero budget for every single debtor. (About 4,000). Which is ok. But my concern is with new debtors taht are added during the year. If we forget to enter a budget for every new customer, our sales totals will be incorrect.

Thanks

 

[kenhamady]

One small correction, when you link using a 'Datafile' (non-odbc) join, the join type will SAY equal, but it will behave as a left outer join. Putting criteria on the outer table will make it behave like an equal join.

Since you can only join one way, this doesn't help you.


You can also use subreports, but this is a slow option. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com