bpetrick
asked on
Crystal Report Writer and Peachtree Salesperson commission
I am having problems getting a report out of Peachtree using Crystal Report Writer. I need to pay salespeople commission in the month when cash is received for an outstanding invoice.
In Peachtree we assign a salesperson at the time of creating an invoice. The problem is that if I try to pull the sales receipts for the month out of the cash receipts journal, Peachtree uses the default salesperson in the setup file for that customer.
I can list the invoices and see the proper salesperson in Crystal but I need a way to show the receipts that were applied to that invoice and the receipt date.
In Peachtree we assign a salesperson at the time of creating an invoice. The problem is that if I try to pull the sales receipts for the month out of the cash receipts journal, Peachtree uses the default salesperson in the setup file for that customer.
I can list the invoices and see the proper salesperson in Crystal but I need a way to show the receipts that were applied to that invoice and the receipt date.
ASKER
Originally that is what I thought - It should be easy. I am not real familiar with Peachtree but the accountant said it cannot be done how he wants to do it. He has the problem of cash receipts pulling the salesperson from the setup screen rather than the invoice it is applied to. I have the data file structure for Peachtree.
I have the invoice, date, amount, salesperson, etc.. and sort by salesperson, but this is not what the commission is paid on. It is paid by cash receipts in a date range associated with this invoice. I cannot find the link from a cash receipt to the invoice it has been applied to.
Peachtree is kind of goofy in that it has all its transaction information in the jrnlhdr or jrnlrow files, no matter if it cash receipt, invoice, payroll, ap, etc..
I have the invoice, date, amount, salesperson, etc.. and sort by salesperson, but this is not what the commission is paid on. It is paid by cash receipts in a date range associated with this invoice. I cannot find the link from a cash receipt to the invoice it has been applied to.
Peachtree is kind of goofy in that it has all its transaction information in the jrnlhdr or jrnlrow files, no matter if it cash receipt, invoice, payroll, ap, etc..
First, I'd look to find the data that you are looking to incorporate into the report. Figure out an invoice that has a Invoice.salesman different than the Customer.salesman. Then, find the record in the table for that invoice to see if you can find the field that the Invoice.salesman is stored.
Second, I'd open the report and look at the database tables that are used in the report and how they are joined and I'd get to understand how it was grouping and filtering (Select Expert) the data.
It'll really take some solid understanding of the data to modify the report. So, get to know how it is working.
Also, I'd probably ensure that there is a backup of the report before saving any modifications.
Second, I'd open the report and look at the database tables that are used in the report and how they are joined and I'd get to understand how it was grouping and filtering (Select Expert) the data.
It'll really take some solid understanding of the data to modify the report. So, get to know how it is working.
Also, I'd probably ensure that there is a backup of the report before saving any modifications.
ASKER
Thanks for the response.. The first two paragraphs are what I have done so far. I know what invoices are showing the incorrect salesman in the receipts, I have created a report from the table that has the original invoice and correct salesman. The problem I think I may be having is that all transactions are stored in the same table. The type of transaction is indicated by a numeric value. For example - a cash receipt is a 1, payroll is 2, Invoices are 3, ap is 4, etc.. so there is no linking of tables. I can filter to only show the cash receipts or invoices or both but the data will be wrong either way unless I can find a link in there. I can either have the report show cash receipts in the correct month with the wrong salesperson or the invoices with the correct salesperson but not showing what month they were paid. I need some Peachtree wiz to help me with the Peachtree file structure.
You can link a table to itself. The second copy of the table will have an alias so your query can distinguish between them.
Add the table twice, then you'll need to find a field that links the two together...Invoice Number, for instance. Then filter (Select Expert) so that table1.type = 1 and table2.type = 2
Add the table twice, then you'll need to find a field that links the two together...Invoice Number, for instance. Then filter (Select Expert) so that table1.type = 1 and table2.type = 2
ASKER
OK.. that is my problem.. I dont know what the link is.. from the cash receipt entry to the original invoice. Other accounting programs that I have worked with display an "apply to" field or something similar. Your last addition is helpful. I will be able to use that method. Now I just need to know the commonality - which seems a lot more difficult than it should be. Thanks for the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had to link the reference field of the jrnlhdr with the invforthistrx field from the jrnrow to make it work. Then linked the employee record from jrnlhdr invoice trx to the employee file. But the report is extremely slow in reading records. Thanks for the help..
Do you know the data structure of your invoice table(s)? As you've said that your Invoice entry screen has a field that your enter the salesman for the order. We need to know where that data is stored.
If so, it should be a piece of cake.