Identifying incomplete records 1

[suemon]

Hi
Hoping someone can help. Am still really new to crystal reports and struggle with even the basics. Am booked in to another course but have a current requirement to resolve.

I need to write a basic report in crystal v9 that will identify (and then evaluate) incomplete records within a lotus notes customer record database.

I have a list of fields for each customer record that must be filled in for the record to be considered complete. For example, Co. Name, address, phone, cust type etc.

I firstly need to identify the number of records that are 'incomplete' (ie. don't have all the required fields at least populated) then compare them to the number of records that are 'complete'.

This is simply so that our management can take a 'big brother' view of the database managers performance.

Can anyone help?

 

[Madawc]

Do a formula field. Do something like

if isnull ({phone})
or {phone} = spaces
then "Incomplete"
else "Complete"

You'd need to extend this for every field, and maybe check for people using full stops or similar to get round a compulsory field. You could also separate different sorts of incomplete records, by a more complex set of tests.

Remember that Crystal formulas stop whenever they encounter a null, unless isnull is used first.

Having got your formula field, you should then be able to group by it, and count the number in each group etc.

As for motivation, if you've ever had to trace something using incomplete data, you'll understand the reasons why there are checks that forms are filled out.

Years back, before e-mail etc., I saw a film about how the Royal Mail try to deliver incomplete letters. One was addressed to "Mr Jones", no other details. Someone remarked that they might at least have said which county.

Madawc Williams (East Anglia, UK)

 

[suemon]

Hi Madawc,

You'll have to excuse my ignorance I'm not sure I know what you mean. Are you saying that I've got to build up the one formula to resemble this?

if isnull({phone})or {phone}= " "
then "Incomplete" else "Complete"
????
if isnull({zip})or {zip}= " "
then "Incomplete" else "Complete"
????
if isnull({fax})or {fax}= " "
then "Incomplete" else "Complete"

I've put question marks because I don't understand how to link the tests. Is there something akin to an AND/OR function that would work?

Basically I need to retrieve the records that have incomplete in ANY of the fields so I thought I'd have to do something like this....
If phone is blank
AND/OR
zip is blank
AND/OR
fax is blank... then incomplete.

Do you see what I mean?

 

[Naith]

If you want to extract records with unpopulated fields, you can use the following approach in your selection criteria:

(
IsNull({Phone})
OR
IsNull({Fax})
OR
IsNull({Customer.Customer Name})
)

This will only return records with null values in any of the record set, instead of doing "Complete" / "Incomplete" checks on a field by field, record by record basis.

Naith

 

[Turkbear]

Hi,
If, however, you need all the records use( as you suspected) this formula:

(
if isnull({phone})or trim({phone})= ""
OR
 isnull({Name})or trim({Name})= ""
OR
 isnull({Fax})or trim({Fax})= ""
OR
 isnull({Zip})or trim({Zip})= ""
OR
.....)
Then 'Incomplete' else 'Complete'

You get the idea: One test for each field in an OR chain- Use this formula as a Group field and list details you need to see in its details section..( The trim() function only applies to string fields for numbers use {numberfield} > 0 as the alternate null test).






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[suemon]

Hi Guys,

thanks for the suggestions. Tried yours Naith and somehow I'm still getting records selected that are in fact complete. Don't know where I'm going wrong.

Turkbear,
I'm not sure I understand what you suggested but tried it and keep getting error message that the keyword then is missing

This is my code
Any ideas where I'm going wrong?

(if isnull({Address}) or trim ({Address})=""
OR
if isnull({City}) or trim ({City})=""
OR
if isnull({Zip}) or trim ({Zip})=""
OR
if isnull({Categories}) or trim ({Categories})=""
OR
if isnull({INDUSTRY}) or trim ({INDUSTRY})=""
OR
if isnull({MainPhone}) or trim ({MainPhone})=""
then "Incomplete" else "Complete"

 

[Madawc]

You've put "OR if" when Crystal expected just "OR". You're writing it like COBOL, or half COBOL and half Crystal. Crystal expects IF .. THEN ...

Madawc Williams (East Anglia, UK)

 

[suemon]

Shoot, should have seen that. Solves the error message thanks. But am still left with the problem of it calling up completed records nonetheless. Sure I'm missing something there too. Will look harder.

Thanks, you guys have been fab! I'm sure you'll be hearing from me if I can't see it.

 

[Naith]

Show me the SQL that your report is generating.

 

[Naith]

...and your full selection criteria.

 

[suemon]

Am working from one Lotus notes 'table'/ view as datasource
it contains all required info (no joined tables) Can't see the SQL statement. Using crystal v9 how do I get it?
Selection criteria is as follows.

isnull({Address})
or
isnull({City})
or
isnull({Zip})
or
isnull({Categories})
or
isnull({INDUSTRY})
or 
isnull({MainPhoneNumber})

 

[Naith]

Go to the Database menu, then Show SQL Query.

 

[suemon]

Tried that. Show SQL is an unavailable option on the menu though. Could it be that my crystal v is V9 Professional and that this feature isn't avail? Is there any other way to view it that you know of?

 

[Naith]

No, your version has it alright. I just checked a v9 Pro just now, and it's there. I take it you mean it's an option on your DB menu, but it's greyed out, rather than it's not there.

Are you sure you're using a table or view as your datasource, and not a stored procedure?

 

[suemon]

Yes I mean it's greyed out. Sorry must be my foreign accent. I tend to confuse people all the time.
And I am definitely using a view as datasource. Had to write it in lotus designer myself.

 

[Madawc]

If you're stuck, try my earlier suggestion of splitting the tests and see which one is not working as you expect.

You could copy bits to individual formula fields and see the result. Or if you take out the 'if', you get a Boolian which will return true or false.

Add a detail line below the regular one and see what results you get. Something about your data must be different from what you suppose it to be.



Madawc Williams (East Anglia, UK)

 

[lbass]

Your selection formula will limit the records coming into the report only to those which have null fields. I thought you wanted to consider blanks as well AND completed records. Try removing the selection criteria and then use a formula like the following to check for incompleteness (this is the formula corrected earlier):

(if isnull({Address}) or trim ({Address})=""
OR
isnull({City}) or trim ({City})=""
OR
isnull({Zip}) or trim ({Zip})=""
OR
isnull({Categories}) or trim ({Categories})=""
OR
isnull({INDUSTRY}) or trim ({INDUSTRY})=""
OR
isnull({MainPhone}) or trim ({MainPhone})=""
then "Incomplete" else "Complete"

-LB