Integration to sync sales orders from Connectwise Manage PSA to e-automate.
Jump to: Overview | Samples | Setup | Variables | Testing | Alert Functionality | Best Practices & Tips | Related Alerts
Click here for our short videos on the sales order sync!
Overview
Overview
Tools like ConnectWise Sell allow salespeople to see real-time costs and availability for the IT products they sell. Once their opportunity is approved in Sell, the items need to be ordered via e-automate, while keeping the salesperson updated on the status of their order. The sync will push the order into e-automate for ordering and then you can use ID348 to keep everyone notified on the status of the order. The sync will also update a custom field on the order in ConnectWise with the order status from e-automate.
Sales orders sync when set to a specified status in ConnectWise. Note: we do not support changes to the order once it has been created in e-automate. The sales order sync is a two-part process, ID966 pulls the information from ConnectWise and then our ID634 process creates the sales order in e-automate. You will need to subscribe to ID634 along with ID966 for the sales order sync.
The sync first checks to see if all the items on the order exist in e-automate (product ID in CW to the item number in EA). If the items do not exist, the sync has the option to create the items based on the item product type templates. Attachments on the order in Connectwise will be brought over and attached to the sales order in e-automate. All procurement and invoicing of the order take place in e-automate.
Run Schedule: Every 15 minutes
Type of Output: Data push
* * *
Sample
Sample
Jump to: Sales Order Header | Items | Billing/Shipping Tab | Messages Tab | Attachments
Sales Order Header
Sales Order Header
1. The sales order number will be set to the order number from ConnectWise, with the prefix set in variable 5.
2. The customer will match the company from ConnectWise. Notes on customer mapping are here.
3. The description will be the opportunity name, with the subtotal amount from ConnectWise. You can change what posts in the description of the sales order based on the ID634 variables. More details here.
4. The order status will match the default set in variable 10, if no default is set there, the order will use the default status from ID634. More details on order status are here.
5. The PO number from the order in ConnectWise will populate the PO field on the order in e-automate.
6. The sales rep will match the rep from ConnectWise, as long as the member is mapped to a valid sales rep in e-automate. More details on sales rep are here.
7. The order type will be set by this hierarchy:
A. Order type custom field from ConnectWise.
B. Default order type set on variable 7 on ID966.
C. Default order type set on variable 1 on ID634.
See this link for more details on order type.
8. The warehouse on the order will be set by this hierarchy:
A. Warehouse from custom field in ConnectWise.
B. Default warehouse set on variable 4 on ID966.
C. Branch warehouse
See this link for more details on the warehouse settings.
9. The subtotal should match from ConnectWise to e-automate.
Items
Items
Each product on the sales order in ConnectWise will have a matching line item on the sales order in e-automate. New items are created in e-automate when Variable W is set to Yes. Items are created based on the templates, there will be one item template for each product type from ConnectWise.
1. Items that can't be created based on the templates will have the Error Item set in ID634 added instead. The description, quantity and pricing will match the product from ConnectWise. Specific errors as to why the item couldn't be created will show on the messages tab of the order. You'll need to create the item in e-automate, add it to the order and then cancel the error item from the order.
Items marked as Do Not Bill or No Charge or show as canceled on the sales order in ConnectWise will NOT sync to the sales order in e-automate.
Billing/Shipping Tab
Billing/Shipping Tab
1. The shipping site from ConnectWise will match to the ship to location in e-automate, as long as the sites are mapped. More details on site linking here.
2. The shipping contact name will populate in the Attn: line on the ship to in e-automate.
3. The ship method set on the order will follow this hierarchy:
A. Value from custom field in ConnectWise.
B. Default shipping method set in ID634.
More details on the shipping method are here.
Messages Tab
Message Tab
The messages tab will show the opportunity name, subtotal of the order, and any errors importing the order. You can suppress data from posting to the Messages tab using the Hidden2 variable on ID634.
Common errors are either the item template missing a code or the template not found.
Attachments
Attachments
Attachments to the order in ConnectWise will sync to e-automate and be added as attachments to the sales order. More details on attachments are here.
* * *
Setup
Setup
Jump To: EA Settings | CW Settings | Subscribe to Alerts
EA Settings
1. Order Status- What order status should be used for these orders in e-automate? This is set in Variable 10. More details on order status in e-automate are found here.
2. Order Type - What order type should be used for these orders in e-automate? This is set in Variable 7. More details on order types here.
4. Optional Defaults - The following are optional values that can be set in ID966 but are not required:
Sales Rep - set a default rep to use on all orders created. With no default set, the sync will use the matching rep from the ConnectWise order. If there's no matching rep, the customer rep is used. More details on sales reps are here.
Warehouse - set a default warehouse to be used on all orders created. More details on warehouse settings are here.
Branch - set a default branch to be used. If no default is set, the option set on ID634 Variable Z for Branch Source will be used. (Branch from customer or branch from sales rep).
Department Number - set a default department to be used on all orders created. The department field will be blank if no default is set.
CW Settings
1. Order Status to trigger sync - determine the status in ConnectWise to use to sync the order to e-automate. More details on the status in ConnectWise are here.
2. Order status after sync - optionally, you can create a status in ConnectWise to update the order to after the sync to indicate that no edits should be made to the order.
3. Optional Custom Fields
Order Type - Create a drop-down field for Order Types, details here.
Ship Method - Create a drop-down field for Shipping Methods, details here.
EA Status - Create a custom text field for the status of the order in e-automate, details here.
Warehouse - Create a drop-down to select the warehouse in e-automate, details here.
Subscribe to Alerts
1. Once you have the settings in ConnectWise and e-automate determined, subscribe to ID966 and ID634, being sure to update the variables as needed. See this link for details on the variables for ID634.
If you don't have a login to our subscription site, you can email help@ceojuice.com to have us set you up with a login.
2. If you are not already subscribed to ID747, you'll need to subscribe to that alert as well as ID747 is required to be running to implement ID966. See this link if you have questions on ID747.
* * *
Variables
Variables
Variable W: Create Items from Templates: Set to Yes to have the sync create items from the product type templates. When set to No, if the item on the order in ConnectWise does not have a match in e-automate, the item will not be created and the sync will add the error item from ID634 to the order as a placeholder.
Variable 1: SalesOrder Status - What sales order status in Connectwise should trigger the integration to move the order to e-automate. More details on order status here.
Variable 2: Post Sync Status - Change the status of the sales order in Connectwise to this status after the order has been imported into e-automate. The default value is Synced, DO NOT EDIT. **You will need to create this order status in ConnectWise if you choose to use it, see notes on how to do that here.
Variable 3: Default Sales Rep - Always default imported orders to have this sales rep. If blank, the sales rep will match the Connectwise sales rep, if there is a match. If there is no match between EA and CW, the sales rep will default to the sales rep for the customer in e-automate.
Variable 4: Default Warehouse - Warehouse to use for all imported sales orders. If blank, the Sales Orders Customer branch default warehouse will be used. More details on warehouse setup here.
Variable 5: SalesOrder Prefix - The imported sales order will reflect the Connectwise order number with the prefix set in this variable. (We can't use the CW number directly as there may be an existing order with that number in e-automate). The default value is CW. (for example, if the CW order number is 1234, the order will import to e-automate as order number CW1234). *Required
Variable 6: Default Branch - If you would like all imported sales orders to use the same branch, specify the branch number here. More details on branch are here.
Variable 7: Order Type - Default order type to use for all imported orders. All orders imported from Connectwise will use this type. If you would like to have multiple order types, you'll need to set up a custom field in ConnectWise, see details on how to do that here. If not provided, orders will use the default order status for imported sales orders from the variable settings on ID634.
Variable 8: Department Number - Optional department number for all imported sales orders.
Variable 10: Order Status - Optional default order status for all imported orders. If provided, all orders imported will use this status. If this variable is left blank, the default option set in ID634 will be used. More details on the order status here.
Variable 9: Default Customer - For versions of e-automate 20.1 and higher, you are able to change the customer on sales orders that are in an Open status. If you enter a default customer number in this variable, sales orders that do not have the customer mapped between ConnectWise and e-automate will be inserted with this default customer. Leaving this variable blank will cause orders where the customer mapping is not in place to not insert into e-automate. **ONLY ENTER A CUSTOMER NUMBER IN THIS VARIABLE IS YOU ARE ON E-AUTOMATE 20.1 OR HIGHER!!**
Variable 11: ConnectWise Credentials - If you have multiple ConnectWise API credentials added to the CEOJuice website, select the set to use with this integration. (Most likely, you only have one set of credentials and would not need to update this variable.)
Variable 12: Exclude Product Types - If you would like to exclude any products from syncing to e-automate, list the product types to exclude. No items of these product types will sync to the e-automate sales order.
* * *
Testing
There are two parts to testing the sales order sync, setting up the testing environment and updating the item templates, and then pushing orders to the test database to check the import process.
Jump to: Testing Setup | Item Templates | Testing Checklist
Testing Setup
Testing Setup
If you already have portions of the ConnectWise sync running, you most likely have these setup steps completed. You may need to have these steps completed with the help of your IT department.
1. Create API keys for CEO Juice in ConnectWise and enter them on the CEO Juice website (See this link for instructions on how to do that) .
2. We do require that the initial sync goes from your production ConnectWise database to a Test/Development EA database, so you will need to have a test e-automate database set up for this process. (See this link for notes on how to create a test EA database.) If you have a test ConnectWise database, we can initially sync from that, but it is not required. (This sync process only pushes information from ConnectWise to e-automate while in test). **It's important that this test e-automate database is not refreshed with new data during the testing process!!
Be sure that the test e-automate database has been updated with the needed codes for the sales order sync to work:
A. On Hold Codes from ID634
B. Error Item set on ID634
C. All item templates have been updated with either an expense or inventory code and a sales code.
3. We also require a test copy of our CEO Juice database to be created for the testing phase. See this link for details on how to do that.
Item Templates
Item Templates
Items created by the sales order sync will be based on the same item templates used by the contract sync. There will be one item template for each product type in ConnectWise.
The item number will match the product ID from ConnectWise and the Description will match the product description from ConnectWise. If you have the agreement sync running (ID964), these templates will already exist in e-automate, if not, we will create them for you. You'll need to update the following codes on the templates for the sales order sync:
1. Category - required field for item records in e-automate.
2. Unit of Measure - required field for item records in e-automate.
3. Tax as - This field is greyed out until either a sales code or service code is added to the item record. Once one of those fields has been filled in you can change the tax as from None to a different tax flag.
4. Item type - either Inventory or Expense. This is a required field for items to be added to a sales order and must be filled out on all item templates that will be used for the sales order sync.
5. Inventory or Expense Code - This is a required field for items to be added to a sales order and must be filled out on all item templates that will be used for the sales order sync.
6. Sales code - This is a required field for items to be added to a sales order and must be filled out on all item templates that will be used for the sales order sync.
7. Service code - Optional field, only used if the item is added to a service call.
8. Serviceable as an item - Checked on all item templates by default when created.
9. Contract base distribution code - used when item is added to a service contract.
Bin defaults:
Bin default settings will be copied over to the item created. **This will not change the warehouse or bin on the item on the specific sales order that the sync creates**
Warehouses:
Warehouses and stocking codes set on the item templates will be copied over to new items created.
Testing Checklist
Testing Checklist
Review the orders created to check that everything has been imported correctly. A few things to review:
- The description line of the order will show the total amount, does this match the total on the order?
-
If there were any attachments on the order in ConnectWise, did those sync over correctly?
- Verify that the attachment shows the correct name in e-automate.
- Open the order and verify that there are no errors opening and viewing the attachment.
-
Do all of the items on the order in ConnectWise show on the order in e-automate?
- Are there items added with the error item instead of the proper item?
- After fulfilling the order, are the proper revenue accounts being used?
After confirming that everything is syncing properly, we will move the templates to your production e-automate and repoint the sync to production to push in the sales orders.
* * *
Alert Functionality
Alert Functionality
When the sales order status in ConnectWise is updated to the status indicated in variable 1, the sync will pick up the order details and insert them into our background tables. Once the order has synced to the background tables, no changes made in ConnectWise will sync. We recommend using variable 2 to set a status to change the order to after the sync, so that users in ConnectWise will know not to edit the order.
The process that creates the sales order in e-automate is ID634, see details on that process here.
Attachments to the order from ConnectWise will sync across and attach to the sales order in e-automate. There is a 15-minute delay between the order creation and the attachments getting added to the order.
Post sync, there is an option to have the sync update a custom field on the sales order in ConnectWise with the sales order status from e-automate.
Jump to: CW Order Status | Customer Mapping | Ship To Customer | Order Type | Sales Rep | PO Number | Branch | Warehouse | EA Order Status | Parent/Child Items | Serialized Items | CW Billable Options | Ship Method | Attachments
CW Order Status
CW Order Status
The order status in ConnectWise drives when the order is synced to e-automate. For example, if you have the status to sync set to Approved (Variable 1) when the status is set to that, the order will sync to e-automate:
Optionally, you can set a status in variable 2 to update back to ConnectWise once the order has been pushed to e-automate. For example, if you set variable 2 to Synced, DO NOT EDIT, then the order in ConnectWise would show this after the order has been synced:
This variable is optional, if you leave it blank, the status of the order in ConnectWise will remain as what is set in variable 1.
You can create statuses for the orders in ConnectWise by going to Setup Tables - Sales order Status List:
**Note that once the order has been moved to the status set in Variable 1 we do not support changes made to the order in ConnectWise. If you need to make changes, you'll need to do a change order and create a new sales order.**
Customer Mapping
Customers must be mapped between the two systems for the sales order to import correctly. For this reason, ID747 (Customer Sync) is required to be running, click here for an overview of the customer sync.
The customer mapping is done via a custom property in e-automate. The company RecID from ConnectWise is populated in the custom property ZCJ_CW_CompanyID in e-automate. This value must be populated for the sales order sync to find the customer match and create the sales order.
The company RecID is found in ConnectWise in the company list view:
If you do not see this column in the list view, click on the gear icon in the upper right corner and move the Company_RecID from the hidden columns to the visible columns.
This value is entered into the custom property ZCJ_CW_CompanyID in e-automate:
If the customer mapping is not in place, the sales order will not import.
**If you are on e-automate version 20.1 or higher, you have the option to enter a default customer in variable 9 that will be used when there is no customer match found. You are able to change the customer on the order in e-automate as long as the order is in an open status.**
Ship To Customer
Ship To Customer
When the Shipping Site (1) is updated to a site that is NOT the main site, and the shipping site is mapped to a location in e-automate, the sync will use that location as the ship to customer on the sales order in e-automate. Additionally, if there is a Shipping contact (2) selected, the name of the contact will show in the Ship to Attention line.
Order Type
If there is only one Order Type that you want to use for all ConnectWise orders, you can enter this order type in Variable 7 on the alert. If you do not enter an order type in the ID966 variable, the order type set in the ID634 variables will be used. (Order type is a required field when creating an order in e-automate.)
Order Types can be found in e-automate in Tools - Lists and Codes - Sales order types:
If you would like to have multiple order types for the ConnectWise sales orders, you can do this by setting up a custom field in ConnectWise with a drop-down list of the order types. This custom field must have the name ZCJ_Order_Type, details on how to create that custom field are here.
Sales Reps
If you have a default sales rep set in variable 3, that is the sales rep that will be assigned to all orders. If that variable is blank, the sync will try to match the sales rep assigned to the order in ConnectWise to a sales rep in e-automate based on the sales rep mapping.
The sales rep mapping is done by entering the Member RecID from ConnectWise into a custom property in e-automate, details on creating the custom property are here.
The value to be entered in the custom property is the Member RecID from ConnectWise:
The linked sales rep must also have their employee record set as a sales rep:
If the sales rep is not mapped in e-automate, the sync will use the sales rep assigned to the customer record in e-automate. If there is no sales rep assigned to the customer, no sales rep will be added to the order.
PO Number
PO Number
The PO Number from the order in ConnectWise will sync to the PO field on the order in e-automate.
Branch
Branch
The branch on the order can be set to the same branch for all orders using variable 6, if that variable is set on ID966, that is the branch that will be used on all orders. Enter the branch number into the variable, which is found in e-automate by going to Accounting - Branches:
If no branch is specified on the variable, the branch will be set per the ID634 settings, either pulling from the branch assigned to the sales rep, or the branch assigned to the customer. (Variable Z on ID634).
Warehouse
Warehouse
The warehouse used on the order and the line items will default to the warehouse that you set in variable 4 on the alert. You also have the option to create a custom field in ConnectWise that will create a drop-down list of warehouse options, details on how to do that are here.
If neither the variable setting or the custom field show a warehouse, the logic follows this sequence to find the warehouse to use on the order:
1. The default warehouse for the branch used on the order.
2. If there is no value for the warehouse at this branch, the logic moves to the default warehouse for the customer's branch.
3. If there is no value for the warehouse for the customer's branch, the logic moves to the warehouse set for the main branch.
Order Status
EA Order Status
When the order is pushed into e-automate, the order will use the default order status you enter in variable 10. If you do not enter a value in variable 10, the order status will be set to the default entered on ID634.
Statuses in e-automate are found in Tools - Lists and codes - Sales/RMA order statuses:
If you would like the status of the order in e-automate to update on the order in ConnectWise, you'll need to create a new custom field called ZCJ_EAStatus. Details on how to create that custom field are here.
The sales order status here in e-automate will update this field:
The order in ConnectWise will have this custom field updated as the status in e-automate changes:
Parent Child Assoc
Parent / Child Line Item Association
The sync can assign parent/child relationships on the order in e-automate based on the sequence of the products in ConnectWise. On the order in ConnectWise, set the sequence of the products to mimic the parent/child relationship:
The line items will then sync to the order in e-automate with this same line item settings:
Serialized Items
Serialized Items
The sync can create serialized items if the corresponding item template for the product type has the Serialized option checked. If you want the sync to create serialized items we recommend creating a new product type in ConnectWise that is ONLY for serialized items. When the serialized box is checked on the item template, all items created will be created as serialized in e-automate.
The inventory, sales code, service code and tax as fields will be copied from the template to the new item created. Equipment code, Make, Model and Tracking configuration will all need to be set manually on the newly created item.
CW Billable Options
CW Billable Options
For each product added to the sales order in ConnectWise, there is an option to set the billable method to Billable, Do Not Bill or No Charge. Any products that are marked as either Do Not Bill or No Charge will not sync to the sales order in e-automate.
Ship Method
The default ship method that will be used on the sales orders will be the default setting on the customer:
If there is no ship method set on the customer record, the default standard shipping method set in ID634 will be used. If you would like to have different shipping methods to choose from, you can accomplish this by creating a custom field in ConnectWise. This field must be named ZCJ_ShipMethod, details on how to create that custom field are here.
Shipping methods can be found in e-automate by going to Tools - Lists and codes - Shipping methods:
CW allows for a check box on the line item to indicate that line is drop ship, e-automate only allows one ship method per order. If we find an order with a mixture of drop-ship and non-drop-ship items we will break it into two orders and add DropShip to the end of the sales order normal description. The shipping method on drop-ship orders will be the default set on ID634 for drop-ship orders.
Order Messages
Order Messages
Specific errors on the order will be listed on the messages tab, along with the description of sales order.
Attachments
Attachments
Attachments to the order in ConnectWise will be added as attachments to the e-automate sales order.
There will be up to a 15-minute delay between when the order is created and when the attachments are added to the sales order. ID966 pulls the attachments from ConnectWise, but cannot add the attachments in e-automate until the order is created by ID634. The next run of ID966 after the order has been created will add the attachment.
If you see that attachments are not getting added to the orders in e-automate, please send an email to help@ceojuice.com, this process sometimes runs into security permission errors and therefore doesn't add the attachments properly.
If you do not want the attachments to sync, please let us know and we can turn off this option.
* * *
Best Practices & Tips
Best Practices & Tips
Jump to: Suggested Product Types | Custom Properties | Custom Fields
Suggested Product Types
Suggested Product Types
Like our Agreement/contract sync, we use Product Types and item templates in e-automate to determine what GL codes the sales order in e-automate will post to. A Product in CW is an Item in e-automate. We suggest you use different Product Types for sales orders even though you may have a Product Type on an agreement that is similar.
We believe these 4 product types should work for most clients (Sales Orders only).
Hardware Sales
Software Sales
Materials Sales
Labor
Material sales would include supplies like cabling etc, consumed on a project.
Custom Properties
Custom Properties
To create a custom property in e-automate:
Go to Tools - Lists and Codes - Attributes and then click on new:
Attribute name: ZCJ_CW_EmpRecordID
Description: ConnectWise Sync Member ID
Data type: Numeric
Custom Fields
Custom Fields
There are several optional custom fields that you can setup in ConnectWise for the sales order sync:
1. Order type options - Must match order type names in e-automate exactly.
2. Warehouse options - Must match warehouse names in e-automate exactly.
4. Ship Method options - Must match shipping methods in e-automate exactly.
To set up a custom field in ConnectWise, go to Setup Tables - Custom Fields - Sales Order Overview, then click on the plus button to add a new field. The custom fields in ConnectWise MUST be created with the exact names listed below to work with the sync!
Order Type
Order Type Optional Drop-down: - ZCJ_Order_type
1. Field caption: ZCJ_Order_Type
2. Display on screen: check
3. Include on List View: check
4: Method of Entry: List (Drop-down)
5. Options: Order types from e-automate, must match exactly to e-automate.
Warehouse
Warehouse Options Drop-down - ZCJ_Warehouse
1. Field caption: ZCJ_Warehouse
2. Display on screen: check
3. Include on List View: check
4: Method of Entry: List (Drop-down)
5. Options: Warehouse numbers from e-automate, must match exactly to e-automate.
Order Status Updates
Order Status Updates - ZCJ_EAStatus
1. Field caption: ZCJ_EAStatus
2. Display on screen: check
3. Include on List View: check
4: Method of Entry: Entry Field
Ship Methods
Shipping Methods Options - ZCJ_ShipMethod
1. Field caption: ZCJ_ShipMethod
2. Display on screen: check
3. Include on List View: check
4: Method of Entry: List (Drop-down)
5. Options: Shipping methods from e-automate, must match exactly to e-automate.
* * *
Related Alerts
Related Alerts
ID634 - Sales Order Import Utility
ID747 - Connectwise Manage Customer Sync
ID964 - Connectwise Manage Agreement Sync
ID965 - Connectwise Manage Service Call Sync
* * *
0 Comments