There are times where customers need to output all the notes on an account. There is not a report or way to print out those notes from SedonaOffice. Below are the steps to take to get a list of all of the notes listed on the account.
Overview
The notes list will be all notes for a specific account unless you follow the directions to give all notes for all accounts.
Steps:
1.Click on the SQL button in SedonaOffice.
If you have to add a password you may not have access
2.Copy the script below changing the highlighted fields
a.Then Paste in the SQL Command window
b.Hit Enter key or click on the Green Arrow
SCRIPT:
- To only pull specific customer remove the double dash “--“ that is in front of each Where and add the double dash “--“ that is in front of Order By
- Change the Green Highlighted areas as needed
SELECT Type='GEN','SVC/Job'='',c.Customer_Number,c.Customer_Name,cn.Note_Date,cn.UserName,cn.Notes
FROM AR_Customer_Notes cn
INNER JOIN AR_Customer c on c.Customer_Id = cn.Customer_Id
--WHERE c.customer_number = '48685-2'
UNION ALL
SELECT
Type='COL','SVC/Job'='',c.Customer_Number,c.Customer_Name, n.Note_Date, n.UserCode as UserName,n.Notes
FROM AR_Collection_Notes n
INNER JOIN AR_Collection_Queue q on q.Collection_Queue_Id = n.Collection_Queue_Id
INNER JOIN AR_Customer c on c.Customer_Id = q.Customer_Id
--WHERE c.customer_number = '48685-2'
UNION ALL
SELECT
Type='JOB',j.Job_Code as'SVC/Job',c.Customer_Number,c.Customer_Name, jn.Entered_Date as Note_Date,jn.UserCode as UserName,jn.Notes
FROM OE_Job_Notes jn
INNER JOIN OE_Job j on j.Job_Id =jn.Job_Id
INNERJOIN AR_Customer c on c.Customer_Id =j.Customer_Id
--WHERE c.customer_number = '48685-2'
UNION ALL
SELECT
Type='SVC',CAST(t.Ticket_Number as varchar(50)) as 'SVC/Job',
c.Customer_Number,c.Customer_Name, sn.Entered_Date as Note_Date,sn.UserCode as UserName,sn.Notes
FROM SV_Service_Ticket_Notes sn
INNER JOIN SV_Service_Ticket t on t.Service_Ticket_Id =sn.Service_Ticket_Id
INNER JOIN AR_Customer c on c.Customer_Id =t.Customer_Id
--WHERE c.customer_number = '48685-2'
--Order by c.customer_number
3.Copy data Results to export out
a.Highlight the 1st line in the Results
b.Use Control+C to copy data
4.Paste data Results into Excel or Word
a.Open application to paste the data into
b.Then Control+V to Paste
c.You may still need to clean up or move Notes with multiple lines so in one cell, or can move under the other notes cell
Spreadsheet Cleaned Up:
