nedd name of payment history file

[newbeeeeeeeee]

Is there a table containing payment history data along with the invoice number in Great Plains 5.5 using bTrieve?
This is so we can pay commisions based on what's actually received intead of what's sold.
I've seen a few candidates using the Report Writer but nothing I can use yet. Any help would be great.

Thanks

 

[Luvsql]

Does 5.5 have Smartlist? If so, you can open the receivables transactions and click on search > doc type = payments, void status = normal. You can then export to excel.

Do you also need what these payments were applied to?

 

[newbeeeeeeeee]

No, I don;t think it does have Smarlist. In the meantime, I found a posting which makes reference to rm30200 which has records marked with a 1 in the doctyp filed, indicating they are invoices and a docnumber field which contains the invoice number. I'll try that and post my results here in case anyone is interested.

 

[minerva9]

If you are on v5.5, you won't have Smartlist. The table that has the invoice and payment info is RM30201. The APTODCNM is your invoice#, APFRDCNM is the payment#, and the APPTOAMT is the part of the payment applied (paid) to your invoice. You can check out your data either via sql/access...

Hope this helps!

 

[newbeeeeeeeee]

thanks for the reply, strangely emough the file in my system is RM20201, RM30201 is empty. Maybe we have different versions or different implenentations.
Anyway, for the crystal report it needs to have a link to the salesperson file as well as to the invoice master(names are my own) so the query I have looks something like this:
SELECT
SOP30200."SOPNUMBE", SOP30200."INVODATE", SOP30200."CUSTNAME", SOP30200."SLPRSNID",
RM00301."SLPRSNFN", RM00301."SPRSNSLN", RM00301."COMPRCNT",
RM20201."APPTOAMT"
FROM
"SOP30200" SOP30200,
"RM00301" RM00301,
"RM20201" RM20201
WHERE
SOP30200."SLPRSNID" = RM00301."SLPRSNID" AND
SOP30200."SOPNUMBE" = SOP30300."SOPNUMBE" AND
SOP30200."SOPNUMBE" = RM20201."APTODCNM" AND
SOP30200."INVODATE" >= ? AND
SOP30200."INVODATE" <= ? AND
SOP30200."SLPRSNID" >= 'aaaaaaa' AND
SOP30200."SLPRSNID" <= 'zzzzzzzzzzzzzzz'
ORDER BY
SOP30200."SLPRSNID" ASC,
SOP30200."CUSTNAME" ASC,
SOP30200."SOPNUMBE" ASC

THe only problem is that since I need to refer back to the invoice master for the saleserson id, my join is giving me duplicate records which dissapear when I do a select distinct on MS QUery but it's not supported on btrieve.
Is there another table that I can use to link back to for the salesperson ID besides SOP30200?

 

[minerva9]

Hi,

Sorry about that, RM30201 is a "history" table and RM20201 is an "open" table. RM30201 should get populated when you run "Paid Transaction Removal" (Sales Routines).

Could you use the "SLPRSNID" from the header tables?
RM20101 for "open" documents, and RM30101 for "history" documents...

 

[newbeeeeeeeee]

Looks like they never ran that "paid transaction removal" program, is it possible to get all the data I need from the op[en file instead(rm20101)? I just need the amount paid, invoice number, customer number and salesperson id to link it to the salesperson master and get their name. Ideally the invoiced amount would be great,
Also, I need t find an alternate way because the query I posted before takes forever to run and not feasible.

 

[RWahlert]

I'm joining this party a little late.

If you want apply information, payment amount and invoice number you will have to use the RM20101 and the RM20201. the RM20201 is the apply to table and will have a record for each invoice applied to a payment. You can link these tables using the RM20101.DOCNUMBR to the RM20201 APFRDOC field.

I hope this helps.

 

[newbeeeeeeeee]

Thanks for the posting , I will try that.

 

[newbeeeeeeeee]

My updated query looks like below but it takes forever to process(I have to cancel it before it finishes), is there a limit on btrieve as far as the number of tables you can include on a select?

SELECT
RM20101."CUSTNMBR", RM20101."DOCNUMBR", RM20101."POSTDATE", RM20101."ORTRXAMT", RM20101."SLPRSNID",
RM00101."CUSTNAME",
RM00301."SLPRSNFN", RM00301."SPRSNSLN", RM00301."COMPRCNT"
FROM
RM20101 RM20101, RM20201 RM20201, RM00301 RM00301, RM00101 RM00101
WHERE
RM20101."DOCNUMBR" = RM20201."APFRDCNM" AND
RM20101."SLPRSNID" = RM00301."SLPRSNID" AND
RM20101."CUSTNMBR" = RM00101."CUSTNMBR" AND
RM20101."POSTDATE" >= ? AND
RM20101."POSTDATE" <= ? AND
RM20101."SLPRSNID" >= ? AND
RM20101."SLPRSNID" <= ?
ORDER BY
RM20101."SLPRSNID" ASC,
RM20101."CUSTNMBR" ASC,
RM20101."DOCNUMBR" ASC

 

[newbeeeeeeeee]

Looks like my salesperson id on the rm20201 file is not populated, that's why the query never finished, it was doing a table scan. ANyway, is there another file I can link to to get the salesperson id and match it to the rm20201 and rm20101 files?

 

[RWahlert]

You have the salesperson master table, RM00301, in your query. I assume you have salespeople in it and they are assigned to customers in the customer master, RM00101. You should add the RM00101 table. Link this table to the RM20101 by the customer ID. Remove the link the connecting the RM00301 to the RM20201 and then link the RM00301 to the RM00101 by the sales person ID field. You can then use the sales person ID field in the RM00101 in your where clause of your select statement, and since the tables are linked to the customer master you will be able to pull in data from the open and apply tables.

Use left outer joins for alll your links.