Excel or smartlist report that will pull in the customer notes from customer card

(0) Share

Report

Posted on by Katie Pieczynski

3,225

Does anyone know if there is a report or if I could build a report that would have the customer number/name and the notes from the customer card? Basically I run a report that has all of our past due customers and what balance they have in each category (this is a smartlist) I would like to include the notes from the customer card on that for when I send it to my boss. Any suggestions would be great.

Thank you,

*This post is locked for comments

All responses (9)

Answers (1)

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

Katie,

This might help: victoriayudin.com/.../how-to-use-a-sql-view-in-smartlist-builder. :-)

Have a great weekend!
Katie Pieczynski 3,225 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

Thank you very much. I have slb but I have never done an SQL script with it. I will look into that.

Thanks again
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

David,

Excellent idea, since SmartLists sometimes does not handle nulls properly. I am partial to coalesce instead of isnull, but they will both accomplish the same thing here. :-) I edited the code above to add this.

Thanks again!
davidf. on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

Victoria,

Thanks for posting that. One small suggestion... add isnull to the text field so it shows a blank instead of NULL.

isnull(cast(n.TXTFIELD as varchar(2000)),'') Notes

David
Suggested answer

Victoria Yudin 22,768 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

Hi Katie,

Do you have SmartList Builder? If so, you could use the code below to create a SQL view and then create a new SmartList from that. This code is a small variation on my code published here: http://victoriayudin.com/2012/01/25/sql-view-for-current-receivables-aging-in-dynamics-gp/, it is just adding the customer note:

create view view_Current_Receivables_Aging_Summary

as

/*

created May 31, 2013 by Victoria Yudin - Flexible Solutions, Inc.

one line per customer, current AR aging with hard-coded aging buckets

functional currency only

*/

select

CM.CUSTNMBR Customer_ID,

CM.CUSTNAME Customer_Name,

CM.PYMTRMID Customer_Terms,

CM.CUSTCLAS Customer_Class,

CM.PRCLEVEL Price_Level,

sum(case

when RM.RMDTYPAL < 7 then RM.CURTRXAM

else RM.CURTRXAM * -1

end) Total_Due,

sum(case

when DATEDIFF(d, RM.DUEDATE, getdate()) < 31 and RM.RMDTYPAL < 7 then RM.CURTRXAM

when DATEDIFF(d, RM.DOCDATE, getdate()) < 31 and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1

else 0

end) [Current],

sum(case

when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60 and RM.RMDTYPAL < 7 then RM.CURTRXAM

when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60 and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1

else 0

end) [31_to_60_Days],

sum(case

when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90 and RM.RMDTYPAL < 7 then RM.CURTRXAM

when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90 and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1

else 0

end) [61_to_90_Days],

sum(case

when DATEDIFF(d, RM.DUEDATE, getdate()) > 90 and RM.RMDTYPAL < 7 then RM.CURTRXAM

when DATEDIFF(d, RM.DOCDATE, getdate()) > 90 and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1

else 0

end) [91_and_Over],

CS.LASTPYDT Last_Payment_Date,

CS.LPYMTAMT Last_Payment_Amount,

coalesce(cast(n.TXTFIELD as varchar(2000)),'') Notes

from RM20101 RM

inner join RM00101 CM

on RM.CUSTNMBR = CM.CUSTNMBR

inner join RM00103 CS

on RM.CUSTNMBR = CS.CUSTNMBR

left outer join SY03900 n

on CM.NOTEINDX = n.NOTEINDX

where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0

group by CM.CUSTNMBR, CM.CUSTNAME, CM.PYMTRMID, CM.CUSTCLAS,

CM.PRCLEVEL, CS.LASTPYDT,CS.LPYMTAMT, cast(n.TXTFIELD as varchar(2000))

go

grant select on view_Current_Receivables_Aging_Summary to DYNGRP
KirkLivermont 5,985 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

Select the note field in SLB and click the blue over arrow. There should be a check box that allows the field to display as text.
Katie Pieczynski 3,225 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

I was able to just add the column note index to my smartlist, but now the notes are just coming in as numbers. Do I have to format it to text? If so, how do I do that? Thank you
Andrew King on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

You need to link to the Record Notes Master to the Customer Master Note Index field. It also needs to be a left outer join (otherwise Customers without comments won't show up). Table is SY03900.
Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

RE: Excel or smartlist report that will pull in the customer notes from customer card

You should be able to add this to either a smartlist or refreshable excel report by adding the Note Index field from the RM00101 Customer Master table.