Sales Order import utility to facilitate 3rd party integrations and other CEO Juice alerts to create sales orders in e-automate.
Jump to: Overview | Samples | Variables | Table Structure | Setup and Testing | Alert Functionality | Best Practices & Tips | Related Alerts | Webinar | Table Schema
Overview
Overview
This alert was built to use a custom CEO Juice table populated via import or direct SQL feed from a 3rd party billing system and inject those imports into e-automate as sales orders. As this will require SQL expertise from your internal IT and/or 3rd party provider to write the required data into our custom table, you will need to coordinate with our team to make sure that table is populated accordingly.
ID634 works with several third-party systems, see notes here on current integrations.
The records imported are the line item details for the order(s) to be created, and our process will calculate the order total / tax / freight accordingly using the matching e-automate Item you created with the imported 'item'. That is a requirement, that the imported 'item' must match to an existing e-automate item (whether that be an expense code item or an actual inventory item). This match is required so that the tax is calculated accurately according to the tax code flags for the customer's tax authority.
The video below has an overview of the ID634 process along with a sample order pushed into e-automate. Use the chapter headers to skip to a specific section of the sales order.
Run Schedule: Every 10 minutes
Type of Output: Data Push/Email
* * *
Sample
Sample
Jump to: Sales Order Header | Items Tab | Billing\Shipping Tab | Messages\Remarks | Email Output
Sales Order Header
Sales Order Header
1. Customer - ImpCustomerNumber in the import table or the default customer if there is no match to the ImpCustomerNumber. If there is no default customer set, the main branch customer will be used.
2. Description - You can control the data that populates in this field based on Variable Hidden2 and Variable W.
3. Order Type - OrderType in the import table, if provided, otherwise the default order type is set in variable 1.
4. Sales rep - Sales rep linked to the SalesRepNumber in the import table, if provided, otherwise the default sales rep for the customer.
5. PO number - PONumber in the import table, if provided, otherwise will be blank.
6. Ordered by - If the proper information is in the import table, the contact on the imported order.
7. Branch - If a branch is specified in the SOBranchNumber field, that branch will be used. If nothing is in the import table, variable Z will determine where the branch is pulled from, either the customer record or the sales rep record.
8. Number - ImpSONumber in the import table, if provided. Otherwise, it will be the next number from e-automate.
9. Warehouse - WarehouseNumber from the import table, if provided. If no warehouse is provided, warehouse is determined by checking for the default warehouse set at the branch level.
10. Department - Department set in variable 2, if no department is provided in the import table.
11. Status - TargEAOrderStatus in import table, if provided. If not, default order status set in variable 4.
12. Total - The total should match the total price from the third-party integration.
Items Tab
Items Tab
Each item from the imported order will be listed on the items tab of the order. Items that do not exist in e-automate will be added to the order as the error item, description, quantity and price will all match the third-party integration.
1. Equipment Number - If the EquipmentNumber column of the import table is populated with a valid equipment number, that record will be attached to the order.
2. Contract Number - If the ContractNumber column of the import table is populated with a valid contract number, that record will be attached to the order.
3. Shipping contact - If the ShipToContactNumber column of the import table is populated with a valid contact number, that record will be attached to the order.
4. Parent line item - If parent/child linking is set on the order, the child items will show the parent line item in this field.
5. Rollup price - If the item is a child item, and the option to roll up the price is set in the import table, this option will be checked to roll up the price of the item to the parent.
6. Hide on packing list/picking list - This option is selected by default for all expense items, inventory items will not have this option selected.
Billing\Shipping Tab
Billing\Shipping Tab
1. Bill to - ImpBillToCustomerNumber from the import table. If no bill to customer is specified, the customer from the sales order header will be used.
2. Ship to - ImpShipToCustomerNumber from the import table. The attention line will be populated from the ShipToAttn column in the import table.
3. Ship method - ImpShipMethod from the import table, if not populated, the ship method from the customer record. If no shipping method is specified at the customer level, the ship method is pulled from the variable settings, variable 6 for standard orders and variable 7 for drop-ship orders.
4. Terms - If the TermsCode column of the import table is populated, that is the terms that will be used. If this column is blank, the terms from the customer record will be used.
5. Tax code - If the TaxCode column of the import field is populated, this is the tax code that will be used. If there is no tax code specified, the tax code is pulled from the customer record.
Messages\Remarks
Messages\Remarks
The Messages tab will be populated with the following information:
1. Anything in the Messages column of the import table.
2. If the Hidden2 variable is set to yes, the order description will be added to the message.
3. Anything in the OrderNotes column of the import table.
4. Any errors on the order.
The remarks tab is populated based on the Remarks column in the import table.
Email Output
Email Output
An email is generated when a new sales order is created.
* * *
Variables
Variables
See the video below for details on the variables (use chapters to skip to specific variables), or expand the section containing the variable in the documentation below.
Variables Y / X / 1: Integration, Order Break and Order Type Default
Variable Y: 3rd party integration used - You must specify one integration from the list. Each integration used will require a separate subscription. More details on the available integrations are here. **This is a required field for the integration to work.
Variable X: One order per - Options for order grouping are:
1 order per shipto location - Orders will be grouped by the ship to location.
1 order per host configuration - Orders will be grouped by the host configuration.
All lines on one order - No grouping will be used, all lines in the import table will create one order in e-automate.
**This is a required field for the integration to work.
Variable 1: Order Type Default - Indicate the default order type to be used when sales orders are created. **This is a required field for the integration to work.
Variables 4 / 6 / 7 / 9: Order Status, Ship Methods, Default Customer
Variable 4: Default Order Status - Enter the default status to be used on created orders. **This is a required field for the integration to work.
Variable 6: Default Ship Method - Enter the ship method code you wish to use on non-drop ship orders if the default ship method on the customer is not set. **This is a required field for the integration to work.
Variable 7: Default Drop Ship Ship Method - Enter the default drop-ship shipping method code to use if the imported ship method code = "Drop". **This is a required field for the integration to work.
Variable 9: Default Customer - By default the process will use the customer number supplied in the import table. If the customer number imported is invalid, we need a default customer to use for the sales order we create. If this variable is left blank, the main branch customer will be used on the order.
Variables 3 / 8 / 11: On Hold Codes
Variable 3: On Hold Code - Optional to specify an on-hold code to be used for all orders created with no errors. If left blank, orders with no errors will not be placed on hold.
Variable 8: On Hold Code for Errors - Enter the on-hold code to be used when there are errors on the order. **This is a required field for the integration to work.
Variable 11: In Process On Hold Code - Specify an on-hold code to be used while the order is being created by the sync. **This is a required field for the integration to work.
Variables 10 / 2 / Z: Error Item, Default Department, Branch Source
Variable 10: Error Item Number - Enter an item number to be used on the sales order when the sync can't find a matching item in e-automate to use. Example Item#: **ItemError. **This is a required field for the integration to work.
Variable 2: Department - Enter the department code to use on orders created by the sync.
Variable Z: Branch Source - Indicate whether the branch on created orders should pull from the customer record or the sales rep record in e-automate.
Variables 12 / 5: Group renumber and Renumber Invoices:
Variable 12: Group Renumber - Set the variable to yes if you are importing multiple orders that are related (for example, one order per host equipment for a multi-equipment order) and wish to renumber all the sales orders to be sequential with the same main sales order number.
Variable 5: Renumber Invoices - Enter yes to renumber the e-automate sales invoice to match the invoice number from the external system.
Variables W / Hidden2 / Hidden1: Sales Messages settings, Line item detail remarks
Variable W: Sales Messages - If you would like to have the process update the sales order description and messages tab, indicate here the concatenation of the possible combinations to incorporate into the message:
PriceBookName - if your integration has special pricing options enabled on quotes and they are populating our column PriceBookName. (PriceBookName column of import table)
SourceID - ID# from the host system (SourceID column of import table)
DealNumber - The deal number from the third party. (DealNumber column of import table)
Imported Description - Description column from the import table.
DoNotWriteMessage - no description added
Variable Hidden2: Update Sales Order Message - if you want the process to add the options selected in Variable W to the sales order message and description, select Yes. When set to No, the process will not add a sales order message or a description on the sales order.
Variable Hidden1: Update Sales Order Detail Remarks - If you want the sales order detail remarks from the import table added to the line item detail on the sales order select Yes. When set to No, the process will not add these line item detail remarks.
Variables Hidden3 / Hidden4: Jobs and Creating Ordered by Contact
Variable Hidden3: Create Jobs - By default the process does not create jobs, if you are using jobs with one of the two supported use cases, choose that case from the drop-down to have the process create jobs.
Processor Assigned: use a job on an order to indicate the sales order processor who will be handling the processing of the order/deal through e-automate.
Deal Costing: link all the transactions associated to getting an equipment 'deal' processed.
Variable Hidden4: Create Ordered by Contact - If you would like the process to create the order by contact select Yes in this variable. To create the contact, the integration must populate the OrderedByEmail, OrderedByFirstName, and OrderedByLastName in the import table.
* * *
Import Table
Import Table
For each integration used with ID634, the data is populated into our import table and the sales order is created based on this information.
Below is a list of the column names in the import table that are either required to be populated or are optional to be populated in the table. The full list of columns in the import table is in the spreadsheet attached at the bottom of this post. **Please review the spreadsheet for the full list of columns, this is not an exhaustive list of all columns in the table**
Expand for Import Table Columns
ImpSONumber - This is the Sales Order number from your 3rd party system. We will use this number as the SO Number created. If the data pushed is not coming from our Quote Conversion process, then a value in SourceID or ImpSONumber must be provided.
ImpInvNumber - This is the Invoice number from your 3rd party system. After the imported sales orders are Fulfilled in e-automate, the process will identify the created invoice and change the invoice number to this imported number if variable 5 is set to Yes.
Description - Sales Order Description, optional.
ImpCustomerNumber - Must correspond to your e-automate customer number. REQUIRED
ImpShipToCustomerNumber - Optional, if none is provided, then we will use ImpCustomerNumber
SODate - Will use today/date of import unless you tell us otherwise, optional.
SalesRepNumber - The e-Automate employee agent number of the sales rep associated with the order. if blank will default to the rep assigned to the customer.
SOBranchNumber - The GLBranch to be associated with the sales order. This branch will override the branch settings in Variable Z.
PONumber - If provided, will be pushed as the PO number on the sales order.
Item - This must correspond to an item number in e-automate, or have an item template to be used to create the item.
DetailDesc - This is the description you wish to be written to the sales order detail lines (it will override the EA item description)
Qty - Item quantity. REQUIRED
UnitPrice - Specify or we will default to price on Item
DetailID - Used to determine the sales order detailid. If you leave this column null, the process will auto assign a unique detailid within the ImpSONumber/SourceID grouping.
EquipmentNumber - If there is a particular Equipment record to be associated to the line item, this would be populated in the import with the appropriate equipment number from e-automate.
ContractNumber - If there is a particular contract record to be associated to the line item, this would be populated in the import with the appropriate contract number from e-automate.
Remarks - Remarks to be added to the sales order. Remarks are added to the Remarks tab of the order and each line items remarks are added to the line item remarks.
Notes - Notes to be added to the sales order. Notes are added on the messages tab of the order and to the line item notes.
WarehouseNumber - If the item is set up as a stocking item, then the e-automate warehouse number must be included in the import records.
BinNumber - If the item is setup as a stocking item, then the associated e-automate bin should be included in the import records (unless the client has the proper default sales fulfillment bin setup for the item in e-automate).
OutCost - If there is a salesrep outcost, include that amount in the import.
ParentID - The line item sales order detail Parent line item id. Only populated if importing sales order details that will be using the parent/child line item hierarchy.
ImpBillToCustomerNumber - The e-automate customer number for the Bill To record to be associated to the sales order.
SourceID - If the imported data is not pushing in a value into ImpSONumber column (which would be used as the e-automate order number when created), then you will have to provide a unique SourceID that would represent the common value across rows that are to be grouped into a single sales order. Either SourceID or ImpSONumber must be provided (if data is not coming in from converted quotes).
ShipToAttn - This will override any defaults from the Customer record if provided to populate SOOrders.
OnHoldCode - REQUIRED if we need to put On Hold, otherwise will default to Variable3.
OrderType - REQUIRED or will default to Variable1.
ContactNumber - The Eautomate contact number to be used as the 'ordered by' on the sales order header.
ReqDate - If value is provided, this will be used as the order required by date. Optional.
ShipMethod - If value is provided, it will be applied as the shipmethod on the order. Typical use would be for drop ship quotes converted.
DetailParentID - This would be the recursive DetailID to associate detail line items to a Parent line item. If provided, then it's expected the import is also providing the DetailID on the import, and then the process will update the SOOrderDetails DetailParentID to create the desired parent/child relationship on the order.
ConfigID - From SalesForce.com, this is the Host device in the configuration, required from SFAD integrations.
ConfigShipID - From salesforce.com, this is the individual items… Note: the ParentID column coming in from AgentDealer is the numeric portion of ConfigShipID and it points back to the 'Host' configshipID
IsDevice - to indicate if the item is a machine/accessory, the process will only link devices to the Host Item from the quote
ShipToContactNumber - The e-automate contact number (CMContacts.ContactNumber) to be used as the 'Shipping Contact' on the sales order line items (SOOrderDetails.ShippingContactID)
TargEAOrderStatus - The order status to be used on the orders created. This value must match a valid EA OrderStatus. If this column is not populated the default order status from variable 4 will be used.
ImpSource - This column is meant to identify the 'source' of the import (ie: SFAD, Quotes, etc). This column is populated by the specific ID634 subscription importing data to the table. Any feed to the import table that is not from one of our alerts should use 'Client' as the source.
DeliveryRemarks - Additional delivery instructions are to be passed through to the EA Sales Order remarks. Please note, the remarks column is limited to 1024 characters.
JobNumber - E-automate JOB# to associate to transactions
HiddenLine - if a line item on an order should be 'hidden' when printing.
RollUpPrice - indicate if an item price is to be 'rolled' up into the Parent line item.
ConfigParentShipID - If you are unable to populate the DetailID and ParentDetailID (the DetailID that accessory lines in a configuration belong to) then we can discuss how to use the ConfigShipID / ConfigParenShipID to accomplish the same relationship of host/accessory.
LineNumber - This is not required but it basically is what is used to display line item relationships in the EA UI (ie 1.1, 1.2, 1.3, etc to visually reference parent/child (host/accy) relationships. We will populate this column if left null.
TaxCode - The EA TaxCode to be applied to the order. Leave null and EA will use the Shipto customer's tax code.
TermsCode - The EA Terms code to be applied to the sales order ( leave null to let EA determine default terms).
Message - Message to be added to the sales order 'message' block (we take the max value based upon a grouping by the SOURCEID ) column.
Freight - if freight is to be added to the sales order..(note: we take the max value of this freight column across all rows with common SOURCEID).
DealNumber - if you have a visible deal# from your quote you need to populate to EA pass that value here.
OrderedByEmail - If the email address listed in the column matches the email on a contact record in EA, then that contact will be set as the ordered by contact on the order.
OrderedByFirstName - Contact first name.
OrderedByLastName - Contact last name.
OrderdByPhone - Contact phone number.
RowsExpected - This is the total number of rows to be included on the order. This is required and used to verify that all items are added to the sales order. (Sync will not process orders till the number of lines in the table for an order match the rows expected value.) REQUIRED
* * *
Setup and Testing
Setup and Testing
See the video below for an overview of the setup and testing needed for ID634, use the chapter headings to jump to each specific section.
Jump to: On Hold Codes | Shipping Methods | Default Customer | Error Item | Import Table Setup | Testing
On Hold Codes
On Hold Codes
There are two required on-hold codes that need to be created in e-automate and one optional on-hold code:
1. In-Progress On-Hold Code: Used to indicate that the order is in the process of being created by ID634. Any orders with this on-hold code should not be opened or edited in any way.
2. Error On-Hold Code: Applied to any orders created by ID634 that have an error. These orders will need to be reviewed and the order released from hold once ready to be processed.
3. Review On-Hold Code: This is an optional on-hold code to be set on any order created by ID634 that has no errors. These orders would be reviewed and the on-hold released once the order is ready to be processed.
Full details on how to create on-hold codes in e-automate can be found here.
Shipping Methods
Shipping Methods
There are two shipping methods to be set on the ID634 variables, both are required:
1. Default shipping method: This is the shipping method to be used if there is not a shipping method specified in the import table and the customer on the order does not have a default shipping method set.
2. Drop-Ship shipping method: This is the shipping method to be used if the order in the import table is specified as 'Drop-Ship'.
Full details on how to create shipping methods in e-automate can be found here.
Default Customer
Default Customer
The orders created by ID634 will first use the imported customer number as the customer record, if that customer number is not valid, then there is an option to specify the default customer to be used. If no default customer is specified, then the process will use the main branch customer as the customer on the branch when the imported customer number is invalid.
Full details on how the customer is set on the created orders can be found here.
Error Item
Error Item
Every subscription of ID634 must specify an item to be used when the imported item number does not exist in e-automate or the item in e-automate does not have the proper coding to be added to a sales order. If this item is not specified or is invalid, the process will not be able to create any sales orders.
Full details on how to creat the error item can be found here.
Import Table Setup
Import Table Setup
Any integrations that have a specific CEO Juice ID number that is facilitating the push of data to the import table will not need any specific import table setup. If you are using the 'Client' integration to directly populate the CEO Juice import table, you will need to review the table schema and verify that your process is populating all required columns and not populating any columns that should not be populated.
Testing
Testing
Items to check during the testing phase:
- Customer is populating correctly.
- Items added with proper descriptions and pricing.
- Default settings for order type, status, ship method and customer are all set correctly.
- Review any errors on orders to make corrections to customers or items.
- Fulfill order to verify proper coding to GL accounts.
* * *
Alert Functionality
Alert Functionality
Data from third-party integrations is pushed to the CEO Juice background table and used to create sales orders in e-automate.
Jump to: Third-Party Integrations | ImpSource | Customers | Order Number | Description | Order Type | Sales Reps | PO Number | Ordered By | Creating Contacts | Branch | Order Date | Warehouse| Department | Order Status | Items | Contract on Item | Equipment on Item | Hide Item | Error Item | Rows Expected | Line Item Remarks | Line Item Notes | Bill To | Ship To | Ship To Contact | Ship Methods | Freight | Default GL Accounts | Terms | Tax Code | Jobs | On Hold Codes | Outcost | Messages Tab | Remarks | Parent/Child Line Items | Rollup Pricing | Order Grouping | Order Renumber | Invoice Renumber | Error Details | Clones
Third-Party Integrations
Third-Party Integrations
For each integration used, a separate subscription to ID634 will be needed, with the third-party integration set in variable Y. For details on how to create a cloned subscription, see here. Currently, ID634 can integrate with these third parties:
CRM's:
-
Salesforce Agent Dealer: Clients can use an ETL utility called SKYVVA for integration from SalesForce. SKYVVA allows you to map the data from SalesForce to the import table's columns.
- For this integration, Agent Dealer will need their own SQL user created that has datareader / datawriter rights to the CEOJuice database. Your internal IT will create this user and provide the credentials to Agent Dealer.
- D365: Microsoft Dynamics 365.
PSA's:
- ConnectWise: Subscribe to ID966 to push sales orders from ConnectWise to e-automate.
- Autotask: Subscribe to ID915 to push sales orders from Autotask to e-automate.
DCA's:
Available/In Beta:
- FM Audit: Subscribe to ID29, this process is currently in Beta.
- Supplies Network: Subscribe to ID30, this process is currently in Beta.
- Sharp MICAS: Subscribe to ID32, this process is currently in Beta.
- KPAX: Subscribe to ID76 to push orders from KPAX.
- Printanista: Subscribe to ID192, this process is currently in Beta.
- HP SDS / MPS Monitor: Subscribe to ID311 to push orders from MPS Monitor.
- EKM: Subscribe to ID448, this process is currently in Beta.
Pending development:
- Kyocera Fleet Services: Subscribe to ID240, this process is currently pending development.
- NextGen, Subscribe to ID365, this process is currently pending development.
- Xerox: Subscribe to ID763, this process is currently pending development.
E-Commerce:
- B2B Toolbox: Subscribe to ID126.
Other:
- EA Quote: Subscribe to ID742.
-
Client: If you are manually pushing to the import table from another source, set the integration to Client.
- For this integration, your internal IT will need to create a SQL user that has datareader / datawriter rights to the CEOJuice database.
As new integrations are added, this list will be updated. (Current as of 2/17/2025)
ImpSource
ImpSource
The ImpSource column in the import table indicates the source that the order came from. You can filter by this in the e-view to only see orders of a certain source.
The source is populated by the integration pushing data to the table.
Customers
Customers
The customer number imported into the ImpCustomerNumber column of the import table must match a customer number in e-automate. If there is a match to a customer in e-automate, that customer will be used on the order.
You can choose to set a default customer to use on orders where there is no match to an e-automate customer number. This default customer is set in variable 9. If there is no match between the ImpCustomerNumber to a customer number in e-automate, and Variable 9 is blank, the sync will create the sales order using the main branch customer:
Order Number
Order Number
If an order number is specified in the ImpSONumber column of the table, that number will be used as the sales order number in e-automate.
If no order number is specified, the next sales order number from e-automate will be used.
Description
Sales Order Description
The sales order description is determined by the values selected in Variable W and Hidden2. To show the data in the description, Hidden2 needs to be set to Yes. Then the options selected in Variable W will be shown. The options are:
1. PriceBookName - The data from the PriceBookName of the import table. The price book name will be wrapped in brackets in the description.
2. SourceID - The data from the SourceID column in the import table. If the SourceID column in the import table is NULL, the SourceID will not populate in the description. The SourceID will be prepended with SourceID: in the description.
3. DealNumber - The data from the DealNumber column of the import table. If the DealNumber column in the import table is NULL, the DealNumber will not populate in the description. The deal number will be prepended with [ DealNumber: in the description.
4. The imported description - the value in the description column of the import table.
5. DoNotWriteMessage - No data is populated in the description field.
**If multiple options are selected in Variable W, the data will be populated in the description in the order the options are noted above.**
Order Type
Order Type
If the imported order has a type set in the import table (ImpOrderType), that is the type that will be used on the order. If the import table does not have a value for order type, the default set in Variable 1 will be used.
Order Types can be found in e-automate in Tools - Lists and Codes - Sales order types:
Sales Reps
Sales Reps
To specify the sales rep to use on the created sales order in e-automate, you'll need to populate the SalesRepNumber column in the import table with the employee number from e-automate:
If no sales rep is mapped in the import table, then the sales rep is pulled from the customer record:
PO Number
PO Number
If there is a PO number populated on the PONumber field of the import table, that value will be posted to the PO number field on the order in e-automate.
Ordered By Contact
Ordered By Contact
There are two ways to populate the Ordered by field on the sales order:
1. Push the contact's email address to the OrderedbyEmail column of the import table:
If the email address is linked to a contact record for the customer in e-automate, the contact will be set in the ordered by field on the sales order. The email address must match exactly to an existing contact record in e-automate to be added.
2. Push the contact number to the ContactNumber column of the import table to specify the ordered by contact:
The contact number is shown here:
If the contact number is a valid contact number in e-automate, that contact will be set as the ordered by on the sales order.
Creating Contacts
Creating Contacts
If you would like the process to create a contact record to be added as the Ordered by contact when there is no match in the OrderedByEmail column of the import table, you will first need to set variable Hidden4 to Yes. Then you will need to populate the import table columns OrderedByEmail, OrderedByFirstName and OrderedByLastName. These columns must be populated to create the contact record.
The newly created contact will have the Contact number in the import table populated with the newly created contact and this contact will be added to the order as the Ordered by contact:
Branch
Branch
If the SOBranchNumber column of the import table is populated, that value will be used as the branch on the order. If no branch is specified in the import table, the process will use the option selected in Variable Z, either the branch from the sales rep or the branch from the customer.
Import Table:
Customer Branch:
Sales Rep Branch:
Order Date
Order Date
The order date will match the SODate (1) in the import table, within the e-view you can also see the time that the order was imported (2) into the table:
*If the ReqDate column of the import table is populated with a date, that will push to the Req. Date on the order when created.*
Warehouse
Warehouse
If there is a warehouse specified in the WarehouseNumber column of the import table, that is the warehouse that will be used by the process:
Warehouse number in e-automate:
The logic follows this sequence to find the warehouse to use on the order if nothing is specified in the import table:
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.
Department
Department
The department will be assigned based on this hierarchy:
1. A department is specified in the import table, the name of the column in the import table is GLDeptNumber.
2. A default department is set in Variable 2.
If neither of the above applies, the department field will be blank on the order.
Order Status
Order Status
If there is an order status specified in the import table, that is the status that will be used when the order is created. The column for the status is TargEAOrderStatus in the import table. If there is no order status specified in the import table, the order status specified in variable four will be used on all orders created by ID634:
In the e-view there is a column for order status, this status reflects the current status of the order in e-automate and will be updated as the order is updated.
Sales order statuses are found in e-automate under Tools - Lists and Codes - Sales/RMA order statuses:
Items
Items
The items added to the sales order will match the items from the import table:
Contract on Item
Contract on Item
If a contract is populated in the Contract number column of the import table, that contract will be attached to the item when it's added to the sales order.
Equipment on Item
Equipment on Item
If an equipment record is populated in the equipment number column of the import table, that equipment will be attached to the item when it's added to the sales order.
Hide Item
Hide Items
By default, when an expense item is added to the sales order, the 'Hide on packing list' and 'Hide on picking list' options are checked. The options are not checked when inventory items are added to the order.
To force a line item to be hidden, the hidden column in the import table should be 1. To force an item to not have the hidden line checked, the hidden column should be 0. If the value is set to 1, the ImpRollUpPrice needs to also be set to 1. If the hidden line column is blank, the default behavior noted above will be followed.
Error Item
Error Item
An error item is required for the sync to work, it's used whenever the sync is unable to create a new item or find a matching item in e-automate. If the error item does not exist in e-automate, the sync will not be able to create any orders.
The error item needs to have either an inventory or expense code and a sales code:
The description, quantity, and pricing on the order will match the imported details:
You'll need to cancel the error item from the order and add the proper item once it has been created.
Rows Expected
Rows Expected
Orders are created in e-automate only once all items have been imported into the table. This is facilitated by the column Rows Expected, which should have the total number of items for the order. If the integration populating the import table is a CEO Juice alert, the rows expected column will be automatically populated with the proper number of rows. For SFAD orders, the rows expected column can be mapped in SKYVAA, or if the column is not populated, the ID634 process will populate the value with the number of items in the table when ID634 runs. If you are pushing orders to the import table through a direct SQL connection you must populate this column to process the orders.
When the sync goes to create an order it will check that the total number of items to add to the order match the value in the rows expected column. If this number does not match, the order will not be created.
LIne Item Remarks
Line Item Remarks
If the ImpDeliveryRemarks (1) column of the import table is populated, those remarks will show in the line item remarks if the Hidden1 variable is set to Yes. The delivery remarks will be within the square brackets. Anything in the remarks (2) column of the import table will also show in the line item remarks.
If the variable is set to No, these remarks will not show at the line item detail level.
Line Item Notes
Line Item Notes
If Hidden1 is set to yes, anything in the notes column of the import table will post to the line item notes:
Order Bill To
Order Bill To
The bill to on the order will match the bill to customer that is set in the ImpBillToCustomerNumber in the import table. If no bill to customer is specified, the bill to on the order will be set to the customer on the sales order header.
Order Ship To
Order Ship To
The order ship to will match the ImpShipToCustomerNumber in the import table. If no ship to customer is specified in the import table, the ship to will be set to the customer on the sales order header.
If the ShipToAttn column in the import table is populated, that will show in the attn line of the ship to address:
Ship To Contact
Ship To Contact
If the ShipToContactNumber column of the import table is populated with a contact number that exists in e-automate, the line item ship to contact will be populated on the order:
The contact number on the contact record in e-automate is shown below:
Ship Methods
Ship Methods
If there is a shipping method set in the import table, that is the shipping method that will be used on the order:
If there's no shipping method specified in the import table, the shipping method set on the customer will be used:
If there is no ship method set on the customer record, the default standard shipping method set in variable 6 will be used. If the order is set as a drop ship order, the sync will use the default drop-ship method specified in variable 7.
Shipping methods are found in e-automate in Lists and Codes - Shipping Methods:
Freight
Freight
If the Freight column of the import table is populated, that amount will populate in the Freight amount on the sales order:
**The sum of all freight amounts for the items on the same ImpSONumber or SourceID will be pushed to the Freight amount.**
GL Accounts
GL Accounts
The accounts and departments set on the order will all be pulled from the defaults set in options in e-automate.
Terms
Terms
The terms on the sales order will be set following this hierarchy:
1. The TermsCode column of the import table.
2. Terms set on the customer record, first the bill to customer, then the main customer.
3. If there are no terms set on any of the above, no terms will show on the order.
Tax Code
Tax Code
If there is a tax code specified in the TaxCode column of the import table, that code will be used on the order.
If there is no tax code in the import table, the tax code will be pulled from the Ship to customer record in e-automate.
Jobs
Jobs
Starting with the 20240326 version there is an option to create jobs in e-automate for SFAD orders imported. This functionality ONLY works with orders that are imported from SFAD, no other import types support job creation. With other import sources you are able to add the job to the sales order if the JobNumber column of the import table is populated with a valid job name.
There are currently two use cases for creating jobs:
Processor Assigned: The job number in e-automate is used to indicate the sales order processor that will be handling the processing of the order.
Job/Deal Costing: The job number in e-automate is used to link all associated transactions to getting the equipment deal processed. This use case is for future development.
Set the Hidden3 variable to the use case that applies or leave as Not using jobs to not create jobs.
When Hidden3 is set to Processor, the EAUserID column of the import table will need to be populated with a valid e-automate user login.
The order insert will attempt to match the value in the EAUserID column to a job number in e-automate:
If a match is found, that job will be applied to the sales order. If no match is found the process will create a new job for the e-automate user.
On Hold Codes
On Hold Codes
There are two required on hold codes for the order import and one optional on hold code:
In progress on hold code: While the import is creating the order, this hold code will be applied to the order. This indicates that the order should not be edited as it's still in the process of being created. This is specified in variable 11.
Error on hold code: This hold code indicates that there was an error on the order that needs to be resolved. This hold code is set in variable 8.
On hold code: There is an option to place all orders that do not have any errors on hold. This is done by specifying a hold code in variable 3.
In e-automate, on hold codes are found in Lists and Codes - On Hold Codes
Outcost
Outcost
The outcost on the order will be the sum of the data in the OutCost column of the import table.
Sales Messages
Sales Messages
If you do not want the messages tab of the order populated, you can set the Hidden2 variable to No. If Hidden2 is set to Yes, the following columns from the import table will populate:
1. The message column.
2. The description of the sales order (determined by the options selected in Variable W).
3. The notes column.
4. Details on any errors on the order, if any.
To push each line of data to a new line on the message tab, end the message column with one pipe (this is my message for this order | ) and wrap the notes column in pipes (|order notes|).
Remarks
Remarks
The data in the remarks column of the import table will push to the Remarks tab of the order:
The data in the remarks column will also push to the remarks for each line item on the sales order if variable Hidden1 is set to Yes. If Hidden1 is set to No, the remarks will only push to the Remarks tab of the order.
Parent Child Assoc
Parent / Child Line Item Association
There are two ways to designate a parent/child relationship among the items on an order:
1. Line Number and ParentID: Populate the Line Number column in the import table to mirror what the line numbers should be on the sales order and populate the ParentID column with the parent linking. The 'parent' item will have NULL for the ParentID (shows as a blank in the e-view), the items that are 'children' to that parent will all have the ParentID populated to match the line number of the parent.
2. ConfigShipID and ConfigParentShipID: If you are unable to populate the line number and parent ID fields, you can use the configShipID and ConfigParentShipID fields to accomplish the linking. Please reach out to help@ceojuice.com if you need to use this linking method.
Rollup Pricing
Rollup Pricing
If you are using the parent/child relationship for items on the sales order, you can specify that the child items price rolls up into the parent price. This is done by pushing a 1 to the RollUpPrice column of the import table:
Order Grouping
Order Grouping
All orders are first grouped by either the ImpSONumber or the SourceID, depending on which value is populated in the import table. (One of these columns is required to have a value.)
The second level of grouping depends on the option set in variable X:
1. Group by Ship to location: the orders are grouped by the value in the ImpShipToCustomer column.
2. Group by Host configuration: the orders are grouped by the value in the ConfigID column.
3. All lines on one order is selected, no grouping is done at the second level.
Order Renumbering
Order Renumbering
If the option to group the sales orders either by ship to or host machine is selected, you also have the option to renumber the sales orders so that there is one main order and each subsequent order created has a -001, -002, etc added. To turn on this option, set variable 12 to yes.
Invoice Renumber
Invoice Renumber
If the ImpInvoiceNumber column in the import table is populated, and variable 5 is set to Yes, the process will renumber the sales invoice to match the value populated in ImpInvoiceNumber. This column must be populated at the time of order creation, then once the sales order has been fulfilled in e-automate, the sales invoice will be renumbered to match the table value.
Error Details
Error Details
The sales order insert is designed to have very few reasons why an order would not import at all, most errors on the order will not stop the order from being created and will be detailed on the Messages tab.
You can also view the errors on an order in the e-view:
The most common error is that the item doesn't exist in e-automate or can't be created from the template. If the process can not find a matching item or can't create an item based on the template, the error item will be added to the order. The description, quantity, and price will all match the item that should have been added to the order.
When there is an error, the order will be placed on hold with the error on-hold code set in variable 8.
Clones
Clones
Each third-party integration will need a separate subscription for ID634, after the first subscription is created additional subscriptions are created by clicking on the + sign below ID634:
On the new subscription created, set the specific integration to be used in Variable Y. This clone of ID634 will not push sales orders into e-automate until moved to live mode by CEO Juice.
* * *
Best Practices & Tips
Best Practices & Tips
Jump To: Sales Quotes | Custom eView
Sales Quotes
Sales Quotes
Juice no longer supports pushing Deals in as sales quotes. The best practice is to have the salesperson determine when a deal is finalized in the CRM, then have the sales admin push the deal into eAutomate with a status of "On Hold - Needs Review". Not only are Quotes not relevant but they don't have all the fields needed.
Custom e-View
As part of the installation of the sales order import, we will install a custom eView showing the details of the imported orders. You'll find this eView in the System section of eViews. The included columns are listed below, they are also included on the schema spreadsheet attached at the bottom of this post.
Expand for Details on e-view Columns
[SourceID] - This is the value from the 3rd part importing, typically this is the overall "DEAL" identifier.
[ImpSource] - Indicates the 'source' of the data for this row.
[ImpOrderNumber] - If the 3rd party is pushing an order# to use this is the value they pushed. The process will use this as the EA Order# (if the imported number doesn't already exist)
SO Number - If the order was successfully created in e-automate, the order number.
[SFADDealNumber] - The SFAD deal number.
[DealNumber] - The column in the import table for the deal number.
[ImpPriceBook] - The column in the import table for the price book name.
[ImpCustomerNumber] - The customer number from the import table.
[OrderCustomer] - This is the EA Customer name matched to the imported value in the ImpCustomerNumber column.
[ImpOrderDescription] - Imported value to use as the sales order description (the process will also pre-pend the imported description with "SourceID:" and the value from the SourceID column if it's provided. This is to help users visually match & sort orders by the visible DealID value imported.
[ImpOrderType] - The order type from the import table.
[ImpSalesRepNumber] - The imported value that should match to the EA EmployeeNumber for the SalesRep to assign to the sales order
Sales rep - If the Imported value in ImpSalesRepNumber matched successfully to an EA employee number, the EA employee name will display here.
[ImpPONumber] - The PO number from the import table.
[OrderedbyEmail] - The email from the contact on the order, if mapped in the import table.
[OrderedByFirstName] - The first name of the imported ordered by contact, if mapped in the import table.
[OrderedByLastName] - The last name of the imported ordered by contact, if mapped in the import table.
Contact number - The contact number from the import table, if mapped.
[ImpBranchNumber] - The Eautomate BranchNumber to associate to the sales order. If this value is not imported then the process will use either the Branch assigned to the Customer record, or the SalesReps' branch based upon your settings in the ID634 subscription for VariableZ "BranchSource".
[DateImported] - This is the date time stamp of when this row was inserted into our import table from the 3rd party source.
[SODate] - The date of the order in e-automate.
WarehouseNumber - The warehouse number from the import table.
[ImpDeptNumberr] - The Eautomate GL department number to be associated with the sales order line items (requires version 20.x of EA or higher!)
Order status - The current order status of the EA sales order.
Item - This is the EA Item# matched to the ImpItem value. Blank values indicate the imported item# cannot be found in EA.
[ParentID] - The line item sales order detail Parent line item id…if importing sales order details that will be using the parent/child line item hierarchy.
[SODetailID] - If the row was processed into an EA order successfully, this is the EA SOOrderDetails DetailID.
LineNumber - The line number of the order.
[ImpItem] - The Item# imported from the 3rd party source.
Item description - The EA item description (if ImpItem matched to an EA Item#)
[ImpItemDescription] - The line item description imported from the 3rd party source.
[ImpQty] - The Qty imported to use for the order line item
[ImpUnitPrice] - Unit price imported to use as the unit price on the sales order line item
[ImpOutCost] - OutCost value from 3rd party to use on the imported line item details (will override the Eautomate default outcost value on the item)
[ImpContractNumber] - The contract number from the import table.
[ImpEquipmentNumber] - The equipment number from the import table.
[ImpIsDevice] - This column was inserted to help identify 'equipment/accy' items from misc non-accy items. 1 means the item on this row is a machine/device, 0 means it's not
[ConfigID] - This is the configuration master ID for imported quote details and is used to 'group' the parent-child relationships. All rows with the same ConfigID are part of the same configuration grouping
[ImpRollUpPrice] - If this 'row's price should be rolled up into the Parent line item on the order details , then the value imported should be "1". If the item should not rollup, the value should be "0"
[ImpHideLine] - If this line should be 'hidden' when the EA order prints, this value imported s/b "1". If not to be hidden, value s/b "0". (this is the checkbox on the EA SalesOrder line item edit screen "Hide on sales order/invoice". If this value is "1" (hide), then the ImpRollUpPrice column must be "1" as well.
[RowsExpected] - The total number of line items on the order.
[ImpBillToCustomer] - The bill to customer from the import table.
[OrderBillTo] - This is the EA Customer name matched to the imported value in the ImpBillToCustomerNumber column.
[ImpShipToCustomer] - The ship to customer from the import table
[OrderShipTo] - This is the EA Customer name matched to the imported value in the ImpShipToCustomerNumber column.
ShiptToAttn - The ship to attention contact from the import table.
[ShipToContactNumber] - The ship to contact number, must match a valid contact number from e-automate.
Ship Method - The shipping method from the import table.
Freight - The freight amount from the import table.
[TermsCode] - The terms code from the import table.
Tax Code - The tax code from the import table.
[ImpOrderRemarks] - Imported values to insert into the sales order remarks.
[ImpDeliveryRemarks] - The delivery remarks from the import table.
[OrderNotes] - The notes from the import table.
[OrderMessage] - The information in the messages column of the import table.
[DataErrors] - If there were any errors on this row (customer number not found, bill to not found, ship to not found, item not found, rep not found) will contain the value(s) that were not matched in EA at the time this row was processed
[ImpJobNumber] - The EAJobNumber to associated with this sales order. The JobNumber must exist in EA before the deal is pushed down if you intend to use Job#'s.
[EAQuoteNumber] - If this row came from our process to convert EA Quotes to sales orders, this column is the EAQuote# it came from
[ImpQuoteNumber] - This is the imported value of the EAQuote# to convert
]ImpQuoteID] - This is the QuoteID from SOQuotes for our quote conversion imports
]ImpQuoteDetailID] - This is the DetailID from SOQuoteDetailss for our quote conversion imports
* * *
Related Alerts
Related Alerts
ID29 - FM Audit
ID30 - Supplies Network
ID32 - Sharp MICAS
ID76 - KPAX
ID126 - B2B Toolbox
ID192 - Printanista
ID742 - EA Quote
ID915 - Autotask
ID966 - ConnectWise
* * *
0 Comments