Year End Vendor Contact Cleanup

One of my favorite firm leaders recently asked me for a ProLaw report she could run to identify which vendors have become inactive in the last year.

My initial suggestion was this contacts query:

(Contacts Status='active' and Contact Class contains 'vendor') and (does have any (Journals where Contacts Type='P' or Is Spoil='Y')) and (doesn't have any (Journals where Adding Date (Journals)>1/1/18))

*Note this client classifies all vendor contacts with the contact type of vendor, and you should too. If you have a "front office" contact that is a duplicate, I don't care. If you ever hope to be a one office client (and you should hope that), you will eventually have the need to separate vendor from front office contact. This is best practice.

I think the contact query above works, it's not perfect, there could be checks cut for 2017 vendor invoices that don't exist in 2018. It's an imperfect solution. Remember all you have to do to flip a contact back to active is to change the status back. No big deal. But.... what if I don't trust the query?

REPORTS! To the rescue! Below is some free code that you can use to create a contacts report that will produce by contact the amount of checks cut to that contact this year, last year, the year before that, the year before that.... Etc. The report looks like this when you save it out to excel (contact name and contact company fields not shown):

Then from there you can filter as appropriate. Maybe you set the current year to zero only, and then look at those contacts. Maybe you set all the years to zero like I have in the screenshot.

One thing to note, you can update contact status in mass if you have the permissions. So you might create the report just to verify your results that the initial contacts query is getting you the vendor list that is inactive, and then using the mass change to move your vendors to inactive in mass.

** Remember there is no undo with mass changes typically..... If you're unsure, do it the long slow way till you are sure.

--------------------------------------------------------------------------------------------------------------------------

If you're unsure what to do with this code, hire someone who does. Your system will stay working longer that way. Please only use this code if you know how to manipulate data sets and create reports without being a detriment to your firm. It's better to pay a programmer for an hour, than a system administrator for four to restore from back up.

/* C2Axis.com 1/3/19 by Jason Davey How to identify inactive vendors at year end. A ProLaw data set

So I left a bunch of columns that helped me see the big picture of this data set. It's a simple contacts report lookup passing the contact pkey to journals to get the sum spent, based roughly on the checks journals sql lookup. I did optimize some portions for speed.

Tested against large journals user, ran in zero seconds. The last year total amount uses the curr year minus the interval. If you just want last year, you can remove the additional lookups.

*/

declare @CurrYear int set @CurrYear = year(getdate())

select contacts.CONTACTS, contacts.FullName, contacts.CompName, contacts.Status, contacts.StatusDate, isnull(contacts.TaxID,'') TaxId, isnull((SELECT

--EntryNo,--CheckNo,--JournalDate,--journals.Contacts , sum(abs(JournalAccounts.Amount)) TotalAmount--, --IsChecking,--AccountDesc,--AccountNo,--AccountType, --isonhold,--voiddate,--invdate,--IsSpoil,--IsReverse, --iscleared,--cleareddate, --CASE CheckNo WHEN '0' THEN 'N' ELSE 'Y' END AS IsPrintedCheck, --CASE WHEN ContactsType = 'B' THEN CASE WHEN --COALESCE(CompSort,'') = '' THEN --Contacts.LastName+', '+Contacts.FirstName+' '+Contacts.MiddleName --ELSE CompSort END ELSE '' END AS BillingName, --CASE WHEN (ContactsType = 'P' OR ContactsType = 'I') THEN CASE WHEN --COALESCE(CompSort,'') = '' THEN --Contacts.LastName+', '+Contacts.FirstName+' '+Contacts.MiddleName --ELSE CompSort END ELSE '' END AS PayeeName, --RefNo

FROM Journals JOIN JournalAccounts ON Journals.Journals = JournalAccounts.Journals JOIN Accounts ON JournalAccounts.Accounts = Accounts.Accounts LEFT OUTER JOIN Contacts c2 ON Journals.Contacts = c2.Contacts LEFT OUTER JOIN CheckBatch on CheckBatch.CheckBatch = Journals.CheckBatch WHERE CONTACTSTYPE='P' --AND CONTACTS.COMPNAME LIKE '%BLUE CROSS BLUE SHIELD OF LOUISIANA%' -- Contact Specific and contacts.contacts = c2.contacts AND JOURNALACCOUNTS.AMOUNT<0 AND JOURNALS.CHECKNO IS NOT NULL AND {FN YEAR(JOURNALACCOUNTS.JOURNALDATE)}=@CurrYear-1 -- Query Specific AND (COALESCE(ContactsType,'') = 'P' OR IsSpoil = 'Y') --and IsChecking = 'Y' and IsOnHold = 'N' AND PostType >= 0 AND IsFinancials <> 'Y'),0) as LastYearTotalAmount, isnull((SELECT sum(abs(JournalAccounts.Amount)) TotalAmount--,

FROM Journals JOIN JournalAccounts ON Journals.Journals = JournalAccounts.Journals JOIN Accounts ON JournalAccounts.Accounts = Accounts.Accounts LEFT OUTER JOIN Contacts c2 ON Journals.Contacts = c2.Contacts LEFT OUTER JOIN CheckBatch on CheckBatch.CheckBatch = Journals.CheckBatch WHERE CONTACTSTYPE='P' --AND CONTACTS.COMPNAME LIKE '%BLUE CROSS BLUE SHIELD OF LOUISIANA%' -- Contact Specific and contacts.contacts = c2.contacts AND JOURNALACCOUNTS.AMOUNT<0 AND JOURNALS.CHECKNO IS NOT NULL AND {FN YEAR(JOURNALACCOUNTS.JOURNALDATE)}=@CurrYear -- Query Specific AND (COALESCE(ContactsType,'') = 'P' OR IsSpoil = 'Y') --and IsChecking = 'Y' and IsOnHold = 'N' AND PostType >= 0 AND IsFinancials <> 'Y'),0) as CurrYearTotalAmount ,

isnull((SELECT sum(abs(JournalAccounts.Amount)) TotalAmount--,

FROM Journals JOIN JournalAccounts ON Journals.Journals = JournalAccounts.Journals JOIN Accounts ON JournalAccounts.Accounts = Accounts.Accounts LEFT OUTER JOIN Contacts c2 ON Journals.Contacts = c2.Contacts LEFT OUTER JOIN CheckBatch on CheckBatch.CheckBatch = Journals.CheckBatch WHERE CONTACTSTYPE='P' --AND CONTACTS.COMPNAME LIKE '%BLUE CROSS BLUE SHIELD OF LOUISIANA%' -- Contact Specific and contacts.contacts = c2.contacts AND JOURNALACCOUNTS.AMOUNT<0 AND JOURNALS.CHECKNO IS NOT NULL AND {FN YEAR(JOURNALACCOUNTS.JOURNALDATE)}=@CurrYear-2 -- Query Specific AND (COALESCE(ContactsType,'') = 'P' OR IsSpoil = 'Y') --and IsChecking = 'Y' and IsOnHold = 'N' AND PostType >= 0 AND IsFinancials <> 'Y'),0) as TwoYearAgoTotalAmount ,

isnull((SELECT sum(abs(JournalAccounts.Amount)) TotalAmount--,

FROM Journals JOIN JournalAccounts ON Journals.Journals = JournalAccounts.Journals JOIN Accounts ON JournalAccounts.Accounts = Accounts.Accounts LEFT OUTER JOIN Contacts c2 ON Journals.Contacts = c2.Contacts LEFT OUTER JOIN CheckBatch on CheckBatch.CheckBatch = Journals.CheckBatch WHERE CONTACTSTYPE='P' --AND CONTACTS.COMPNAME LIKE '%BLUE CROSS BLUE SHIELD OF LOUISIANA%' -- Contact Specific and contacts.contacts = c2.contacts AND JOURNALACCOUNTS.AMOUNT<0 AND JOURNALS.CHECKNO IS NOT NULL AND {FN YEAR(JOURNALACCOUNTS.JOURNALDATE)}=@CurrYear-3-- Query Specific AND (COALESCE(ContactsType,'') = 'P' OR IsSpoil = 'Y') --and IsChecking = 'Y' and IsOnHold = 'N' AND PostType >= 0 AND IsFinancials <> 'Y'),0) as ThreeYearAgoTotalAmount ,

isnull((SELECT sum(abs(JournalAccounts.Amount)) TotalAmount--,

FROM Journals JOIN JournalAccounts ON Journals.Journals = JournalAccounts.Journals JOIN Accounts ON JournalAccounts.Accounts = Accounts.Accounts LEFT OUTER JOIN Contacts c2 ON Journals.Contacts = c2.Contacts LEFT OUTER JOIN CheckBatch on CheckBatch.CheckBatch = Journals.CheckBatch WHERE CONTACTSTYPE='P' --AND CONTACTS.COMPNAME LIKE '%BLUE CROSS BLUE SHIELD OF LOUISIANA%' -- Contact Specific and contacts.contacts = c2.contacts AND JOURNALACCOUNTS.AMOUNT<0 AND JOURNALS.CHECKNO IS NOT NULL AND {FN YEAR(JOURNALACCOUNTS.JOURNALDATE)}=@CurrYear-4 -- Query Specific AND (COALESCE(ContactsType,'') = 'P' OR IsSpoil = 'Y') --and IsChecking = 'Y' and IsOnHold = 'N' AND PostType >= 0 AND IsFinancials <> 'Y'),0) as FourYearAgoTotalAmount

from contacts left outer join ReportContactsAtoms on ReportContactsAtoms.Contacts = contacts.CONTACTS where ReportContactsAtoms.ReportID = @Report_Id--'94ffc0d7-0671-46bd-9f7b-0da4b893ff33'--

--select distinct ReportContactsAtoms.ReportID from ReportContactsAtoms