How to display records when one field is empty? 1

[paula]

My report looks something like this (based on a SQL database - knowhow items records):

Title Created Date Created By Assigned To


Some records will hold data for 'Created By' but not 'Assigned To'. Where this is the case, the record does not show in the report at present.

I want to show all records created, whether or not they have been assigned to someone, with a blank entry in 'Assigned To' if appropriate. Can anyone suggest a formula or option to allow this, please?

 

[kenhamady]

Are these fields coming from multiple tables?

You probably need an outer join to one of the tables (the one that may not have matches).

What tables are used by the report, and how are they linked? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[paula]

Thanks Ken...here's a run-down of the tables (including the alias you suggested to me the other day!):

------------------------------------------------------------
KnowhowItem
ID (Linked: KnowhowItem.ID to KnowhowDraftList.ID and
KnowhowItem.ID to KnowhowQAList.ID)
Title (in report)
CreatedDate (in report)
CreatorID (Linked: KnowhowItem.Creator ID to AppUser.ID)

AppUser
ID (Linked: KnowhowItem.Creator ID to AppUser.ID)
FullName (in report)

KnowhowDraftList
KnowhowID (Linked: KnowhowItem.ID to KnowhowDraftList.ID)
UserID (Linked: KnowhowDraftList.UserID to AppUser2.ID)

KnowhowQAList
KnowhowID (Linked: KnowhowItem.ID to KnowhowQAList.ID)
UserID (Linked: KnowhowQAList.UserID to AppUser2.ID)

AppUser2(alias of AppUser)
ID (Linked: KnowhowQAList.UserID to AppUser2.ID and
KnowhowDraftList.UserID to AppUser2.ID)
FullName (in report)
------------------------------------------------------------

AppUser2.FullName provides the name of the person to whom the knowhow item has been assigned (i.e. who's queue it's in to work on). The SQL joins are all equal joins at present, but I have dabbled with outers without much success.

All I really want is to get a full listing of all records, with both the creator name (always present) and the assignee name (if there is one) on one report.

Again, many thanks for your assistance.

Paula

 

[kenhamady]

I think you need a user2 and user3.
You are trying to link to this table 3 times, and you only have 2 instances. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[paula]

Thanks very much, Ken.

Problem solved - my sanity saved!

Your input into these forums is invaluable.

Paula