You create purchase order from your sales order. You do it every time if you are in certain in industry. But why doesn’t accounting systems let you automate it?

We saw this behavior in many accounting application from – QuickBooks to Intacct and Microsoft Dynamics GP (aka GreatPlains).

We ran into this requirement with quite a few times and ended up using our DBSync application to build out these automated processes.

Here are some of the use cases
1. CRM application creates an opportunity
2. Opportunity flows into accounting as a Sales Order
3. Invoices can be created from these Sales Orders
4. Purchase Orders can be created using the Sales Order

To create Purchase Orders, you will need to make sure that all items have associated item vendors in it and in certain accounting applications, it should also need to be “Purchased” and not in stock.

In most of accounting system, you will have to read Sales Orders (SO) and translate to Purchase Orders (PO). Fortunately Microsoft Dynamics GP (GreatPlains) makes it easy for it when using with eConnect API. Microsoft Dynamics provides a SOP Transaction for creating PO from Sales Order (nice !!!). There seems to be a misconception in GP community that taSopToPopLink only links the two documents which is not true. It actually does create the PO for you.

The following eConnect XML request creates PO from a Sales Order (SOPNUMBE)

<eConnect xmlns:dt=”urn:schemas-microsoft-com:datatypes”>

Once you run it, you get back true or false (with error). Error is quite self descriptive for you to trouble shoot it.

You can find the records in POP10100 (header) and POP10110 (line item).

Hope to post the video of it working in due course. Hope this article helps.

Enjoy integrating…

We ran into a problem with upgrading to Microsoft Dynamics GP 2010 and did not find much documentation to solve it.

We were consistently getting error at

The following SQL statement produced an error:
CREATE VIEW GL10000CurrencyTranslationView AS select [GL10000Final].[OPENYEAR], [GL10000Final].[ACTINDX], [GL10000Final].[CRDTAMNT], [GL10000Final].[DEBITAMT], [GL10000Final].[ORCRDAMT], [GL10000Final].[ORDBTAMT], [GL10000Final].[TRXDATE], [GL10000Final].[DSCRIPTN], ...


After looking under the hood we ran the following 2 scripts to get over it -
Script 1

GO

SET ANSI_NULLS OFF
GO

SET ANSI_PADDING OFF
GO


Script 2

GO

SET ANSI_NULLS ON
GO

CREATE VIEW [dbo].[GL10000CurrencyTranslationView] AS select [GL10000Final].[OPENYEAR], [GL10000Final].[ACTINDX], [GL10000Final].[CRDTAMNT], [GL10000Final].[DEBITAMT], [GL10000Final].[ORCRDAMT], [GL10000Final].[ORDBTAMT], [GL10000Final].[TRXDATE], [GL10000Final].[DSCRIPTN], [GL10000Final].[REFRENCE], [GL10000Final].[CURNCYID], [GL10000Final].[Original_Exchange_Rate], [GL10000Final].[JRNENTRY], [GL10000Final].[TRXSORCE], [GL10000Final].[SOURCDOC], [GL10000Final].[ORDOCNUM], [GL10000Final].[ORTRXSRC], [GL10000Final].[ORMSTRID], [GL10000Final].[ORMSTRNM], [GL10000Final].[ORTRXTYP], [GL10000Final].[SERIES], [GL10000Final].[VOIDED], [GL10000Final].[Ledger_ID], [GL10000Final].[TranslationCurrencyID], [GL10000Final].[CurrencyTranslationType], [GL10000Final].[PERDENDT], [GL10000Final].[TranslationExchangeRate], case GL10000Final.CRDTAMNT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended([GL10000Final].[RTCLCMTD], 3, [GL10000Final].[TranslationExchangeRate], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[DECPLCUR], [GL10000Final].[CRDTAMNT]) end as TranslationCreditAmount, case GL10000Final.DEBITAMT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended([GL10000Final].[RTCLCMTD], 3, [GL10000Final].[TranslationExchangeRate], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[DECPLCUR], [GL10000Final].[DEBITAMT]) end as TranslationDebitAmount, [GL10000Final].[SequenceNumber], [GL10000Final].[PERIODID], [GL10000Final].[CURRNIDX], [GL10000Final].[DECPLCUR], [GL10000Final].[RATETPID], [GL10000Final].[EXGTBLID], [GL10000Final].[EXCHDATE], [GL10000Final].[TIME1], [GL10000Final].[RTCLCMTD], [GL10000Final].[DENXRATE], [GL10000Final].[MCTRXSTT], [GL10000Final].[Adjustment_Transaction] from (select distinct [GL_TRX_WORK].[OPENYEAR], [GL_TRX_WORK].[ACTINDX], [GL_TRX_WORK].[CRDTAMNT], [GL_TRX_WORK].[DEBITAMT], [GL_TRX_WORK].[ORCRDAMT], [GL_TRX_WORK].[ORDBTAMT], [GL_TRX_WORK].[TRXDATE], [GL_TRX_WORK].[DSCRIPTN], [GL_TRX_WORK].[REFRENCE], [GL_TRX_WORK].[CURNCYID], [GL_TRX_WORK].[XCHGRATE] as Original_Exchange_Rate, [GL_TRX_WORK].[JRNENTRY], [GL_TRX_WORK].[TRXSORCE], [GL_TRX_WORK].[SOURCDOC], [GL_TRX_WORK].[ORDOCNUM], [GL_TRX_WORK].[ORTRXSRC], [GL_TRX_WORK].[ORMSTRID], [GL_TRX_WORK].[ORMSTRNM], [GL_TRX_WORK].[ORTRXTYP], [GL_TRX_WORK].[SERIES], [GL_TRX_WORK].[VOIDED], [GL_TRX_WORK].[Ledger_ID], [GL_TRX_WORK].[TranslationCurrencyID], [GL_TRX_WORK].[CurrencyTranslationType], [GL_TRX_WORK].[PERDENDT], F.XCHGRATE as TranslationExchangeRate, [GL_TRX_WORK].[SQNCLINE] as SequenceNumber, [GL_TRX_WORK].[PERIODID], E.[CURRNIDX], (E.[DECPLCUR]-1) as DECPLCUR, [GL_TRX_WORK].[RATETPID], [GL_TRX_WORK].[EXGTBLID], F.[EXCHDATE], F.[TIME1], D.[RTCLCMTD], dbo.mcFuncGetDenExchRate(GL_TRX_WORK.TranslationCurrencyID,D.RTCLCMTD) as DENXRATE, [GL_TRX_WORK].[MCTRXSTT], [GL_TRX_WORK].[Adjustment_Transaction] from DYNAMICS..MC40200 E, DYNAMICS..MC40300 D cross apply (select e.YEAR1 as OPENYEAR,a.JRNENTRY,a.SOURCDOC,a.REFRENCE,f.DSCRIPTN,a.TRXDATE, a.TRXSORCE,f.ACTINDX,a.SERIES,f.ORTRXTYP,f.ORMSTRID,f.ORMSTRNM, f.ORDOCNUM,a.ORTRXSRC,a.SQNCLINE,a.CURNCYID,b.CURNCYID as TranslationCurrencyID, a.CURRNIDX,a.RATETPID,b.ExchangeTableID as EXGTBLID,a.XCHGRATE, a.EXCHDATE,a.TIME1,a.RTCLCMTD,dbo.glFuncGetPeriodID(a.TRXDATE,a.OPENYEAR,2) as PERIODID,f.CRDTAMNT,f.DEBITAMT,f.ORCRDAMT,f.ORDBTAMT, e.PERDENDT, dbo.mcFuncGetMCTrxState(b.CURNCYID) as MCTRXSTT,b.CurrencyTranslationType, a.VOIDED,a.Ledger_ID, a.Adjustment_Transaction, case b.CurrencyTranslationType when 1 then e.PERDENDT when 3 then a.TRXDATE end as ExchangeRateDate from GL10000 a, GL10001 f, (select c.ACTINDX,b.CURNCYID, ExchangeTableID= case CurrencyTranslationType when 1 then b.AverageExchangeTableID when 3 then b.HistoricalExchgTableID end, c.CurrencyTranslationType from MC00200 c,MC40600 b where c.CURNCYID='' and c.CurrencyTranslationType<>2) b, (select distinct b.PERIODID, a.YEAR1,a.FSTFSCDY,a.LSTFSCDY, b.PERIODDT,b.PERDENDT from SY40101 a, SY40100 b where a.YEAR1=b.YEAR1 and b.SERIES=2) e where a.JRNENTRY=f.JRNENTRY and f.ACTINDX=b.ACTINDX and a.PERIODID=e.PERIODID and e.YEAR1=e.YEAR1 and a.TRXDATE >=e.FSTFSCDY and a.TRXDATE <=e.LSTFSCDY) GL_TRX_WORK cross apply dbo.mcFuncGetExchangeRateTable(GL_TRX_WORK.ExchangeRateDate, GL_TRX_WORK.EXGTBLID, D.TRXDTDEF, D.DATELMTS,D.PRVDSLMT,D.Base_Exchange_Rate_On,GL_TRX_WORK.MCTRXSTT) F where GL_TRX_WORK.EXGTBLID = D.EXGTBLID and D.CURNCYID=E.CURNCYID) GL10000Final
GO


This got us past this hurdle.

Hope this helps you get started with Microsoft Dyanamics GP 2010.

As you grow into your business, you add applications. You start off with Quickbooks, then add eCommerce applications and more as you start to organize your business and life. Then you realize “Geez” I have all these data all across and I am sitting down copying data from one application to the other, reconciling payments and more. Every business we have spoken to have its own story…

While we are trying to address most of these issues, lately we have received a lot of interest in our Intacct Integration Connector. Intacct themselves provide Salesforce.com integration with a pre-built setup, works for most case and does not in others. But a bigger problem is integrating with existing in-house application like Shopping carts and others. If you look at the Intacct ecosystem, there are not many, actually none which are cost effective.

We had a similar experience with one of our prospects and we recorded our session to show you all how we can easily use DBSync to integrate with in-house databases. We have also used the same mechanism to integrate with other CRM’s like SugarCRM and vTiger. We hope you find this informative.

This is a two part YouTube video.

Part 1:

Part 2:

Please feel free to contact us if you have any questions. You can also register at www.mydbsync.com to get access to our Wiki for more examples.

Many of our customers are not use on how best to streamline business process between CRM like Salesforce.com or Microsoft CRM or eCommerce or shopping carts with accounting systems like Quickbooks, Intacct or Microsoft Dynamcs GP.

A “typical” business flow is like

Opportunity -> Estimate/Quotes  ->Sales Order -> Invoice ->Credit

Here is how they should think about it -

Do you carry inventory?

If Yes (manufacturing, retail etc)

Holding inventory has a all sort of new ounces to worry about like managing adequate Inventory levels, supply chain – remember – holding inventory is not a good idea as it locks up working capital. From a standpoint of online orders or CRM Opportunity integration, they should always use “Sales Order”. Here are some are benefits -

• Sales Order does not hit GL and usually is a “notification” for fulfillment to deliver
• As you ship or deliver goods, you can generate multiple Invoices against a Sales Order
• Sales Order will deduct from inventory but not necessarily commit shipped on Inventory. This gives you a better count of inventory on hand vs commited.

So the recommendation is

Opportunity -> Quotes -> Sales Order -> Invoice (on or more) -> Credits or RMA (Return Merchandise)

If No (Services companies, Professional Services, Consulting etc)

You deal with people most of the time and billing is usually a function of scope of work or time spent.  Normally you would have many contract terms on billing as mile stone based, retainers and others. So in your case you can have multiple setups of invoicing and would trigger at different times.

• If you receive retainers, you can apply towards an Asset account and as you invoice it, move it from the asset account to the income account
• You can use Estimate to hold the hours allocated
• Invoices (and PO or Bills on AP if you are sub contracting) can be generated from Time & Expense modules
• The same can be done if you invoice based on milestones. You can deduct hours or Unit of work against your Estimate towards invoicing

So the recommendation is

Opportunity -> Estimate (hold hours) -> Invoice (invoice on spent time on milestone and track against Estimate) -> Credits

These are some simple rule of thumb when designing your business process flow. Hope this helps our audience.

Do you know how your application is used by your Customers?
Can you predict if a prospect will make a purchase?
Can you identify if your Customer will renew your subscription?

These are some of the common challenges facing every organization. So what is Customer Intelligence?

Customer Intelligence (CI) is the process of gathering and analyzing information regarding customers; their details and their activities, in order to build deeper and more effective customer relationships and improve strategic decision making.

Customer Intelligence is a key component of effective Customer Relationship Management (CRM) such as salesforce.com or Microsoft CRM, and when effectively implemented it is a rich source of insight into the behavior and experience of a company’s customer base.

Customer Intelligence begins with reference data – basic key facts about the customer and their interaction with your company and / or applications. By mining this data, and placing it in context with wider information about competitors, conditions in the industry, and general trends, information can be obtained about customers’ existing and future needs, how they reach decisions, and predictions made about their future behavior.

This data is then supplemented with transactional data – reports of customer activity. This can be commercial information (for example purchase history from sales and order processing), interactions from service contacts over the phone, e-mail, web visits to tracking use of your application. A further subjective dimension can be added, in the form of customer satisfaction surveys or agent data.

This and in my coming series of articles we will address some of the ways to develop strategies and architect solutions and analytics to help you develop a better intelligence set.

Some of the ways to build your customer intelligence

• Tracking Application usage
• Tracking web site activity
• Tracking shopping cart activity and understanding lost baskets
• Integrating Accounting with CRM to track payment patterns and Account Receivables
• Social Media integration and mining
• Predicting Renewals for subscriptions

Developing a strategy for building a Customer Intelligence platform –

Building Customer Intelligence using DBSync

Any Customer Intelligence platform should have the following

• Define: What is that you want from the information and what you plan to do with it
• Model: Define the algorithm that would best define your measures. Measures are the input parameters that you need to have to effectively score and segment your customer. A way to think about it is
  y = f(x1,x2,x3)
  Many times while defining the model we introduce measures which could enhancement and improve your model, but would be difficult to capture given the existing technology and capabilities of the organization. As part of designing CI, efforts should be made to check feasibility of implementing the model.
• Capture: Capture information from your data sources. These could be either from transactional databases or your data warehouse. In general you would have the following setup –
  • Data extraction from applications and datasources into a data ware house
    Example: Extracting web activity from your web application into a data warehouse which could have online customer tracking cookie, IP, referring source, visiting pages, time of visit, length of visit.
  • Aggregating and Summarizing information in data warehouse
    Example: Summarizing data extracted from accounting to track payment patterns and frequency, Credit status or from CRM to look at number of opened cases in last six months.
  • Validate: Run your data captured and summarized to the model developed. Check if your model does provide adequate scoring and segmentation that is required to effectively make decision. A good practice is to segment your score into color codes to define levels of Customer Intelligence Level that could help you quickly say if for example a new Lead is a qualified prospect or an existing customer relationship is going sour.
  • Integrate with CRM: Now that you have valuable information with your model to represent your customer intelligence, you need to integrate and make it easily accessible to your Sales and Marketing team. The best way is to seamlessly integrate with your CRM application so that your marketing team can use your CRM customer or prospect database and Customer Intelligence data along with CRM inbuilt analytics to continue to track and nurture the relationship with your customer.

• • Value— clearly identify information of value.
  • Context— clearly identify the context in which the data was gathered or processed. For instance, an increase in umbrella sales may be due to an increase in local precipitation rather than a fashion trend.
  • Granularity of identity— clearly distinguish and associate between data instances. For example, information surrounding the attributes of customer A may not apply to customer B.
  • Action—The results of analytics should point to a course of action.

Use Case

At DBSync we have a comprehensive customer tracking system built in to track customer usage and satisfaction to build our Customer Intelligence.

We use salesforce.com as our CRM application. Amongst the various models that we track, one is on predicting customer renewals based on usage of our application.

Define: Track and monitor Customer use of DBSync to predict renewals and assist customers continue increase use of the application. Our goal is the have each customer at 7 or higher.

Model: Our model y = f(x1,x2,x3,x4) can be best described as

y = A value between 1 (low usage) to 10 (high usage)
x1 =  Records processed in last 1 week
x2 =  Records processed in last 1 months
x3 =  Records processed in last 6 months
x4 = Date of Customer Acquisition
f(…) = A mathematical weighted model to score the customer.

Capture: Our Extract, Transform and Load for executing this model is as follows –

1. We use DBSync application itself to extract data from our tracking database into our data warehours
2. Use DBSync to execute data warehouse processes to build the data mart for summarization and aggregation.
3. Once our data warehouse is ready we use DBSync to run our Model y =f(x1,..) and populate Salesforce.com customer records.

Integration with CRM: Once information is populated in Salesforce.com, we have reports and dashboards for customer usage analytics. These reports are scheduled to be emailed out every week to the Sales and Management team to track and assist our customers.

Articles and links that would be helpful

Learn More about DBSync

Tutorial on how to use DBSync with database

Register for Free Trial

Need to perform exact schema and data replication to your database? DBSync for Data Synchronizer is the right tool for you.

Why do you need it?

• To build a data warehouse or create complex reports using BI tools
• To perform integration with other inhouse applications like Customer Master databases, ERP or other databases
• To perform offline data scrubbing and migration before final migration to Salesforce.com.

How does it work?

DBSync is a simple application which understands data structures in Salesforce.com and your database (any JDBC complaint database like MySQL, SQLServer, Oracle, DB2 or PostGresSQL) and can auto-create both schema and data in your database.

DBSync provides 3 ways for driving data to and from a database -

1. Using a web interface to define your integration “Profiles” end points, and select the Salesforce.com objects to synch-up.
2. Batch interface – once you have setup your profiles, you can use pre-configured batch scripts (for Windows or Linux) to initiate data and schema transfer from Salesforce.com to database or data transfer from database to Salesforce.com.
3. Real-time integration from Salesforce.com to database – DBSync leverages Salesforce.com outbound messages to intercept and interpret inbound message and then route it to the target database.

The initial setup and install time is pretty quick. A person familiar with databases should be able to install and setup in less than an hours time.

More information can be found at DBSync Synchronizer page

Sales and Inventory forecasting is a limitation for QuickBooks … Lokad to the rescue…

DBSync for Lokad - Sales forcasting made easy

DBSync has partnered with Lokad to provide an amazing sales forecasting solution. In about 20 miniutes, you can extract years of sales data from your QuickBooks directly into Lokad’s Salescast applicaiton and get your forecast.

• Manage inventory by understanding your safety stock levels and trends
• Identify trends in sales, inventory and more
• Reduce cost by reducing capital stuck up in your inventory

Our solution now can un-lock valuable QuickBooks data and provide actionable business intelligence.

To learn more, check out our Lokad and QuickBooks integration offerings. Call us to see all the amazing things we can get out of your accounting data.

Read more at Lokad Blog to see how to setup and get started.

Integration with Salesforce.com Product Schedules is now available !!!

Lot of customers have been asking us for supporting Product Schedule for recurring invoicing and billing, and now we have the support for it.

Who would benefit from it

• Firms providing Software Subscriptions based products and services.
• Publication firms – magazine, online content, elearning etc
• Services companies providing utilities like waste management, telecom, insurance, day care and others
• Healthcare firms providing on-going services like weight management, home-health and others

How does it work

• Setup your product schedule as you would normally setup in Salesforce. Setup your dates and occurrences
• Setup up scheduler to run every day, week or month. The scheduler provided calculates the next invoice date and updates the next invoice date (custom) field in Opportunity. Use a workflow to update the Generate flag for next invoice.
• You are done!!! DBSync will pickup all invoices as the dates come due and will generate Invoice, Sales Order, Sales Receipt as per Generate Field in opportunity.
• This setup can easily be extended to address most of the business processes around recurring invoice payments.

Installation:

• Login to DBSync
• Click on Library
• Add the process library for Recurring template and use the installation document to install the application.
• Once you install the application, setup the scheduler provided in the installation to match your billing cycle
• As the scheduler runs in the interval assigned, it will mark the corresponding opportunity to generate one of the accounting documents like Invoice, Sales Receipt etc.
• The next time DBSync runs the invoicing process, it will pickup all the Opportunities and process it for invoicing as per setup for Invoice or Sales Receipts.

Please feel free to contact us at support@avankia.com or call 1-877-739-2818 to get more information.

The key problem one of the Radiology – healthcare providers was facing was visibility to their referral information. They were using Net.Orange, and was proving to be quite expensive.

To run their day to day operations, they were using Amicas a leading EMR/Radiology Information System, which managed patient scheduling, physician information and all details other than the billing information, which was managed by another system. While it contained all key data from referral standpoint, they were unable to understand and related referral activities to that of physician liaison and outreach activities.

We used Healthcare CRM: Physician Relationship and Referral Management module built on force.com and were able to quickly setup the sales and marketing end of it. The key still remained to extract information out of the EMR and scheduling system. We had couple of options

Options:

1. Leverage HL7 – HL7 is an event driven protocol and two transactions namely ADT (Admit – discharge) and SIU (Scheduling) was relevant in this case. The issue was that not elements were passed through with the transaction – like complete referring physician information or diagnosis codes and we were not able to extract historical data as it was needed for forecasting future referral.
2. Perform an export in flat-files and manually import it. While it sounded like was in-expensive way to do it, it was quite expensive in the long run. Also, we had seen a drop in usage as folks would forget to perform import.
3. Automate data extract and import of data using DBSync. We were able to tap into the SQL Server and construct views for each of the data sets required for our analysis. We then scheduled data extracts every 15 minutes with the built in scheduler and push to force.com.

Analytics:

While Force.com has built in reports, dashboards and workflows, it does lack some statistical calculations like variance and drop offs. We added real time calculations on a number of statistical calculations required for analysis using APEX – an on-demand programming language for force.com. These routines would run at either real-time to process referral statistics as it came through the integration or run every day at a scheduled interval.

The end result was an on-demand data warehousing and analytical tool to provide clear real-time reports and dashboards, along with workflows to automate notifications of referral drop offs by physicians or new physician referrals.

For more detail check out http://www.avankia.com/healthcarecrm_prm

There are an ever growing number of companies offering EDI + API that stirred up some thought:

Having worked with GXS, one of the leading EDI VAN services, I felt I should provide my readers with some background – EDI or Electronic Data Interchange is a B2B protocol used by major business to automate their supply chain. These standards pre-date the WebServices days and were designed to be compact messages designed to save bandwidth and are governed by standard committees (ANSI, EDIFACT or TRADACOM).  These  B2B documents represented an invoice, acceptance notification and many other pre-defined forms.

EDI offers major savings over large corporations like Walmart or JC Penney who wanted to streamline their businesses and normally have large partner networks.  Standardizing and integrating businesses between Hub (the Company) and Spokes (all the partner suppliers) is a win-win for everyone and ends up being a cost to Spokes to choose to do business with the main Hub.

The Spokes are smaller firms. They choose EDI because the Hub asked to do so. Will they internally adopt EDI?  Maybe / maybe not.  As most firms, internal applications are integrated or can be connected by building bridges between applications.  In today’s world, they will very likely invest in integration tools which connect directly to each other.  To integrate with the Hub, they would go with an EDI VAN service like GXS, buy a tool to map and transform their internal representation of Invoice or other documents and send it through to them as EDI, using a prebuilt transfer protocol like FTP/S, EDI*Express or others.

Now let’s look at an Integration provider building an offering in API+EDI. It would make sense to me if the Integration Provider -

• Had a trading partner hub so that it made sense for Spokes to join in – or if they don’t have an offering of a hub,
• Offered an integration platform for Spokes to transform their documents in EDI and push to the network – seamlessly.
• Sold to the Hub an offering to replace EDI VAN services, and move to become a front-end to them for receiving EDI.  In this case, the Integration Provider should have a good strategy to upgrade the connector software for all Spokes.

So what I am getting to is that a firm will have difficulty making headway by only providing a way to transport EDI over an API without providing a value proposition to either the Hub or the spoke.  In this case, perhaps I am missing something.

I feel a better strategy we be to provide a built-in integration with Accounting or ERP packages to EDI.  This will certainly address the major concerns for the Spokes and increase traffic to the EDI Trading Grid / Network or connect with the Hub directly.  This is why a solution like DBSync makes the solution complete, as it connects to the accounting systems, provides mapping capability, and transfers to Hub in the form they need.

Please do jump in with your views…