Linked tables omitting data
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22305
Printed Date: 29 Apr 2024 at 1:33pm
Topic: Linked tables omitting data
Posted By: crystalnewbie33
Subject: Linked tables omitting data
Date Posted: 10 May 2017 at 8:41am
Hello,
I have linked two tables - billed & paid. I am trying to create a report that will list all the billed customers and have a column that will show whether they are paid or not. I have linked the two tables by invoice #. Here are the main headers:
Invoice Customer Paid
The customer's name comes from the billed table and the paid field comes from the paid table. When I insert the {paid.paid} field into the report, it will only show the invoices that have been paid. I would like ALL the invoices to show in the report and the unpaid invoices to either show as blank in the Paid column or "N". I tried creating a formula field for the Paid column as follows:
if {billed.invoice} = {paid.invoice} then
formula = {paid.paid} else formula = " " end if
I sincerely appreciate any help, thanks.
|
Replies:
Posted By: DBlank
Date Posted: 10 May 2017 at 10:12am
You need to change your join to an outer join (likely left).
Keep in mind that additional select criteria might make it an inner join again,
|
Posted By: crystalnewbie33
Date Posted: 10 May 2017 at 10:35am
|
Wow! I did not realize there were different link options. Thank you so much, I'm going to read up on the types of joins and their uses. I've had similar troubles in the past with other reports and I'm thinking this changing the joins may have fixed the problems. Thanks, DBlank
|
Posted By: DBlank
Date Posted: 10 May 2017 at 10:39am
note that in crystal adding in two tables does not enforce the join. It is only if you choose to enforce it in the join set up or if you use a field from both ends of the joined tables. This is why you saw rows "disappear" when you used the first field from paid table. It was enforcing the join at that point.
There are a lot of posts re: this so search on phrases like
left join
outer join
enforce
|
Posted By: crystalnewbie33
Date Posted: 10 May 2017 at 10:51am
|
I see, that is good to know. I'll do some searching & studying. Thanks again
|
|