Details Looping ??? 1

[McNab]

Hello All,

I have a rpt that I am updating. It has several Report Headers and then one group (Group Header #1 :InvestHolding.AcctNum - A) around the 2 detail lines. is what the group is on. I simply added a table or two and one field (not formula) in the group header. It displays a name. Now the report gets the correct name, but the data for the client is comming out four times in the detail line? The data is not incorrect or different, but it is the same thing four times? Any ideas?

Thx

Jamie in Canada

 

[kenhamady]

You just added a table or two?
I will assume that you linked them correctly.
Apparently one of these tables has a one-to-many relationship and is causing the duplication of the results.

Ken Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[McNab]

Hey Ken!

Congrates on the tipmaster of the week! I know I voted for you more than once.

Yeah...well... I added 3 tables. They are really only used to get a persons name out of the db. I added the policy table to link the NameID from the CorpClient table to the NameID in the policy table. Then I added/linked the policy.InsuredID to PolInsured.NameID (another table which I added) and linked that NameID to the NameID an alias table I made (NameEntity.NameID). Then, on my rpt, I simply added a field from the alias table (NameEntity.Name). I linked from the policy table because the name I needed to pull was attached to an ID number in one of the fields. After that, all tables are linked with NameID. The Insured Persons NameID is stored in the NameEntity table. But then it loops on me.

I just kinda fixed it by checking off the "suppress if duplicated" check box, but I don't like this solutions as it leaves room for error later with different peoples data.

You are correct about the one to many relationships. CorpClient.NameID = one-to-many, Policy has one-to-many, but the fields i'm linking do not. PolInsured.NameID = one-to-many. How do you get around this problem of the detail looping? And any idea why it is coming out four times?

Thx again Ken

Jamie

 

[kenhamady]

The trick is to add the tables one at a time, adding one field from the new table. This will tell you which table is causing it and why. On-to-many isn't a matter of which fields you use. It is a matter of the how many records match from the added table.

Once you know why it is inflating, we can talk about fixing it.

If you are using v8 you can try the Database-Select Distinct as a quick fix, but this doesn't really solve the underlying problem. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[McNab]

Hello Ken,

So, what your saying is that I should add one table at a time, and then put a field on the screen for each one just to "test" what is happening. I had to add 3 tables to link "ID's" to pull a value from the third table. You want me to add a table, and put the field on the report, then run it. Sounds good to me Ken ... I'll give it a shot!

Thx again!

Jamie in Canada

 

[McNab]

Hey Ken,

I took your advice and disconnected the links to the last two tables (PolInsured table and NameEntityInsured table), leaving the other new table I added (Policy table) linked to the CorpClient table (which was an existing table). I used the field (Policy.InsuredID) that I linked to the next table (PolInsured table) and placed it on the report. When I run the report, it loops out the clients data four times. When I erase the field in Print Prev mode, the report runs again and come out without looping. That is that it gets the proper client data once (like it's suppost to). I take it that this is what you needed to know to help me fix the problem. I'm using CR ver. 6.0, so there is no Database-Select Distinct. Please let me know what you think.

Thx again,

Jamie in Canada

 

[kenhamady]

That means that each record is finding 4 matching records in this added table. Is it always exactly 4? Put the other fields from this added table onto the report and see if there is anything that distinguishes them from one another. Like ideally a Sequence number that would be 1,2,3,4 or something like that. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[McNab]

Hey Ken,

I think I have an idea why it's looping. What this report does is give a listing of all of a clients "In Trust" policies. Each of these policies come out in the report with there money values and dates etc... What I want to do is add the name of the person that the client has insured for each of the "In Trust" policies. I can add the insured name (through linking the policy.InsuredID to NameEntityInsured.NameID placing the NameEntityInsured.Name on the report. Now, having said that, the number of "In Trust" policies that a client has, is the number of times that it "loops" and prints out. So, the number of repetitions that come out match the number of "in trust" policies that the client has. Now ... all of the correct info comes in for each policy (execpt it loops) but the name that I inserted does not change with each policy. It should give me a different name that is associated with that specific policy number (through the Policy.InsuredID). The first name that comes out is correct as it is the person that is insured under that "in trust" policy. "But the next policy to come out, has the correct data assosiated with that policy, but the insured name does not change from the first policy.

Just thought I would try to give you a better idea of what's going on with my report. I hope it helps!

You asked me to put the other fields from the added table onto the report and see if there is anything that distinguishes them from one another. They are all the same so there is no distinguising fields. It just seems to loop out the data the same number of times that a client has people insured with "in trust" accts. Could it have something to do with the way I set up my links? By that I mean the 3 option buttons "When linking more that one file". I hope I haven't confused you or scared you off. Let me know what you think...

Thx again...

Jamie

 

[kenhamady]

Include only 2 tables, the policy and the Name Entity, and put all fields on the detail band with no groups.

Do you get the correct Insured name for each policy? If not, your link is incorrect.

Do you get more than one record per policy? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[McNab]

Hey Ken,

So you want me to delete the current group that is around the detail lines? The current group is Group Header #1 - InvestHolding.AcctNum - A.

I'll give it a shot!

Thx

jamie

 

[McNab]

Ok Ken,

I deleted the grouping on Group Header #1 - InvestHolding.AcctNum - A. Then I ran the rpt. It gave me all of the same data that it should (info for the five ppl which combined makes 20 policies), but it was not separated (by AcctNum), so it all came in together (all 20 consecutively). Then I added the policy and NameEntity tables and added the Policy.PolicyID, Policy.OwnerID, Policy.InsuredID (this is the ID that links to the name of the person I want with each policy) and I added the NameEntity.Name (which is the insured name). Now, for each of the 20 policies, it would list all five peoples names for each policy. The names always come out in the same order for each of the 20 policies. The correct data still comes in with the first name, but the other four names each get the same data as the first name. This is the loop. The next policy has the correct data for one person, but also lists the same data beside each name. Also, the order of the names does not change. The second name that comes out is the one that should come out with this Policy, so it should be first. The five names never change order. As for you last question “Do you get more that one record per policy?”. Yes … I guess I do. Like I said, it comes out five times (five names) with the same data beside each name.

Here is what it looks like now:

Investment (insured name) Total Cost Market value
===========================================================
Bond Fund Adams, Amile 11.22 1,122.23
Adams, Karl 11.22 1,122.23
Adams, Evan 11.22 1,122.23
Adams, Marina 11.22 1,122.23
Adams, Mary 11.22 1,122.23
Equity Fund Adams, Amile 22.33 2,254.92
Adams, Karl 22.33 2,254.92
Adams, Evan 22.33 2,254.92
Adams, Marina 22.33 2,254.92
Adams, Mary 22.33 2,254.92
*this would go on for the other 3 policies that Amile has after these 2. This is the 6th policy, and this set of policies are insured to Karl.
Bond Fund Adams, Amile 66.54 44,842.02
Adams, Karl 66.54 44,842.02
Adams, Evan 66.54 44,842.02
Adams, Marina 66.54 44,842.02
Adams, Mary 66.54 44,842.02
Equity Fund Adams, Amile 25.42 3,142.29
Adams, Karl 25.42 3,142.29
Adams, Evan 25.42 3,142.29
Adams, Marina 25.42 3,142.29
Adams, Mary 25.42 3,142.29
*the rest of the report looks like this and the total adding each time the values loop, so each value is added 5 times instead of 1.

Here is what I am trying to make it look like:

*the "0005046356" is the AcctNum

Investment Total Cost Market value
===========================================================
0005046356 Adams, Amile
Bond Fund 11.22 1,122.23
Equity Fund 22.33 2,254.92
Bal Fund 54.56 5,458.22
Int'L Growth Fund 66.36 9,874.32
Asset Alloc. Fund 5.55 123.45
=================
"the total"
0009941I05 Adams, Karl
Bond Fund 54.27 5,152.25
Equity Fund 76.53 7,257.94
Bal Fund 44.55 4,444.22
Int'L Growth Fund 12.33 8,865.36
Asset Alloc. Fund 55.55 121.41
US Equity Index Fund 35.25 777.77
=================
"the total"
...and so on for each of the five people, with a grand total line at the bottom.

I hope this helps you

advTHANKSance

Jamie in Canada

 

[kenhamady]

I am starting to think that this is more complex than what I can troubleshoot via TT. I would have to look at the report itself, which is a service reserved for my customers.

But I will take one last shot. It looks like you may have linked somwhere based on the person's last name. That isn't good, since several people have the same last name.

Good Luck. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[RickyW]

Jamie,

Try moveing everything out of the Detail section of the report and put all in the group section. Suppress the print on the detail section.

 

[McNab]

Thx for all the help none the less...

I had an idea to insert another group inside the existing group.

Group Header #1 - InvestHolding.AcctNum - A
Group Header #1B - Policy.InsuredID - A

would this work, or should the next group be G. H #2 (not #1B)?

I'll give it a try, and Ricky ... I'll try your suggestion too.

Thx guys!

Jamie in Canada

 

[kenhamady]

Since you have records with bad data combinations (names that don't match numbers) I can't imagine that grouping the records will have any effect on the problem. I think you have a bad link. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[McNab]

Hey Ken,

Thx for the quick reply. I think that you are correct about the bad link. For this report, I am dealing with Investments (not policies). I may have said policy earlier in the post for the sake of convience, but I really meant Investments. As for the bad link, the only table in the database that has 1. Acct Num, 2. OwnerID and 3. *InsuredID*, is in the policy table. Like I said, this has nothing to do with policies, so this table should not be used. Is that what you mean by a "bad link"? Do you think that it is possible for me to fix this problem?

Thx for all the help!

Jamie

 

[kenhamady]

It is possible that your data is stored in a way that makes the report you want impossible. I couldn't tell you without reviewing your available tables and their relationships.

I can only tell you that your current table arrangement isn't getting you data that is useful for your report. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[McNab]

Thanks Ken,

This report that I am working on is an existing report. The person that made it really knew what they were doing (of course, they are long gone now). It makes no sense that they left the insured persons name out of the report in the first place. I have a feeling that they tried this and couldn't get it to work either. I guess this was my turn. Oh well ...

Thx again for all of the help Ken!

Jamie in Canada

 

[willto]

I am using symposium to create reports off my PBX,I have taken some Crystal Reporting courses so I can manipulate exported reports from Symposium and create new ones. Is this the way to go? Or should I focus on trying to created new reports in Crytstal and import them into Symposium? Most of the canned reports in Symposium have too much info for the front office folks.

 

[McNab]

Uhhh ... hey willto,

I think you posted this in the wrong place. You want to "start a new thread" if you have an unrelated question. You'll get more responses this way ...

Good Luck

Jamie