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.
bpetrickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ast2550Commented:
Have you ever tweaked a report?  I don't work with Peachtree, so I don't know how available report modifications are.

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.
0
bpetrickAuthor Commented:
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..  
0
ast2550Commented:
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.

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

bpetrickAuthor Commented:
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.  
0
ast2550Commented:
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
0
bpetrickAuthor Commented:
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.  
0
ast2550Commented:
I'd start by isolating the two records I was looking to link up.  There needs to be something identifiable...how about $$$?  As you cannot join the records by $$$, you can at least know you are looking at the corresponding records of the transaction.  Find an invoice for an overly large or odd value where it is less likely that there are bunches of them.  Then find the cash receipt record to pertains to it.  

There maybe some ID field that has a several data items concatenated together.  ID = 01234500044408012204 Where the first 6 characters are the invoice number, the next 6 are the customer number, the next 6 are the date and the last two are some sequential, as a silly example.

If that is the case, you can parse out the data into their own fields using a Database View, Database Stored Procedure or Crystal Reports Command.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bpetrickAuthor Commented:
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..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.