How to compare one field with another field in same table?

[HNLboy]

Post in TekTips

I'm pretty new to Crystal and Data reporting, so not sure where this question belongs (and please excuse lack of clarity or accurate terminology).

Using Crystal 8.5 and connecting to MS SQL 7 database via OLE DB

I'm trying to write a report extracting info from one table that has all of our estimates. Some of the estimates are updated versions of a "Master"; sort of a child of a parent thing. I need a report that shows 1)'stand-alone' estimates (ones with no Master) and 2)the latest version only of any estimates based on a Master.

Here is an example of the data I'm working with:
Est. Number Master
1240 1233
1241 1233
1962
2146
2277 2146
2278 2146
2279 2146

In this example, report should only show records for estimates 1241, 1962, 2279.

I am having difficulty with criteria 1 as I don't know how to distinguish stand-alone estimates with no Masters (like est#1962 in example) from estimates that themselves serve as Masters for another record(est. #2146 in example above; must be filtered out in my report). No other criteria in table distinguishes Versions from Masters. Please help me! Thanks! HNLboy

 

[synapsevampire]

Your descripion, data and expected result differs.

Here is an example of the data I'm working with:
Est. Number Master
1240 1233
1241 1233
1962
2146
2277 2146
2278 2146
2279 2146

In this example, report should only show records for estimates 1241, 1962, 2279.

You state that "1)'stand-alone' estimates (ones with no Master)" are to be displayed, yet you arenn't displaying 2146, why is this?

It sounds like you want a Left Outer join from the Estimate to the Master, Group by Estimate, and then in the Report->Edit Selection Formula-Group place:

isnull({masterid})
or
{table.estimatedate} = maximum({table.estimatedate}, {table.estimate})

Hard to say though, we need more facts to clearly help.

In general if you supply example data and expected output (not a description), it's clearer.

-k

 

[HNLboy]

Sorry for the confusion...

The data in previous post are two fields from one table; desired output is based on that is:

1242
1962
2279

I don't want 2146, because it is a "Master" to other estimates; I want only the latest estimate for that "family" (estimate 2279). Since the data is all in one table, I can't (or don't know how to) filter with joins. Also, I can't select using estimate date as often the Master and the versions are created on the same date (no time field available).

Thanks for your help...HNLboy

 

[lbass]

I think you could add the table a second time as an alias (Table_1). Link {Table.EstNo} to {Table_1.Master} with a left join. It looks like your estimate numbers are sequential, with higher numbers being more recent, so I think you could use a record selection formula of:

isnull({Table_1.Master})

This should eliminate all estimate numbers also represented in the Master field. Then group on {Table.Master} and use a group selection formula like

isnull({Table.master}) or
{Table.estno} = maximum({table.estno},{table.Master})

-LB

 

[HNLboy]

Thanks K & LB!

It works! Appreciate your help very much!

Thanks, HNLboy

PS: One question about why it works...I noticed that if I use this in the record selection formula:

{Table_1.Master} = "" (since it is a string field)

nothing gets returned, but if I do as you say

isNull({Table_1.Master})

it works. Why is that? (When you look at the field in Enterprise Manager/table properties, 'Null' is not checked for that field...)

 

[lbass]

= an empty field, which is different from a null field which essentially is not there. In this case, the absence of a corresponding field in Table_1 when linked to the Table that contains the field represents a null.

-LB