Problem with shared variable formula

[Jonesxx]

Hi,

I am writing a report using Crystal version 10, I also have access to Crystal version 8.5.
The main report shows records based on the record accountant managers name.
There is also a sub report which exists to show which records from the main report have a second address.

The problem I am having is as follows:

Example, the accountant manager named Joe Bloggs has 10 records under his name, only 8 of these records have a second address as per the sub report. However when running the main and sub report together the report does not suppress the other two records i.e. only shows eight records it still shows 10 records. I assume the reason the report is still showing 10 records as opposed to just eight is because it is focusing on the main report selection criteria which is based on the accountant managers name.

I know this has something to do with shared variables which I have very little knowledge of, I have had a look on the internet and have attempted to write two formulas to get the main report to also consider the criteria of the sub report. These formulas are below, they do not work, please help if you can. Thanks


//@subformula
//Stores all contacts that have address in
//{CONTSUPP.CONTSUPREF}field
//in a string variable called 'AddressRecords'
whileprintingrecords;
shared stringvar AddressRecords := {CONTSUPP.CONTSUPREF}


//@mainformula
//Returns the records that are stored
//in the shared string variable called
//AddressRecords in teh subreport
Whileprintingrecords;
Shared Stringvar AddressRecords;

 

[rosemaryl]

Dear Jonesxx,

You didn't mention which database nor describe the tables and fields in which the data is stored.

What defines that there is a second address? Is it multiple records in an address table related to the account manager?

Regardless, I would use a sql expression to get a count of addresses. You could then use this as additional selection criteria to say {%addresscount} > 1

I am not even sure you need a subreport ... but I don't have enough information.

Hope that helps,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[Jonesxx]

Rosemaryl,

The database I am using is Goldmine Corporate Edition version 6. The records have been set up in Goldmine as follows:

There is one main record which is where primary contact info is entered. Within every main record there are separate tabbed screens at the bottom, if a contact has a second address this is stored in one of the tabbed screens.

I am not trying to get a count of the addresses, when refreshed the report should produce entries as illustrated:

Joe Bloggs 111 Test Street Test lane (address info from main report)
Joe Bloggs 123 Testing Street, Test County (address info from sub report)

So basically the aim is where a contact has two address both these address are to appear on the report. Every record held on Goldmine has a main address which the main Crystal report focuses on, but not all contacts have a secondary address therefore I only want to view those contacts on the report who have both a main and a secondary address. Therefore the report should always show contacts who have two addresses and never a contact that only has just a main address.

The selection criteria I have used in the sub report to define that there is a secondary address is: {CONTSUPP.CONTSUPREF} like ["*address", "*address*", "address*"]. When refreshed on its own this pulls back all contact info with a secondary address.

The sub report is needed as the accountant manager name is only related to the contact information in the main report meaning that the info in the tabbed screens (which is where the secondary address info is stored) is not related to the accountant manager.

So basically the main report serves the purpose of producing contact info by accountant manager name and the sub report serves the purpose of producing contact info with secondary addresses that do not have any relation to the accountant manager.

Its quite difficult for me to explain but I hope I have answered the bulk of your questions.

 

[rosemaryl]

Dear Jonesxx,

I am afraid that I do not understand ... tabbed forms in an application usually indicate a a child table .. however I don't use Goldmine so I have no idea of the database structure.

I need to know the tables and fields you are using in both reports and what field or information indicates that an address is related to the account manager, but you say there is no relationshiip. So what indicates that the secondary address is related to the record in the main report?

I am not particularly interested in what you are doing now, but more interested in what the data looks like and what you want to see on your report. I still don't think a need exists for the subreport with the example data you have shown.

Perhaps someone who does reporting on Goldmine will see the post and be able to more adequately help you.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762