Crystal Reports linked to Great Plains 2

[dbigelow]

I am having trouble with a Crystal Report linked to Great Plains. The customer wants a Sales/History - AR/History report - I made one and it works fine - shows sales and payments by a certain date - however we have not run Remove Paid Transactions, as once the information has been moved to the history tables the report will show diferent results (fully paid documents will no longer appear). I tried the following and cannot make it work:

I linked the SOP30200 (Sales) table to the RM20201(Curent)and the RM30201(History) table using a left outer join in crystal. However if an invoice has been partially paid and the Removed Paid Transaction routine is run, the payments show on both the Current and History tables causing duplicate records and incorrect totals - is there a way either through linking of maybe different tables or unique records that I can pull all my sales items, current payments, and history items not on the RM20201(Current) table?

 

[Luvsql]

You could use the RM00401 table (RM Keys) which contains all documents, no matter what their status is (open, work, history). Do you need to just show the invoices and payments, or do you need to show that Invoice A was paid by Payment B?

 

[Vijay1998]

Try smartlist Builder from eOne. You can pretty much do this report in there.

if you want to stay in SQL, you would need to create a view for a union between the 2 tables (open and History).

The smartlist builder has a wizard which does this for you automatically.

 

[dbigelow]

Yes - I need to show the invoice amount - and the total payments. For example Invoice A might be for 1500 and the customer will pay in three equal payments --- if the report is run after the secone payment it would look like this:

Amount Paid amount Net
INVOICE A 1500 1000 500

 

[Dukester0122]

What I did was create a View in SQL Enterprise then made a Union Statement for tables RM20201 and RM30201. Link the View to both RM00401 and RM00101.

If you need Help in creating the View just tell me.

 

[Luvsql]

I have this one I've used in reports:

CREATE VIEW ARAPPLYINFO
AS
SELECT RM30101.DOCNUMBR,RM30101.TRXDSCRN, RM30101.RMDTYPAL, RM30101.CUSTNMBR, RM30101.DOCDATE,
RM30101.GLPOSTDT,RM30101.ORTRXAMT, RM30201.APTODCNM,RM30201.APTODCTY,RM30201.APTODCDT,
RM30201.CURNCYID, RM30201.APPTOAMT
FROM RM30101 INNER JOIN RM30201 ON
RM30101.DOCNUMBR=RM30201.APFRDCNM AND RM30101.RMDTYPAL=RM30201.APFRDCTY
WHERE RM30101.VOIDSTTS = 0
UNION ALL
SELECT RM20101.DOCNUMBR,RM20101.TRXDSCRN,RM20101.RMDTYPAL, RM20101.CUSTNMBR, RM20101.DOCDATE,
RM20101.GLPOSTDT,RM20101.ORTRXAMT, RM20201.APTODCNM,RM20201.APTODCTY,RM20201.APTODCDT,
RM20201.CURNCYID, RM20201.APPTOAMT
FROM RM20101 INNER JOIN RM20201 ON
RM20101.DOCNUMBR=RM20201.APFRDCNM AND RM20101.RMDTYPAL=RM20201.APFRDCTY
WHERE RM20101.VOIDSTTS = 0

 

[dbigelow]

All - thank you for helping me - I used the RM00401 keys table with the RM30101, RM 30201, RM 20101 and the RM 20201 linked by the keys table I was able to create my report if you want the SQL version of the links let me know