Link Multiple Field to Same Table with Summary

[Former Member]

I have a table JobOrder which has the fields CommissionStaffID1 and  CommissionStaffID2 which link to the table Staff via StaffID.

What I would like to do is summarise each Job Order by the StaffID whether they be CommissionStaff1 or CommissionStaff2.  The purpose is to be able to calculated commission to consutlants and oftern a Job Order has split commisssion.

[DellSC · Accepted solution]

You cannot link from two fields in one table to the same field in another table - you won't get any data.

For your situation, I would set Staff as the "master table" and then add a second copy of the JobOrder table to your report.  When you do this, Crystal will tell you that the table is already in the report and ask if you want to "alias" it.  It will then add the table to the report as "JobOrder_1".

Link from {Staff.StaffID} to {JobOrder.CommissionStaffID1 }

Link from {Staff_1.StaffID} to {JobOrder_1.CommissionStaffID2}.  Make this a left outer join so that you will still get data even if there is no value in {JobOrder.CommissionStaffID2}

You can then group by any of the fields in Staff on your report to get the data that you need.  You'll also have to combined the data from JobOrder and JobOrder_1 to get correct commission values - be sure to turn on "Use default values for nulls" in the Formula Editor so that you don't have to manually account for null values.

-Dell

[Former Member]

Thanks Dell, unfortunately not returning what I'm after.

StaffID

MATTG

NICKT

BARRYL

JobOrderID     CommissionStaffID1     CommissionStaffID2

200216601     NICKT                            MATTG

200216095     BARRYL                        

200214470     MATTG   

I want to summarise by the StaffID resulting in MATTG being recognised for JobOrderIDs 200216601 and 200214470.

[DellSC]

The only other way I can think of to do this involves using a Command (SQL Select statement) to get ALL of the data for your report.  Basically, you would use a union statement that works something like this:

Select

'Commission1' as CommType,

Staff.StaffID,

JobOrder.JobOrderID,

<any other fields you need>

from Staff

inner join JobOrder

    on staff.StaffID = JobOrder.CommissionStaffID1

where <any filter criteria>

UNION

Select

'Commission2' as CommType,

Staff.StaffID,

JobOrder.JobOrderID,

<any other fields you need>

from Staff

inner join JobOrder

    on staff.StaffID = JobOrder.CommissionStaffID2

where <any filter criteria>

You should then be able  to roll up everything to the individual staff level.

See this blog post for more information about working with Commands:  .

-Dell

[Former Member]

Hi Mark,

Create a Group on StaffID and insert summary on the Group Header or Footer of StaffID Group.

Hope this makes sense else we would need some sample raw data and expected O/P to help you out.

Regards

Niraj