Duplicate caused by formula

[poconoski]

Hello all,

I am creating a report which prints one customer based on customer ID from Access. It works well until I add a formula field. When the formula field in on the report I get many duplicates.

The layout is;

Cust ID
Name
Addr
PPR Code ## Desc

For each client code a desription is pulled from another table. There are no natural relations ships between the 2 tables. I created a link between the codes from table A to Table B.

The Desc formula I am using is:
if not (isnull({Master.PPR})) then
if {CodeLookupsActive.TableName}="MASTER" and
{CodeLookupsActive.FieldName}="PPR" and
{CodeLookupsActive.code2}={Master.PPR} then
{CodeLookupsActive.Description}

Once the formula field is added to the report I get multiple duplicate records. There is only one record in Table A.

Thanks

 

[poconoski]

After further dickering around I think this may be solved. The FirstName, MiddleName and LastName are seperate fields. So I put them into a text box to help control the output. When I remove the text box the report appears to work ok with the formula field. When I add the text box back in it starts duplicating again.
Any ideas on how to include the text box and avoid duplicates?

 

[kenhamady]

When you add your textbox, and the duplicates appear, does the record count that displays in the lower right of the screen change to a higher number?

If, as a test, you place one database field from each table in the page header of your reporr, do you get the same duplication?
When you say there is no natural relationship between the two tables, how will CR know which record in TableB to line up with each record in table A? Bottom line, you are probably doing an invalid link between the tables. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[poconoski]

Ken,

I created an index on each field and then created a link in CR. PPR => Code2.

The formula is;
if not (isnull({Master.PPR})) then
if {CodeLookupsActive.TableName}="MASTER" and
{CodeLookupsActive.FieldName}="PPR" and
{CodeLookupsActive.Code2}={Master.PPR} then
{CodeLookupsActive.Description}

The report pulls the correct data with SelectExpert Equal to client ID #.

The number of records is 1. When I place the formula on the report the record count goes to 10. The data is in access so I am not using ODBC.

Is there any thing else I can supply to clarify the problem?

Thanks

 

[kenhamady]

If, as a test, you place one database field from each table in the page header of your reporr, do you get the same 10 records of duplication?

Does the PPR field have the same values as the CODE2 field? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[poconoski]

Yes. As a test when I place the field from the second table on the header the record count jump up to 10.
PPR and CODE2 each contain the same values.

 

[kenhamady]

That means that for the 1 record in the first table you have 10 matches in the second table. The reason that you see the change is because CR ignores a table until it is used by the report. Apparently what you are doing is the first use of any fields in this report, so that table gets activated.

If the tables have the same values, why is your link a >= instead of a straigt equal? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[poconoski]

After I select the tables the link expert does not allow me to designate the type of link. Under options the radio buttons are grayed out.
Is this because I am going straight into Access instead of using an ODBC connection?

 

[kenhamady]

Yes.
So where are you doing the >= link? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[poconoski]

The link expert does allow me to set a link. PPR to Code2.
It does not let me change it.
And thank you for your patience.
I am still a newbie with crystal

 

[kenhamady]

But, where do you see the >= link - in the greyed out radio buttons?

As another test, make sure your detail band is visible and put both of your linking fields on it. See if they are the same for each record.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[poconoski]

I don't actually see a =>.
The grayed out radio button is equal (=).
The link show an equal join.
When a suppress all duplicates it looks ok. Of course when I click on a field I see all the suppressed duplicates.

When I did the experiment mentioned above I get 10 records. The first one is correct. The PPR field has a value of 11. The formula pick out the matching description and prints "Leader". The subsequent 9 lines show the PPR as 11 and the desc is blank.

My selection expert is set to Client ID 12345.
Each line on the report is for client 12345.

I also have 8 more fields for this report which must do the same thing. Take a value from the Master table, match it up to table B and insert a description line. The next formula does not work. Is this because the only link I have is set to PPR? Can I set multiple links? Is this the only way or the only best way to do something of this nature?

 

[poconoski]

When I create the report using ODBC all link options are available.
Table B is used for many other tables too. Code 11 which matches PPR exists in 10 records. I am triing to match the fields tablename, fieldname and code2 in order to select the correct description.
Using ODBC the formula above I get 10 lines and only one dispays a desription, which is correct.
I am not even sure if the above formula is the way to go.
Thanks again Ken

 

[kenhamady]

Because there are 10 matches you are getting the 10 records. it doesn't know which of the 10 you want. To only get the record with a desctiption, add a rule in the select expert that says :

{Description}

is greater than

(leave blank) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[poconoski]

Thanks Ken. That method works well
I put in TableName="Master" FieldName="PPR"
Thanks again.