Mass update ConnectWise agreement exclusions

Agreement Types are great, since they let you create a standard starting place for how you bill your clients, but changes you make to an agreement type are not propagated to the individual agreements you’ve created from that agreement type. Over time, as you make adjustments to how you structure your client engagements, you not only have to update the agreement types (so future agreements are created properly), but also all of the individual agreements. This quickly turns into a great deal of manual work that is both time consuming and potentially fraught with human error.

A great example of this type of manual mass update is managing agreement exclusions. You may not realize this, but every time you add a new work role or work type, it is automatically included in the scope of all of your existing agreements. This is because ConnectWise only stores the work roles and work types you specifically exclude from an agreement, which means that all new work roles and work types are included by default.

Wouldn’t it be great if you could update agreement exclusions without having to touch every single existing agreements? While you can’t do this through the ConnectWise interface, it is possible to make these updates directly though SQL. Here’s how:

Step 1 – Update the agreement exclusions by agreement type

The first thing you’ll need to do is make sure all of your agreement types are configured with the appropriate work role and work type exclusions.

Navigate to Setup > Setup Tables > Agreement Types. Find the agreement type and click the Exclusions link under Application Parameters. Use the interface to select which work roles and work types should be “Not Covered” by the agreement, then click the Done button. Repeat this process for all of the agreement types you’d like to update.

Step 2 – Make a list of the agreement types you’d like to update

Next, you’ll need to retrieve the AGR_Type_RecIDs for each of the agreement types you updated in step 1. This query returns all of the agreement types in ConnectWise along with their unique record id:

SELECT     AGR_Type_Desc, AGR_Type_RecID
FROM         dbo.AGR_Type
WHERE     (AGR_Inactive_Flag = 0)
ORDER BY AGR_Type_Desc

Here is an example of what SQL will return:

For our example, we’ll be working with Gold (42), Platinum (41) and Silver (43).

Step 3 – Delete existing agreement exclusions

Before you populate the work role and work type exclusions you updated in step 1, you’ll need to delete the existing exclusions:

DELETE FROM AGR_Exclusion
FROM         AGR_Exclusion INNER JOIN
                      AGR_Header ON AGR_Exclusion.AGR_Header_RecID = AGR_Header.AGR_Header_RecID
WHERE     (AGR_Cancel_Flag = 0) AND (AGR_Type_RecID IN (41, 42, 43)) AND (AGR_Date_End > GETDATE()) OR
                      (AGR_Cancel_Flag = 0) AND (AGR_Type_RecID IN (41, 42, 43)) AND (AGR_NoEnd_Flag = 1)

Update lines 4 and 5 (in both places) with the list of AGR_Type_RecIDs you gathered during step 2 (in this example, Gold, Platinum, and Silver agreement types). Lines 4 and 5 also make it so you’re only targeting active agreements (not cancelled, no ending date / or ending date in the future).

Step 4 – Populate new agreement exclusions

Now it’s time to populate the work role and work type exclusions that you updated during step 1:

INSERT INTO AGR_Exclusion
                      (AGR_Header_RecID, Activity_Type_RecID, Activity_Class_RecID, Updated_By, Last_Update)
SELECT     AGR_Header.AGR_Header_RecID, AGR_Type_Exclusion.Activity_Type_RecID, AGR_Type_Exclusion.Activity_Class_RecID, 'sqlscript' AS Updated_By, GETDATE()
                      AS LastUpdate
FROM         AGR_Type_Exclusion LEFT OUTER JOIN
                      AGR_Header ON AGR_Type_Exclusion.AGR_Type_RecID = AGR_Header.AGR_Type_RecID
WHERE     (AGR_Header.AGR_Cancel_Flag = 0) AND (AGR_Type_Exclusion.AGR_Type_RecID IN (41, 42, 43)) AND (AGR_Header.AGR_Date_End > GETDATE()) OR
                      (AGR_Header.AGR_Cancel_Flag = 0) AND (AGR_Type_Exclusion.AGR_Type_RecID IN (41, 42, 43)) AND (AGR_Header.AGR_NoEnd_Flag = 1)

Again, update line 7 with the AGR_TYPE_RecIDs you gathered during step 2 and used during step 3.

That’s it. Going forward all you need to do is update the exclusions on your agreement types and then run the scripts above.

Keep in mind that this process blows out all of the work role and work type exclusions on the targeted agreements, and overwrites them with the exclusions configured on the agreement type. Don’t use this process if you have agreements with custom exclusions that you need to preserve.

Want to do more?

Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact usfor one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.