Treating three fields as one

[N,_Hepinstall_(Guest)]

I want to write a sales report that we can use to calculate commission payments.____The master table is the transaction list, which contains all the info required in the report. Any given transaction has three fields that might contain a salesperson^s initials (PR, PR2 and PR3) and each of those salespeople might or might not earn commission on any given transaction (so there may be an amount in PR_AMT, PR_AMT2 or PR_AMT3, or in all three, or any two, or they might all be 0.00). ____The report has to sort and total the sales by salesperson, regardless of whether they are PR, PR2 or PR3 on the transaction. ____I can^t figure out how to gather the PR, PR2 and PR3 together and treat them as one, for the purpose of sorting. The sales reps are listed in another table with a field that matches the PR codes, if that will help. Thank you.

[kfleming]

Will only one of the three fields have a value or might there be a different value in each field?____Kathleen__R&R Support

[hepinstall]

Hi, Kathleen.____Any one, two, or three of them might have a value. There can be up to three producers on an account (PR1, PR2, PR3), any one of whom might or might not earn some commission (PR_AMT1, PR_AMT2, PR_AMT3). ____What I^ve got so far, which seems to be working in a limited test, is several aliases of the master table (transact.dbf) and a calc field called ^masterpr^ that looks like this: IIF(SCANNING(transac1),transac1->PR,IIF(SCANNING(transac2),transac2->PR2,transac3->PR3)), with a similar calc field for the PR_AMT field. ______Nancy