Crystal Reports Reference data in a related table

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Reference data in a related table

Post Reply

Post New Topic

Author	Message
jgarner Senior Member Joined: 23 Jan 2009 Location: United States Online Status: Offline Posts: 159	Topic: Reference data in a related table Posted: 07 Sep 2010 at 8:42am
	I have a purchase order report (PO) setup that has several related tables. The PO table has a field named PO.employee_num. A related table has Labor.employee_num and Labor.employee_name. On the purchase order report, how do I convert the PO.employee_num to Labor.employee_name?
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 07 Sep 2010 at 9:38am
	likely .... inner join the tables on the number field po.employee_num=labor.employee_num drag and drop the labor.employee_name to whereever you want it to display instead of the number Edited by DBlank - 07 Sep 2010 at 9:38am
	IP Logged

jgarner Senior Member Joined: 23 Jan 2009 Location: United States Online Status: Offline Posts: 159	Posted: 08 Sep 2010 at 3:39am
	Thanks, that worked. Now if I needed to place an additional employee on the report as well, what changes need to be made to the formula.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Sep 2010 at 3:41am
	please explain further...another employee somehow related to the same PO, another employee for a different PO, something else?
	IP Logged

jgarner Senior Member Joined: 23 Jan 2009 Location: United States Online Status: Offline Posts: 159	Posted: 08 Sep 2010 at 3:45am
	Here are additional fields on the same PO: PO.RequestedBy and PO.ModifiedBy Both are employee numbers which relate to Labor.employee_num and Labor.employee_name.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 08 Sep 2010 at 4:43am
	Add the labor table to the report 2 more times. YOu will get a warning saying it has already been added. Just hit OK to get passed that. The new Labor tables will have an alias name of Labor_1 and labor_2. join labor_1.employee_num = Po.requestedby join labor_2.employee_num =PO.modifiedby I am going to guess that requestedby is required and modified is not required. If any field is not required you are going to have to make the join an outer join, otherwise it will omit all the records where there is no match (the nulls in the PO.modifiedby). Drag and drop labor_1.employee_name to show the requestd by name and labor_2.employee_name to show the modifier name
	IP Logged

jgarner Senior Member Joined: 23 Jan 2009 Location: United States Online Status: Offline Posts: 159	Posted: 08 Sep 2010 at 5:04am
	Very clever. I had no idea I could use multiple instances of the same table. Once you explained this, it makes sense. Worked exactly as you suggested. Thanks.
	IP Logged

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum