Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: 2 Tables matching with each other Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Topic: 2 Tables matching with each other
    Posted: 10 Apr 2009 at 11:14am
Hi All,
 
I have a problem and hope someone can help me out.
I am  an user of Crystal Reports XI without any authorisation to amend databases or tables.
 
Example:
 
The report is set up as below:
 
Table 1:                               Table 2:          
LocationMaster                  Location used     :        current Qty:
DC-003-001-00                  DC-003-001-00                   0  
DC-003-002-00                  DC-003-002-00                   0
DC-003-003-00                  DC-003-003-00                   0
DC-003-004-00                  DC-003-004-00                   0
DC-003-005-00    =>problem!! not shown in report             
 
As you can see,I dont have a record in Table 2 for DC-003-005-00,
When I create a boolean,i am not able to select on true or false,the report does not show this record. simply because it has been never used.
 
How can I look up every record from Table 1 in Table 2 and if  there is no record in Table 2 it will be shown in the report?
 
I created following formula;
 
If (locationmaster=location used and qty=0) then 'EMPTY' else 'OCCUPIED'
 
The qty field is set numerical.so if there is no record there is no number.
And it seems that in Crystal you cant use " '' for null.
 
I tried Isnull,IsEmpty etc. but could just not found the solution.
 
For better understanding:
In Excel you have function Vlookup,and if you look up a value in another list and there is no record you get #N/A.
Just something like this i want in Crystal.
 
Currently i am using two Crystal reports to get my desired overview,but i am sure it is possible in one report.
 
greetings
Memoli
 
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Apr 2009 at 11:39am

If I understand your problem correctly you can handle this with how you set up your joins between the 2 tables.

If table2 is always the table that is "missing items" you can use a left join to include all the items from table1 regardless if they connect to an item in table 2.
If both tables have items that don't match you would have to do a full outer join.
Once you do this it you can use an isnull() to find these items
isnull(table2.id) finds items where their was no match to table 1.


Edited by DBlank - 10 Apr 2009 at 1:25pm
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 10 Apr 2009 at 1:21pm
Dear DBlank,
 
Thanks for your quick answer.
 
I will try this tuesday at work.
 
I am just reading the manual and it seems you are right.
 
thanks again and let you know the result.
 
Greetings
Memoli
 
Dear Dblank,
 
I tried it today at work and it seems working correctly.(linking with left join in database expert)
However i have the following problem:
 
i created a formula to check if field x has a null value or is equal to zero.
I have done this with an if-function.
if it is true then you get 'EMPTY' if not 'NOT EMPTY'.
With select expert i am not able to select on  EMPTY.
 
I created two groups.
 
example:
 
Group 1:                   Group 2:            Total QTY (sum)        Formula:
DE-003-004-00      DE-003-004-00                  10                not empty
DE-003-005-00                                                                    empty
DE-003-006-00      DE-003-006-00                   0                 empty
 
Do you know a way to select only empty?
I checked in the helpbox but could not find any answers.(parameter?)
 
already thanks in advance.
 
Gr.
Memoli
 
 


Edited by Memoli28 - 14 Apr 2009 at 9:04am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Apr 2009 at 9:14am
Well it is kind of redundant to create a formula to check for empty and then to use that in a select statement. You can just use the same formula idea to do your select:
(isnull(table.field)=false or table.field>0)
That being said I am not sure that you want to do this. Doesn't this re-exclude the data from the report that you just brought in via the left-join? Why use the left join if you are just going to exclude it again?
Are you just trying to supress it rather than exclude it?
What is your ultimate goal here?
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 14 Apr 2009 at 10:17am

Dear Dblank,

my goal is to see the following:
(based on the example before)
 
Group 1:                Group2:                Sum:         Status:
DE-003-005-00                                                     empty
DE-003-006-00      DE-003-006-00      0               empty
 
I also tried to select on sum-field but then i will see only the zero-values.
blank field (DE-003-005-00) is not shown,otherwise i will ,like you said,exclude it.
 
Group 1 comes from Table 1.
Group 2 comes from Table 2.
 
and the total sum is based on sum of group 2.
 
So status which is equal to 'NOT empty' i want to exclude.
 
> 0 will show NOT empty.
 
 
It is also very strange not being able to select in select expert.
Possible cause maybe by the grouping.
 
 
Hope to inform you well.
 
Gr.
Memoli
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Apr 2009 at 10:35am
Got it. Misunderstodd a bit. Sorry about that. Try this...
Go into select expert.
Click on Show Formula
Click on Group Selection option (you have to use this when selecting on a summary like SUM of a group).
Click on formula editor
Add your formula:
isnull(sum({table.field}, {table.fieldgroupon})) or sum({table.field}, {table.fieldgroupon})=0
You obviosly have to change this to the right field grouping options...
Let me know if that works or not.
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 14 Apr 2009 at 10:41am
Dear Dblank,
 
Thanks.
 
let you know the result tomorrow.
 
gr.
Memoli
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 15 Apr 2009 at 11:05am
Dear Dblank,
 
i tested this today and it gives the following error in the formula:
 
the grouponfield can not be used as condition.(sum function)
 
gr.
Memoli
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Apr 2009 at 11:15am
Can you post the exact formula you used including actual table and field names?
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 15 Apr 2009 at 11:19am
tomorrow i post the complete report design since i am now not at work.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.