Different select for different fields 1

[BasilFawlty001]

I have two tables. Table 1 contains a field called AccountNumber. This field is unique. It holds typical contact info (name, address etc). Table 2 also has an AccountNumber field, though not unique (linked to table 1). For each contact in Table 1 there may be several instances of the same account number in Table 2. I need to select Field A in Table 2 based on Field C being "X". I also need to select Field B in Table 2 based on Field C being "Y".

At the end the report should look like this:

AccountNumber Name Address Phone Field A Field B

 

[HowardHammerman]

No problem. Simply create the following formula in your record selection expert:

{table2.fieldC} ="X" or {table2.fieldC} = "Y"

Then place the fields you want to see (Field A and Field B)
in the report. If you include fields from table 1 and do not want to show the same information N times (based on the number of records in table 2) then format the fields from table 1 and select 'suppress duplicates'.
Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Ngolem]

I am assuming that FieldC is in Table 2...you don't say this

In the report I would group on

Group 1 - Account number


then I would put the formula Howard suggested, in the record selection formula

then I would create an initalization formula

@Initialization (suppressed in Group 1 header)

WhilePrintingRecords;
StringVar array result := ["",""];


then I would place a collect formula in the details

@Collect (suppressed in Detail Section)
WhilePrintingRecords;
StringVar array result;

if {Table2.Field C} = "X" then
result[1] := {Table2.Field A}
else if {Table2.Field C} = "Y" then
result[2] := {Table2.Field B}


then in the footer of Group 1 I would put this formula

@DisplayResult (placed in Group 1 footer)
WhilePrintingRecords;
StringVar array result;
result[1] + " " result[2];

This would be placed along side the other information for field A & B

this should work for you Jim Broadbent

 

[BasilFawlty001]

Thanks Guys!
I'll let you know how it goes.