Can someone describe how to create a list of invoices with the following columns: invoice number, invoice date, customer name, customer number, invoice amount, paid amount, void amount, pending amount? I have been trying to use a smartlist and a trial balance. Also, we have two types of customers, employers and subscribers. I am looking for employers.
*This post is locked for comments
If you have access to sql I would create a view and then create a smartlist off that view or even a refreshable spreadsheet, that's what we use all the time. Victoria Yudin also has a bunch of great views for receivables. Gotta give a shout out to an excellent MVP and one who a wonderful straightforward website too :)
Here's a simple view we use to show all outstanding customer documents, whether they be invoices, payments, miscellaneous credit memos or debit memos. It lists out the customer along with the document, it shows the original amount of the document and the remaining balance. I reversed the signs on payments and credit memos so they stood out. It also shows old the document is so you know how long its been sitting around. Pretty helpful.
Let me know if you need anything else added. I added customer class at the bottom of the where clause and hopefully I called your employer class correctly, if I misspelled or if you want you can completely omit that part out and then filter the smartlist for the different customer classes.
select top 100000
r1.CUSTNMBR as CustomerNumber, m.CUSTNAME as CustomerName, m.custclas as Class, r1.DOCNUMBR as DocNumber,
case r1.RMDTYPAL
when '1' then 'Invoice'
when '2' then 'Reserved for scheduled payments'
when '3' then 'Debit Memo'
when '4' then 'Finance Charge'
when '5' then 'Service / Repair'
when '6' then 'Warranty'
when '7' then 'Credit Memo'
when '8' then 'Return'
when '9' then 'Payment'
end as 'DocType',
case
when r1.RMDTYPAL = 9 then DATEDIFF(day,r1.DOCDATE,GETDATE())
when DATEDIFF(day,r1.duedate,GETDATE()) >= 0 then DATEDIFF(day,r1.duedate,GETDATE())
when DATEDIFF(day,r1.duedate,GETDATE()) <= 0 then 0
end as 'DaysOutstanding',
case
when r1.RMDTYPAL in (9,8,7) then r1.ORTRXAMT *-1
else r1.ORTRXAMT
end as Original_Amount,
case
when r1.RMDTYPAL in (9,8,7) then r1.CURTRXAM *-1
else r1.CURTRXAM
end as Remaining_balance,
r1.DOCDATE as DocDate,
r1.duedate as DocDueDate
From RM20101 r1
left join RM00101 m on m.CUSTNMBR = r1.CUSTNMBR
where r1.CURTRXAM != 0
and CUSTCLAS = 'Employers'
group by r1.DOCNUMBR, r1.CUSTNMBR, m.CUSTNAME, m.CUSTCLAS, r1.RMDTYPAL, r1.DOCDATE, r1.DUEDATE, r1.ORTRXAMT, r1.CURTRXAM
order by r1.CUSTNMBR desc , r1.DOCDATE asc
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
