list multiple partners for one affair 2

[toby77jo]

I have a problem, as you can see below I get a list of the same Affair from our business database, the reason is that one affair can have many partners, so it list the same affair over and over depending on how many partners that were involved in the affair. Is there a way to list the affair only once but to also list all partners belonging to it? Please send me a dummy explanation how to resolve this since I am a rather new user to crystal reports. I am using version XI,


Public Sector
Client 316 A342
Senegal: Identification of End date
the Netherlands Budget 09/30/2004
Support Partner AGRITECH Malysia 2002

For several years the Government of the Netherlands has
provided budget support to Senegal. Up to 2004 this aid was
provided for the financing through a special account at the
Central Bank of specific projects implemented by the
Senegalese Ministry of Env



Client 316 A342
Senegal: Identification of End date
the Netherlands Budget 09/30/2004
Support Partner BA SAMBA-DIOM Cabinet

For several years the Government of the Netherlands has
provided budget support to Senegal. Up to 2004 this aid was
provided for the financing through a special account at the
Central Bank of specific projects implemented by the
Senegalese Ministry of Env

 

[Madawc]

Group by 'Affair', show its details in the group header or footer, list the partners in the details.

I'd also recomend using some other name, 'project' or 'item'. 'Affair' is commonly used for illicit sexual liasions, which is evidently not what your report is listing.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[toby77jo]

I tried it but it did not work.

1 I created a group based on affair
2 I left the Partnerfield as before in details

Did I miss something? Do you need to put something else in the group header or footer?

Here is how the Partners are fetched from the db.

T_Affair(A_ID) to T_Relation_Affair_Partner (A_ID)
T_Relation_Affair_Partner (NumSoc) to T_Partner (NumSoc)

T_Affair
A_ID(Primary Key)
...
...
...
...

T_Relation_Affair_Partner
A_ID(dublicate allowed, for one affair there can be many NumSoc)
NumSoc

T_Partner (Lookup table to get Partner name)
NumSoc
PartnerName
...

Many thanks in advance for any help.

 

[Madawc]

Please clarify "did not work". Did you fail to get any record? Get too many? Or what?

It's also worth posting your SQL - Database > Show SQL Query

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[toby77jo]

Clarification to "did not work", it still list the records in the same manner, as posted in the example where you can see one affair being listed as many times as there are partners related to the specified affair.

A quick reflection: would it not be possible to sort this problem out by creating a group record by affair and put the partner in the details field? But I tried without any luck so far.

Here is the SQL:

SELECT `T_Assigments`.`End Date`, `T_Assigments`.`FundingRaiser`, `T_Found_Raiser`.`Short description EN`, `T_Tenders`.`Number_Affaire`, `T_Partners`.`Society`, `T_Assigments`.`Descr courte-va`, `T_Assigments`.`Titre-va`
FROM (((`T_Assigments` `T_Assigments` INNER JOIN `T_Tenders` `T_Tenders` ON `T_Assigments`.`O_ID`=`T_Tenders`.`O_ID`) INNER JOIN `T_Found Raiser` `T_Found_Raiser` ON (`T_Assigments`.`C_Id`=`T_Found_Raiser`.`C_Id`) AND (`T_Tenders`.`C_Id`=`T_Found_Raiser`.`C_Id`)) INNER JOIN `Relations Affaires - Offres - Partners` `Relations_Affaires___Offres___Partners` ON `T_Assigments`.`O_ID`=`Relations_Affaires___Offres___Partners`.`O_ID`) INNER JOIN `T_Partners` `T_Partners` ON `Relations_Affaires___Offres___Partners`.`NumSoc`=`T_Partners`.`NumSoc`
WHERE `T_Tenders`.`Number_Affaire` LIKE 'A*'


SELECT `T_Expertise`.`Description`
FROM `T_Expertise` `T_Expertise`
ORDER BY `T_Expertise`.`Description`

 

[Madawc]

I see nothing obvious. Have you used the actual Group command - Insert > Group?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

If you group by affair, and place all the fields you want to display in the group header except for the partner field, you can then place the partner field in the detail section. Then go to report->sort records and sort by the partner field. Finally, select the partner field ->format field->common tab->suppress if duplicated.

-LB

 

[IdoMillet]

1. Place all the information that belongs to the affair in the Group Header.

2. Place only the partner info in the detail section. If you'd like to list the partners across (rather than down the page), select in the format attributes of the detail section the option to format the section with multiple columns. A new "Layout" tab then becomes visible... This allows you to format the detail section into multiple columns...

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[toby77jo]

I managed to get it to list correctly by putting all the fields in the group header and only the partner field in details. Now I have another problem, it is only listing those affairs that have a partner, but in the db most affairs did not have a partner, How to I manage to list all affairs even if they do not have a partner?

 

[lbass]

Use a left join FROM the table containing the affair field TO the table containing the partner field.

-LB

 

[toby77jo]

thanks, only problem for me is that in Crysal reports 11 I can only see the SQL query, I can not modify it.

Any Idea how to edit the sql query?

 

[toby77jo]

My query looks like this now:

SELECT `T_Assigments`.`End Date`, `T_Assigments`.`FundingRaiser`, `T_Found_Raiser`.`Short description EN`, `T_Tenders`.`Number_Affaire`, `T_Expertise`.`Description`, `T_Partners`.`Society`, `T_Assigments`.`Descr courte-va`, `T_Assigments`.`Titre-va`
FROM (((((`T_Assigments` `T_Assigments` INNER JOIN `Relations Affaires - Offres - Partners` `Relations_Affaires___Offres___Partners` ON `T_Assigments`.`O_ID`=`Relations_Affaires___Offres___Partners`.`O_ID`) INNER JOIN `Relations Affaires - Expertises` `Relations_Affaires___Expertises` ON `T_Assigments`.`A_ID`=`Relations_Affaires___Expertises`.`A_ID`) INNER JOIN `T_Found Raiser` `T_Found_Raiser` ON `T_Assigments`.`C_Id`=`T_Found_Raiser`.`C_Id`) INNER JOIN `T_Tenders` `T_Tenders` ON `T_Assigments`.`O_ID`=`T_Tenders`.`O_ID`) INNER JOIN `T_Partners` `T_Partners` ON `Relations_Affaires___Offres___Partners`.`NumSoc`=`T_Partners`.`NumSoc`) INNER JOIN `T_Expertise` `T_Expertise` ON `Relations_Affaires___Expertises`.`E_ID`=`T_Expertise`.`E_ID`
WHERE `T_Tenders`.`Number_Affaire` LIKE 'A*'

Should I change it to:

SELECT `T_Assigments`.`End Date`, `T_Assigments`.`FundingRaiser`, `T_Found_Raiser`.`Short description EN`, `T_Tenders`.`Number_Affaire`, `T_Expertise`.`Description`, `T_Partners`.`Society`, `T_Assigments`.`Descr courte-va`, `T_Assigments`.`Titre-va`
FROM (((((`T_Assigments` `T_Assigments` LEFT JOIN `Relations Affaires - Offres - Partners` `Relations_Affaires___Offres___Partners` ON `T_Assigments`.`O_ID`=`Relations_Affaires___Offres___Partners`.`O_ID`) INNER JOIN `Relations Affaires - Expertises` `Relations_Affaires___Expertises` ON `T_Assigments`.`A_ID`=`Relations_Affaires___Expertises`.`A_ID`) INNER JOIN `T_Found Raiser` `T_Found_Raiser` ON `T_Assigments`.`C_Id`=`T_Found_Raiser`.`C_Id`) INNER JOIN `T_Tenders` `T_Tenders` ON `T_Assigments`.`O_ID`=`T_Tenders`.`O_ID`) INNER JOIN `T_Partners` `T_Partners` ON `Relations_Affaires___Offres___Partners`.`NumSoc`=`T_Partners`.`NumSoc`) INNER JOIN `T_Expertise` `T_Expertise` ON `Relations_Affaires___Expertises`.`E_ID`=`T_Expertise`.`E_ID`
WHERE `T_Tenders`.`Number_Affaire` LIKE 'A*'

Just need to know how to modify it in crystal reports 11, this query is genereated automatically through the database expert > links interface.

 

[toby77jo]

lbass : do you have an email, I can send you an jpg of the screen with the links in crystal reports. Like that you get a better overview. I have understood your question but I get error codes in the db connection when I click on the link and choose LEFT Outer Join. or RIGHT join as well.

 

[toby77jo]

My modifed sql query now :

I am doing a left outer join but it still only lists affairs with partners. I guess I am putting the left join in the wroing place.

The relationship is like this:

T_Assignments
AffairID

to

T_Relation_Affair_Partner
AffairID
PartnerID

to

T_Partner
PartnerID
NamePartner



SELECT `T_Assigments`.`End Date`, `T_Assigments`.`FundingRaiser`, `T_Found_Raiser`.`Short description EN`, `T_Tenders`.`Number_Affaire`, `T_Expertise`.`Description`, `T_Partners`.`Society`, `T_Assigments`.`Descr courte-va`, `T_Assigments`.`Titre-va`
FROM (((((`T_Assigments` `T_Assigments` INNER JOIN `Relations Affaires - Offres - Partners` `Relations_Affaires___Offres___Partners` ON `T_Assigments`.`O_ID`=`Relations_Affaires___Offres___Partners`.`O_ID`) INNER JOIN `Relations Affaires - Expertises` `Relations_Affaires___Expertises` ON `T_Assigments`.`A_ID`=`Relations_Affaires___Expertises`.`A_ID`) INNER JOIN `T_Found Raiser` `T_Found_Raiser` ON `T_Assigments`.`C_Id`=`T_Found_Raiser`.`C_Id`) INNER JOIN `T_Tenders` `T_Tenders` ON `T_Assigments`.`O_ID`=`T_Tenders`.`O_ID`) LEFT OUTER JOIN `T_Partners` `T_Partners` ON `Relations_Affaires___Offres___Partners`.`NumSoc`=`T_Partners`.`NumSoc`) INNER JOIN `T_Expertise` `T_Expertise` ON `Relations_Affaires___Expertises`.`E_ID`=`T_Expertise`.`E_ID`
WHERE `T_Tenders`.`Number_Affaire` LIKE 'A*'

 

[toby77jo]

Hmmm, now it is working indeed with a (enforced both) LEFT OUTER join It works. I only have one last problem to finalize the report.

1: I am grouping by which Expertise an affair belongs to. In the relation between affair and expertise there can be many expertises, but I only want to take the first instance into account.

The expertise list looks like this:

Puplic Sector
Poverty
Markets
Private Sector etc.....

Now if AffairID 1 has both Public Sector and Poverty as expertise it will be listed 2 in the report under the different group headings. I only want to take the first into account. In principle one affair can only be listed once not more.

Any idea how to resolve this.

2: How can i suppress a text object if the details field for it is empty.

 

[lbass]

So expertise is Group #1 and affair is Group #2? What makes an expertise "first"? Do you have a corresponding datefield?

On the text object, do you mean you have a database field in a text box? If so, you can conditionally suppress the borders of the text box, so for each line go to the conditional formula area and enter:

isnull({table.field}) or
trim({table.field}) = "" //assuming a string field

-LB

 

[toby77jo]

Problem 1:

Expertise is group one because it describes which sector a project belongs to, we have 11 different sectors, group 2 is affair(project) and in that group I put all the other detail fields, and in the real detail field I put partner,because one project can have many partners belonging to it, but once again I wanted only the affair to be listed once not as many times as there where a society(partner) belonging to it. But now my problem is that one project can list under many different sectors, I only want it to take the first expertise(sector) into account. Instead of repeting the affair again further down in the rerport under another sector group header.

Is that possible to do?

Problem2:

In group header 2b I have a text box called Partners:
Right underneath the text field in the details filed i have the T_Partners_Society coming from the db. So it is not really the text field Partners: that I want to suppress but the empty lines that sometimes appear in the list of T_Partners_Society

thanks you so much if you can help me out.

 

[lbass]

You didn't really answer my question. I wasn't asking about your group structure, but instead what you meant by first instance. If you want an affair listed only once, you have to be able to define what constitutes the "first instance of expertise"--is it the first that occurred in time, i.e., is there a date field that would define this? Or is there some other criterion that you would apply to determine the expertise where you want the affair to appear?

I'm still a little unclear on the empty lines issue--do you mean you have empty detail sections? Go to the section expert->details->suppress blank section.

-LB

 

[toby77jo]

It is the first time it was encoded, not my date, this is an export from the db

A_ID E_ID
568 7
283 11
277 3
280 3
280 8
280 11
287 3
269 2
269 3
276 10
273 9
273 4

A_ID 280 for example, I only want it to take E_ID 3 into account and skip E_ID 8 and 11

E_ID is pointing to a lookup table to get the correct sector name based on its E_ID

 

[lbass]

What would it be for A_ID 273? Does it even matter which E_ID is shown so long as there is only one?

-LB