Hello everybody! In my report, a 3

[mondi]

Hello everybody!

In my report, as Data Source, is a data set. In my computer I have installed Crystal Reports for .NET 2002. The data base is created using SQL 2000.

Now the problem is like this:
I have different tables in my data set, but the two tables that I need, are <SENDERS> and <CUSTOMERS>. If the data fields <SENDERS.SENDER_ID> and <CUSTOMERS.CUSTOMER_ID> have the same value, then I have to display a text in the report. Else if they have different values then I have to display in my report different data fields from the table <SENDERS> like the <NAME1>, <NAME2>, <STREET1>, <STREET2>, and perhaps some other field. These must be displayed in the place specified by me. I mean the two fields might be displayed in one row, the other in second row and so on. I must also specify the distance between two rows so that the two rows must be near to each other.
If somebody helps me in this, would put off me a big problem. Thanks

 

[synapsevampire]

Rather than text descriptions, try posting version info, example data with table layouts, and expected output.

How do these tables join?

It might be fairly simple, but your post has very little technical info to go on.

-k

 

[mondi]

All Right.
My report has to fill a form used in the Customs.
The idea is like this:
If the customer is also the sender (i.e. <SENDERS.SENDER_ID> = <CUSTOMERS.CUSTOMER_ID>) then I have to display a text like &quot;The same&quot;. Else,if the customer is not the sender also, then I have to display in my report some data fields from the table <SENDERS> like <SENDERS.NAME1>, <SENDERS.NAME2>, <SENDERS.STREET1>, etc. They must be displayed like this:

{SENDERS.NAME1} {SENDERS.NAME2}
{SENDERS.STREET1} {SENDERS.STREET2}

I hope I was a bit clearer now.
Thanks for any replies.

 

[Madawc]

For Crystal 8.5, do a formula field
If <SENDERS.SENDER_ID> = <CUSTOMERS.CUSTOMER_ID>
then &quot;The same&quot;
else {SENDERS.NAME1} & &quot; &quot; & {SENDERS.NAME2} & CHR(13)
& {SENDERS.STREET1} & &quot; &quot; & {SENDERS.STREET2}


You use CHR(13)to force a line skip


Madawc Williams
East Anglia, Great Britain

 

[mondi]

Thanks a lot for your answer. I will try it and I think it will work. Can you tell me if it is possible for me to specify the distance between two rows. Because I would rather prefer not to have the rows in the standard distance between each other.
Thank you.

 

[Madawc]

You could have two line returns. Or else do two formula fields, which means you can space them as you please. Right-click and use [Object Size and Position] if you find it's snapping to the wrong positions.

Madawc Williams
East Anglia, Great Britain

 

[mondi]

Yes but isn't there any way how to change the standard distance between two rows? I wanted to specify it myself.
I guess it's difficult to find a way to do it. Perhaps impossible....

 

[synapsevampire]

You can specify the distance.

Rather than the solution offered, you can place the additional fields in seperate Details sections, and then use a formula akin to what you have only to specify the first line to display in the top detail section (right click the details section and select insert section below to add sections)

Now the additional detail sections can be sized up smaller, and conditionally suppressed using the same formula by right clicking the section->Format section->X 2 next Suppress.

-k

 

[mondi]

All right, but my report has many formulas and I want to display the section that I will create only in one part of my report.
So how can I do it? Can you give me a hand?

 

[mondi]

I mean, I want to specify where to display the fields.
Perhaps I didn't get your idea. I understood that I had to create the different Sections and I found that a great idea but I don't want to be the crystal report to specify where to display these sections.

 

[synapsevampire]

All right, but you can't just say I want to decide, you have to describe your requirements with rules, which you aren't doing.

Post some example data, and then post an example of what logic you use to decide how &quot;you're&quot; going to determine where things go.

I hope that you don't mean that after the report runs you want to look at each field and decide?

-k

 

[Ngolem]

let's start this over again...this thread is sort of muddled up with details when the core problem hasn't even been addressed.

You are fill out a shipping label right?

Something like:


Customer

Mr. John Doe
123 Anywhere St.
Pleasant Ville, NY
123456

Sender

Same As above

OR (if the ID is different)

Ms. Jane Public
456 Main St.
MyTown, TX
456789

That is how it should be RIGHT??? I think so...if it isn't... then SHOW US what it is!!!

Now you have these 2 tables...linked by <SENDERS.SENDER_ID> = <CUSTOMERS.CUSTOMER_ID>)

There are more tables involved here aren't there...There is probably a shipping table of some kind or at least another link between these tables....Why? because you have to have some way of identifying the sender and you haven't shown it here.

for example:

You might have fields <SENDERS.orderNumber> and <CUSTOMERS.OrderNumber> so that for a given order you know who is the customer and who is the sender...conceivably even though a customer's address is in the Sender's table they may in fact not be the sender for that particular order...something like this MUST EXIST

your PROBLEM is not difficult at all....but until you define it properly (read my tagline) you are wasting time.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mondi]

Actually my report should look like this:



(1) Mr. John Doe (2)
123 Anywhere St.
Unknown Town (3)
123456
U.S.A
(4)

(5) Mr John Smith (6)
Unknown Street
873462 (7)
Germany



In the (1), is the Customer Address.
In the (2), (3), (4), (6), (7) and so on, I have some other data fields that I have filled for my report.
In the number (5), consists my problem. If the {SENDER_ID} is equal to {CUSTOMER_ID} then instead of the address as written above, I have to write only &quot;The same&quot;. Else I have to write the address as I have written it above in the (5).
But I don't have to much space where to write it, so the lines must be nearer to each other than in the standard case. I mean the distance between rows must be specified by me. My problem is just in displaying in the right way the address of the Sender.
I don't know if I was clearer now. I hope so. I have a little experience in using crystal reports and perhaps my threads didn't define my problem properly. Sorry for that.

 

[Ngolem]

You still haven't explained How YOU MATCH SENDER_ID to CUSTOMER_ID

For example

Customer_ID SenderID
123 345
675
145

there is no match for Customer_ID but WHICH SENDER_ID DO YOU USE for the address??????

Reread my original post.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mondi]

There are more tables than these two in my data set.
The main table is <TRANSPORTS>. This table has different fields. Two of them are the <CUSTOMER_ADDRESS> and <SENDER_ADDRESS>. These two fields serve as keys for the tables <CUSTOMER_ADDRESS> and <SENDER_ADDRESS>. If the fields <CUSTOMER_ADDRESS> and <SENDER_ADDRESS> are not equal to each other than I have to display at (5) some data fields from the table <SENDER_ADDRESS>. Actually I thought this wouldn't been important, that's why I didn't post it.

 

[Ngolem]

understanding the whole report is important

You still haven't described the relationship yet...I see no reference to Customer_ID or Sender_ID in your last post

this is very confusing:

If the fields <CUSTOMER_ADDRESS> and <SENDER_ADDRESS> are not equal to each other than I have to display at (5) some data fields from the table <SENDER_ADDRESS>.

you refer to a table as <SENDER_ADDRESS> and also a field called <SENDER_ADDRESS>...I assume you mean <SENDER_ID> and <CUSTOMER_ID> for these fields

This is how I ASSUME the tables are linked and the locations of the data

Table Transport Table CustomerAddress
--------------- ---------------------
customer_ID ---------> Customer_ID
Sender_ID ----| Address
| City
| ZipCode
| Country
|
| Table SenderAddress
---------> Sender_ID
Address
City
ZipCode
Country

I'll assume that is correct

the solution is fairly easy once we have this picture

Since this is a shipping label the space available for printing is limited

You should divide your Detail section into 2 Subsections Each subsection will be exactly the size of its portion of the Shipping Label...you might include a third subsection between these two to act as a spacer.

The first Subsection will contain the Address field formula for the customer

//@CustomerAddress

WhilePrintingRecords;
StringVar result := &quot;&quot;;

if not isnull({Customername.name}) or
length(trim(Customername.name})) <> 0 then
result := result + {Customername.name}+Chr(13)+Chr(10);
if not isnull({Customername.Address}) or
length(trim(Customername.Address})) <> 0 then
result := result + {Customername.Address}+Chr(13)+Chr(10);
if not isnull({Customername.City}) or
length(trim(Customername.City})) <> 0 then
result := result + {Customername.City}+Chr(13)+Chr(10);
if not isnull({Customername.ZipCode}) or
length(trim(Customername.ZipCode})) <> 0 then
result := result + {Customername.ZipCode}+Chr(13)+Chr(10);
if not isnull({Customername.Country}) or
length(trim(Customername.Country})) <> 0 then
result := result + {Customername.Country}+Chr(13)+Chr(10);

result;

Place the above formula field at the top of the first detail subsection and enable the &quot;Can Grow&quot; option on the formula field

Now for the Sender Address formula for that part of the label

//@SenderAddress

WhilePrintingRecords;
StringVar result := &quot;&quot;;

if {Transport.Customer_ID} = {Transport.Sender_ID} then
result := &quot;Same As Above&quot;
else
(
if not isnull({Sendername.name}) or
length(trim(Sendername.name})) <> 0 then
result := result + {Sendername.name}+Chr(13)+Chr(10);
if not isnull({Sendername.Address}) or
length(trim(Sendername.Address})) <> 0 then
result := result + {Sendername.Address}+Chr(13)+Chr(10);
if not isnull({Sendername.City}) or
length(trim(Sendername.City})) <> 0 then
result := result + {Sendername.City}+Chr(13)+Chr(10);
if not isnull({Sendername.ZipCode}) or
length(trim(Sendername.ZipCode})) <> 0 then
result := result + {Sendername.ZipCode}+Chr(13)+Chr(10);
if not isnull({Sendername.Country}) or
length(trim(Sendername.Country})) <> 0 then
result := result + {Sendername.Country}+Chr(13)+Chr(10);
);

result;

this formula is placed in a similar manner into the subsection for the shipping label with &quot;Can Grow&quot; enabled
That should work...




Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mondi]

Unfortunately, I can't give you more than one star.
Thanks a lot for your answer. I am so sorry for being so stupid and not telling the whole problem. But I thought it wouldn't be really important to tell you the way how the tables were related to each other, and I thought that in the last post, I had explained it.
However your assumption was right.
Thanks once more.