One to Many Record Issue

[lhendrickson]

I am using Crystal 9.0 against Lawson tables.

I have 2 tables an employee table that has one line of detailed employee information, and one table with user field data for each employee. In the userfield table there are up to 10 lines per employee because all of the user field data is printed in one column and further identified by a field key.

example:

Employee Table:
5452 Ed Harris 548-15-8875 12/1/1974
1011 James Davis 999-99-9999 6/6/1984

User Field Table:
5452 84 CORP
5452 98 Dallas
5452 83 Active Full Time
1011 97 Temporary
1011 94 Field

I want to create a report that has one line for each employee and lists the 10 different user field's values in the columns beside the appropriate employee.

Example:
5452 Ed Harris 548-15-8875 12/1/1974 CORP Dallas AFT

I briefly tried a cross tab query, but was not successful. I am currently linking the tables by employee number with a left outer join from Employee to User Fields.

I know that this is a simple problem for most of you, but I cannot decide which Crystal feature to use and have been unsuccessful in searching tek-tips because I don't know what to search on.

Any advice from you experts will be appreciated!

lhendrickson

 

[synapsevampire]

It's not as simple as one would hope...

If the 10 lines (they're called rows) are a standard set of field numbers, then you could create 10 formulas to hold them.

Group by user and create formulas, as in:

Group header:
whileprintingrecords;
stringvar field84comptype:= "";

Dertails:
whileprintingrecords;
stringvar field84comptype;
If {user.fieldtype} = 84 then
field84comptype := {user.fielddescription}

Group footer:
whileprintingrecords;
stringvar field84comptype

Display your fields in the group footer only.

I just completed a Lawson/Crystal project and this is the sort of thing I created Views for because it's very reusable code as it will likely be required by multiple reports.

-k

 

[MJRBIM]

A similar question has been answered in the thread below...

You want to create a StringVar and display it in the footer of the group on EmpId.

Hope it helps...

thread767-711456