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