Problem with Using Multiple tables 2

[Kim296]

I have 3 tables that I am using for one consolidated report (NameMain, ArrestMain & TattoosMain)

When I query a set of dates, I want to see the name and descriptors (race, sex, age, etc..) and IF this person has been arrested and IF this person has Tattoos.

The problem is that everyone shows up on the NameMain table, but when I add the arrest features- it only shows people who have been arrested- AND when I add tattoos, it only shows people who have been arrested which have tattoos.

I've tried using a formula to show if tattoos or arrest are null then "NO Arrest" or "No Tattoos" v/s "Arrest" or "Tattoos", but my report is still only showing the people who have been arrested and have tattoos. Does anyone know why this might be happening and maybe have a solution to fix it.

Here is an example of the formulas for arrest (tattoos are the same):

(Formula #1) {@IFARR} - IF ISNULL({armain.book_id}) THEN TRUE ELSE FALSE
(Formula #2) {@ARREST} - IF ({@IFARR}) = TRUE THEN "NO ARREST UNDER THIS NAME" ELSE ("BOOK ID: "+(TOTEXT({armain.book_id}))+" , "+{armain.agency}+" , "+(TOTEXT({armain.date_arr}))+" , "+{armain.charge})

 

[CoSpringsGuy]

you need to set up a left outer join from namemain to arrest main and to tattoosmain.

In database expert, links tab, right click the line indicating the link and select remove links. Redraw the links making sure it comes from namemain to arrest and then to tattoosmain. Right click the line again and select left outer join.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Kim296]

That works perfect!! Thank you so much. VERY HELPFUL.

 

[Kim296]

I have one more question CoSpringsGuy. Maybe you will know this too. You're above solution worked perfectly, but how do I get it to show each record once. When I selected left outer join, it shows the information that I need, but everything is duplicated multiple times.

For example- If there is a person with 4 tattoos and one arrest:

Name and descriptors

Tattoo 1
Arrest 1

Tattoo 2
Arrest 1

Tattoo 3
Arrest 1

Tatto 4
Arrest 1


If I put each section in a group of it's own, it shows the following:

Tattoo 1
Arrest 1

Do you know how I can get it to show each tattoo (once) and each arrest (once)?

 

[CoSpringsGuy]

Ids it also possible to have multiple arrests?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Kim296]

Yes, either group can have multiple results.

 

[CoSpringsGuy]

This might do what you want or at least get your thoughts going in another direction.... Try this...
Group by name from namemain
group again by {armain.book_id}
create a formula called reset and place in Group2 header

stringvar output :="";
WhilePrintingRecords;
output

Create a formula called accum and place in details

stringvar output;
whileprintingrecords;
output := output + ", " + {TattoosMain.descriptionoftattoo};

create a formula called tattoo and place in Group 2 footer

stringvar output;
WhilePrintingRecords;
output := mid(output,3,len(output)-1);

Hide details section

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Kim296]

I think that I understand what you've done. I created all of the formula's and they show no errors. I placed them in each section as you said, but when I view the report- it pops up an error "String Length is less than 0 or not an integer".

I'm going to play around with it and try to make it work. I think I understand your method. Thank you for your time and help!!

 

[CoSpringsGuy]

change tattoo formula to

stringvar output;
WhilePrintingRecords;
if len(output) > 2 then output := mid(output,3,len(output)-1) else output := "";

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Kim296]

That works PERFECTLY!!!! THANK YOU SO MUCH FOR YOUR HELP TODAY!!

 

[Kim296]

CoSpringsGuy,

I really appreciated your help here and would like to learn more indepth. Can you please give me a good starting reference point to learn some of the codes like you have listed here? I would like to invest in some type of reference material, but I really don't know what would be the most beneficial.

Thanks,
Kim

 

[pmax9999]

There are lots of books around but there aren't any I have found that I was particularly impressed with. My suggestions for learning Crystal would be:
[ul]
[li]Take a look at some of Ken Hamady's publications, such as this one here. I purchased a couple of his in the early days and found them to be very helpful and good value;[/li]
[li]Read the question and answer posts on this site regularly;[/li]
[li]Use the CR Help files to learn about areas you do not fully understand; and[/li]
[li]Subscribe to Ken Hamady's blog. He provides very helpful hints.[/li]
[/ul]

As you will have discovered, there are some very experienced Crystal Reporting professionals on this site who are prepared to invest a considerable amount of time and effort to assist. Just make sure you provide as much information as possible when posting and invariably someone will be willing to help.

Others on this forum will be able to provide their recommendations.

Cheers
Pete

 

[Kim296]

Thank you Pete! I will check out what you suggested.