Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Sales Order Import with GL Distributions


Today we are privileged to have our first guest Tech Tuesday author, Janeece Moreland, Managing Consultant/Business Process Change Agent for Conexus SG, sharing her insights into Sales Order imports with GL Distributions. 

To learn more about Janeece and Conexus, please take a look at their bios at the end of the article.

The purpose of this example is to provide the technical user with the details of how to create a map with a SQL Select statement and group data appropriately on the detail map functions. 

The original map provides Distribution lines, EFT imports, Post Import SQL tasks and Rolling columns. We will be exploring only one portion of the map in this article.

Problem Statement: A user has provided you with an excel spreadsheet with Customer, item description, line amount, GL account numbers and distribution references to be imported as Invoices.

Issue: When you have one source line of data you will need an Accounts Receivable offset account and group the data for 1 invoice, multiple line items and multiple GL distributions.

Example: Our example today will create a Sales Invoice, Line Items, and distribution lines with distribution references.   The complication in the data is the distribution lines normally sum based on the account number on the item but we are supplying the account number instead.  In addition to the GL distribution, we want to provide the distribution reference which will be shown in the financial detail when the Sales Transaction is posted in the General Ledger. The Customer Number will be used to group the data on the Create Sales Transaction.

tt-sales-order-import-1

First, we will look at the ODBC connection.

The data is provided in an Excel file format but we want to control the fields that will be used.  The following selections were made:

Data Source: ODBC Connection.

Connection Type:  Custom Connection

Connection String:

Excel Workbook:

Key Fields: CustNbr

tt-sales-order-import-2

tt-sales-order-import-3
Select Workbook

tt-sales-order-import-4


Select the Drive+Path+Name of File

 tt-sales-order-import-5

Our next step will be to select the data from the Excel spreadsheet.

This will be entered in the data source.  An explanation of each field and its use is listed below:

The Statement accomplishes the following functions:

  • TrxType – It establishes the type of transaction. The assumption is that all these transactions are invoices.  Additional select statement functions can be added to create Returns if needed.
  • Customer Number – used for the SOP Header and the Payment
  • Invoice Date – Used for the SOP Header
  • Item Number – used for the SOP lines
  • Quantity – used for the SOP Line
  • Item Desc – used for SOP Line
  • Unit Price – used for the SOP Line
  • Document Amount – used for the SOP Header, Payment and Cash Distribution
  • EFT Date – used on the Payment map
  • Distribution Reference – this could have been concatenated from the Item and Item Desc but the user wanted to have the flexibility to use it for additional maps. If the user types over the GP field length of 30 characters, the select will truncate the text.
  • GL Account – used on the Distribution map
  • DistType – used on the Distribution map
  • Unit Price – used for sop line and distribution map
  • Credit and Debit Amounts are used on the distribution map
  • The UNION statement creates another line item that will be used for the distribution.

SELECT 3 AS TrxType, CustNbr,[Invoice Date],[Item Number],[Item Desc],[Quantity], [Unit Price] ,[DocumentAmount],[EFT Date],LEFT([Distribution Reference],30) AS DRef,

[GL ACCOUNT NUMBER] AS AccountString, 1 AS DistType,

[Unit Price] AS CreditAmount, 0 AS DebitAmount

FROM [Import$]

WHERE [DocumentAmount]> 0

UNION

SELECT 3 AS TrxType,

CustNbr,[Invoice Date],[Item Number],[Item Desc],[Quantity], [Unit Price] ,[DocumentAmount],[EFT Date],’EFT’ AS DRef,

‘10700-5101-00000-00’ AS AccountString,3 AS DistType,

 0 AS CreditAmount,[DocumentAmount] AS DebitAmount

FROM [Import$]

WHERE [DocumentAmount]> 0

 

Grouping Data

Grouping Data imports the data into the GP Sales Invoice by Customer Number based on the default set on the high level map.

MAP: Create Sales Transaction

Notice that the Group data is checked but the CustNbr is not checked.  The default is the Key field from the Data Source.

tt-sales-order-import-6

MAP: Add line item

Customer Number and Item Description are selected in this map. The item description was selected since the item number does not exist in the Inventory. The items are non-inventory items. The default is the Key field from the Data Source.

About the Author:

Janeece began her consulting career in 1987 working as the IT manager for the largest independent software distributor at the time in the US, Software Spectrum.  She then worked in Sales at International Business Machines (IBM).  From IBM she then moved to Platinum Software Corporation, now known as Epicor Software.  At Platinum Software, Ms. Moreland served as a member of the SWAT consulting team covering the United States working with difficult installations as a problem solver.    She joined Oracle in 1996 as a part of sales and consulting group focused on the Utilities Industry then moved to back into as an Oracle Sales Manager.  Now a managing partner in the Microsoft Business Applications consulting world she manages varying projects but focuses on integrations from varying operational systems into Accounting applications and Manufacturing implementations. Her broad Applications software experience includes: Financial Accounting, Financial Analysis, Project Accounting (Costing), Distribution and Manufacturing Planning systems.

To contact Janeece, please email jmoreland@marksmangroup.com.


About Conexus SG:

Conexus SG specializes in financial systems and process consulting.  A significant portion of their business revolves around the implementation, support, enhancement and upgrade of Microsoft ERP (accounting) software such as Dynamics GP (formerly Great Plains), Dynamics AX and Dynamics SL (formerly Solomon).  To learn more about Conexus SG, visit their website: www.conexussg.com.

4 Comments

  1. Jess Holmes on September 17, 2013 at 9:24 am

    A little hard to follow, but I'm sure if I were to employ a friend or colleague more technologically inclined, this would help my small business immensely. I recently started working a shipping software solutions company- it really is amazing how much potential a business that efficiently communicates with

  2. Abbey on September 17, 2013 at 10:50 am

    Hi Jess – Thank you for your feedback. I agree that communicating with your customer base is a very powerful thing. You'll find that on Tuesdays, all our blog articles are geared toward technical folks. Throughout the rest of the week however you will find articles that cover everything from announcements to sales info to events and more. If I can assist you at all, please email me at

  3. Sanola Jerry on September 18, 2013 at 6:05 am

    Well it is better to use an Microsoft Excel sheet to store accounting details as it is available easily and can be attached with your mail.

    Thanks
    Sanola Jerry

    Accounting Software

  4. Laurie Meyer on July 14, 2015 at 12:38 pm

    It would be great if you could also provide some insight into the importing of SOP invoices where the distributions are pulled from the Item. I appreciated your piece.
    Thanks,
    Laurie

Leave a Comment





RECENT POSTS


Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.