When should you step up to an ERP?

Not all the big fish in a pond use an ERP platform, but it can be an essential tool for SMEs wanting to scale.


IN BRIEF

The big attraction of ERPs is having a single database for all information; the drawback is that they are expensive and involve a learning curve for staff.

SMEs may find that accounting software and third-party applications can do the job they need, but not always.

A rising volume of inter-entity transactions is one sign that your organisation could benefit from an ERP.

When should you step up to an ERP? – Acuity Magazine

Practical signals that you need an ERP

Mindset aside, there are several practical indicators that an SME has outgrown its accounting software.

The first is a rising volume of inter-entity transactions. ERPs use a single database to handle multiple entities, whereas small business software is designed to manage the finances of a single entity.

A major advantage of an ERP’s single database is that it creates a common list of products, suppliers and customers shared by all entities. This can immediately solve the issue of managing stock on hand held by different entities or locations.

A single platform also makes it easier to see a customer’s full history in one place. An ERP will show how many products or services they have bought, their payment history, how often they open your marketing emails and any outstanding support tickets.

Taking a component approach to software

Sometimes it doesn’t suit a business to move to a single ERP. In trying to do everything, ERPs rarely do any one thing extremely well. As a result, the component approach to system building, often sold as “best of breed”, still exists in enterprise.

Sage, for example, sells Sage Intacct which handles complex, multi-entity accounting and relies on integrations with the Salesforce CRM and other specialised tools for other functions.

Financial management tools can capture data in multiple dimensions and then report on any combination of those dimensions. The tool can map account codes to a custom report for each funder and maintain it for the lifetime of the program.

This means you don’t have to export the information to Excel and manipulate it into the correct format each time you want to view a report. These dimensions include locations, departments, projects, grants and strategic objectives.

Financial management software also has the ability to blend financial data with non-financial data. This is extremely important for management, who typically want the ability to track outcomes.

Extract of When should you step up to an ERP? – Acuity Magazine – May 2021

Choosing an ERP – Acuity Magazine

ERPs are the central nervous system of a business. And in a time of AI and technical innovation, they can be a smart move.


IN BRIEF

ERPs run multiple departments within big businesses, from accounting to supply chain and more

ERPs have the option of different ownership models: on-premise or private cloud.

Choosing an ERP – Acuity Magazine

Paying for an ERP

An ERP is often compared to the central nervous system of a business and switching from one ERP to another is a project that every CFO dreads. Along with software costs, such a massive project inevitably impacts operations, productivity and revenue.

Today’s ERPs have the option of different ownership models. You can still buy an ERP licence to run on your own servers, a model often referred to as on-premise. Or you can run the same licence on servers in an outsourced data centre operated by an ERP consultant, called private cloud.

The biggest news in the past decade has been the rise of cloud-based ERPs, where the software runs on servers operated by the software company itself. This is called public cloud, software as a service (SaaS) or multi-tenanted software.

At first glance, the public cloud ERP is the most attractive. A company no longer needs to worry about the capital expense of buying expensive servers or paying for IT administrators and security experts.

Under the public cloud model, the ERP software company is responsible for upgrades, so all public cloud customers are always on the latest version.

The big dollars are not in the actual cost of the software or even the hardware or infrastructure. It is in the risk and the disruption and the cost of those upgrades.

John Leonard, Oracle

Don’t change the suite – add a component

Changing to a new ERP is a massive project. Instead, why not keep your old ERP and add separate tools for your business processes? (A little like adding new cushions to your lounge suite to support your back, rather than buying a whole new lounge.)

Technology constantly swings between recommending a fully featured suite and building your own stack of components. The argument for the suite is less complexity and fewer headaches; components, however, will give you newer features faster.

Salesforce, which started out selling its highly successful cloud-based customer relationship management (CRM) software, has now replaced the sales function in many ERPs. In fact, the term ERP today refers more to finance and inventory rather than customer relationship management or human capital management (an industry term for human resources).

The ERP industry is indeed experiencing a swing from suites to best of breed in separate components, says Gary Katzeff, Sage’s General Manager, ERP, Australia & New Zealand.

“There are absolutely pros and cons (to both approaches). But if a vendor can provide, say, 80% of your requirements out of the box in one solution, then you generally will get a better experience as a customer than if you have to pull together multiple solutions.”

ERP software companies are creating more modules designed for specific industries to hit that 80% threshold. For example, Sage’s cloud-based accounting and finance management software Intacct has enjoyed success in the not-for-profit sector by focusing on multidimensional reporting and faster consolidations.

Intacct has a grants module that tracks government grants and funds management. It also integrates with Salesforce to handle directed donations from individual donors.

If the weakness of suites is slower and lumpy innovation across modules, in components the problems are typically in the complexity and cost of integration. Here again, there is good news. Integrations are getting easier thanks to standardised application programming interfaces (APIs) on public cloud applications.

Extract of Choosing an ERP? – Acuity Magazine – Sept 2020

Invoice Entry screen showing error duplicate invoice

Stop Posting of Duplicate Invoices

How can you do this automatically in Sage 300?

How much time is wasted by your staff dealing with duplicate payments?

Managers get upset as their budgets are hit with costs that can hang around until funds are recovered. Your staff are embarrassed about having to recover funds from suppliers. Then there is the time taken by your staff in dealing with this issue.

Sage 300 prevent users from entering the same invoice number for the same supplier. However numbers may look the same but are different. For example 000123 is not the same as 123.

First, have all staff trained in entering invoice numbers exactly as they appear on the invoice, including leading zero’s, hyphens prefixes such as ‘IN’. This will allow Sage 300 to correctly identify matching invoice numbers.

Then Sage will alert your staff to already entered invoice. You can also have alerts for invoices with the same date and or the same amount.

If you want help implementing these controls don’t hesitate to contact us.

Sage CRM 7.2 : Shuffle that Workflow!

Workflows are an integral part of Sage CRM, and we all know how crucial they are to any business that relies on them to perform their daily processes.

In my experience as a consultant, there have been many times when users from client sites have contacted us to say that they have mistakenly progressed a workflow to an incorrect state and want to move it back to the previous state.

In today’s blog, I’ll be giving you step-by-step instructions on how you can ‘shuffle’ any workflow to the state your client user wants.

I’ll be taking an example of the Opportunity workflow and show how you can move it back from its current state to the previous one. We’ll assume a scenario where a lead is incorrectly set to “qualified” in the workflow; which means the workflow is in the ‘qualified state’, and we want to bring it back to the ‘lead state’.

So, let’s begin!

Step 1:

Let’s pick an opportunity in the Qualified state from a list of opportunities in Sage CRM. Now, when you hover your mouse on this opportunity, you will see a URL at the bottom of the screen that will tell you the Opportunity Id (Oppo_opportunityId – highlighted in green) for this particular opportunity. In this case, it is 22.

Step 2:

Having found the correct opportunity, it is time to look into the Opportunity table in your CRM database and find out its corresponding ‘workflow id’ (oppo_workflowid). In case you ever wondered what a ‘workflow instance’ was, this is it! You can find the workflow instance of an opportunity by executing the following SQL command.

SELECT  oppo_workflowid

FROM     Opportunity

WHERE  oppo_opportunityid = 22

The above SQL command returns the value of oppo_workflowid as 3165. With that information, we will now go to the WorkflowInstance table to get some more information about this workflow instance record in step 3.

Step 3:

There are two things that I always check in the WorkflowInstance table for a given workflow instance record

1. wkin_currentstateid, and

2. wkin_workflowid fields.

WkIn_currentstateid, is used to find out what current state the workflow under consideration is in, and WkIn_WorkflowId  helps me to figure out the right workflow, which in turn helps me to list down all the states for that workflow. You can find the wkin_currentstateid and wkin_workflowid by executing the following SQL command.

SELECT wkin_currentstateid, wkin_workflowid

FROM   WorkflowInstance

WHERE  WkIn_InstanceID = 3165

The above SQL command returns the values of wkin_currentstateid and wkin_workflowid as 11 and 2 respectively.

Step 4:

Now we will use the wkin_workflowid value, which is 2 in this case, from the above step, to list down all the states for this particular workflow from the WorkflowState table. You can execute the following SQL command for that.

SELECT *

FROM  WorkflowState

WHERE WkSt_WorkflowId = 2

Below is a snapshot of the result generated by the query above. As you can see, the table in the snapshot has got all the standard opportunity workflow states that comes with the vanilla Sage CRM install.

Step 5:

So as we know, this lead was incorrectly progressed to the qualified state, as you can see in the image below. We now have to bring it back to the lead state.

We can do so, by updating the current state field (wkin_currentstateid) for workflow instance 3165, which is 11 (Qualified state) at the moment, to 10 (Lead state). If you see in step 4, the state id (WkSt_StateId) for Lead is 10. Let’s write this in SQL

UPDATE WorkflowInstance

SET    WkIn_CurrentStateId = 10

WHERE  WkIn_InstanceID = 3165

Warning : It is extremely important to be certain of the workflow instance you will updating as a final step in the process, as updating the current state id of an incorrect workflow instance will progress another workflow created by someone else!

Now just right click on your browser and reload the frame, and you will see that this opportunity workflow is brought back one step to Lead state again as seen in the image below.

Similarly, you can ‘shuffle’ any workflow to any state you want by simply following the above 5 steps. I recommend you try this out on a test environment first to get a firm conceptual grasp of the steps above.

Let me know how you go. Questions, welcome!

Until next time…happy consulting!  🙂

Sage CRM 7.2 Client-Side API

Recently I was asked to hide the continue button on the ‘Case’ summary screen for one of our clients. As you can see in the image below, it is located on the top right corner of the screen.

'Continue' button on the top right corner of the screen.
Case summary screen with ‘Continue’ button

Since it was a client side customization, I was aware that it could be achieved by one of the many easy to use APIs released with the launch of Sage CRM 7.2. These new APIs can be accessed from http://help.sagecrm.com/js-api/classes/index.html

Sage CRM 7.2 client-side API
Sage CRM 7.2 client-side API

Coming back to our client’s requirement, I went to the above link, and found the ‘hideButton’ API to achieve the goal.

Having found the right API, following are the steps to plug it in Sage CRM 7.2

1.  Go to Administration -> Customisation -> Cases -> Screens

2. Select the correct screen you want the API to trigger for when it loads. Remember you can enable ‘inline customisation’ to find out the right screen.

3. Once you are sure of the screen name, select it from the list of screens in the Case entity (in my case it was ‘CaseWebPicker’). This will give you an access to the ‘Custom Content’ area where in you can type the following javascript code that uses the above API.

4. Code:

     <script>

                crm.ready(function()

                {

                                crm.hideButton(“Continue.gif”);

                })

   </script>

Click update and save the changes.

And voila! The continue button has disappeared!

Case summary screen without the 'Continue' button.
Case summary screen without the ‘Continue’ button.

Similarly, you can use many of the client side APIs with Sage CRM 7.2 to achieve your customisation requirements. The other benefit of using the new APIs is that they are browser independent, and you can use them across IE, Chrome or Firefox without any issues.

I would like to hear about you’ve leveraged Sage CRM 7.2 client side API. Feel free to drop in a line or two about your experience.

Happy Customising!

SQL Server: Copy database from recent to older versions

Recently, I needed to copy a database from SQL Server 2012 to SQL Server 2008 R2.  Unfortunately, database backups from one version of SQL Server cannot be read by a lower version.  To work around this, I had to use the “Generate Scripts” function to first create the structure and then populate the database.

Whilst none of this is particularly complex, I thought it might be useful to collate the steps in one document…

Step 1: Generate structure scripts

  1. In the SQL Server instance that contains the database to copy, right-click on the Database in the Object Explorer, select Tasks, then select Generate Scripts.
  2. In the Wizard, progress to Choose Objects and make sure that “Script entire databases and all database objects” is selected.

    Screenshot: Select "entire database" option
    Select the “entire database” option.
  3. Progress to the “Set Scripting Options” stage.  Click on “Advanced” and make sure that the “Type of data to script” option is set to “Schema only”.  Also set the “Script for Server Version” option to the other SQL Server’s version (I still ran into incompatibility problems with the generated script, however).

    Screenshot: Advanced options in Generate Scripts
    Advanced options in Generate Scripts
  4. Finish off the Wizard, copy the generated scripts to a location that the other SQL Server instance can access and run them in that Management Studio.  Here are the main problems and fixes I ran into during this part of the process:
    1. Error message that “CONTAINMENT is marked as incorrect syntax”:  Just comment out the “CONTAINMENT = NONE” line (source: http://stackoverflow.com/a/15410854)
    2. Error message that CREATE Database commands can’t be run in a multi-transaction statement:  Just run the create statement separately.
    3. Error when trying to alter user to add role:  This command is not supported in older versions; change those commands to use “sp_addrolemember” (e.g. EXEC sp_addrolemember ‘db_datareader’, ‘Administrator’) (source: http://technet.microsoft.com/en-us/library/ms187750.aspx).
  5. If that all goes well, you should now have the database skeleton ready.  The next step is to import the data.
  6. Back on the originating SQL Server, go to Generate Scripts again, but this time in the Advanced options, set “Type of data to script” to “Data only”.  Because this will probably require a lot of space, in the “Set Scripting Options” screen, set “Files to generate” to “Single file per object”, to split up all of the scripts by table.  Finish the Wizard.
  7. You should now have a bunch of script files, ready to be run.  Copy the containing directory to a location the older SQL Server can access.  Because I had 219 generated files, to save on running them manually, I used the following batch script (create and run it from the containing folder):
    for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
    pause

    (source: http://stackoverflow.com/a/6504317)

  8. The above script may take a while to run, but after that, you should be done!

 

How do I output a link to a CRM record, in an email?

Jeff Richards describes how to do it in this post:
community.sagecrm.com/…/embedded-links-in-workflow-emails.aspx

Basically, your Email template needs to have the following text:

#recordanchor# <Anchor text goes here> #recordanchorend#

If you’re manually constructing the link (e.g. via a script), you can use the same “SessionFind” URL structure e.g.:

/CRM/eware.dll/SessionFind?Act=200&CLk=T&Key0=1&Key1=300123

The above URL will redirect the user to the Company Summary page (Act=200) for the company (key0=1) with a comp_companyid of ‘300123’ (key1=300123).

NOTE: The user will always have to login when clicking these links (even if they already have an open session).

How do I set my account to login to a custom screen?

Sage CRM allows users to customise which screen should be displayed by default, when they login.

image2013-8-7 16-35-27

But what if you want to login to a custom screen (e.g. an ASP file)?  In that case, you need to:

  1. Open the following file for editing
    \CRM\WWWRoot\InteractiveDashboard\InteractiveDashboard.asp
  2. Put in a Javascript redirect for the page you would like to redirect to e.g.:
    <script type="text/javascript">
        var strURL = window.location.href;
        strURL = strURL.replace(/\/InteractiveDashboard\/InteractiveDashboard\.asp/, '/CustomPages/CA/Dashboard/index.asp');
        strURL = strURL.replace(/\?.*(SID=\d+).*/, '?' + "$1");
        window.location = strURL;
    </script>
  3. Because the Dashboard is hardcoded to display a set of tabs at the top of the iframe, you will also need to remove those – you could do this by hiding the tabs. For example, in ‘Dashboard/index.asp’, include the following:
    <script>
    // Hide the InteractiveDashboards tabs, that are included in the parent frameset.
    $("#landingpage_top_frame", parent.document).hide();
    </script>
  4. Now, any user with “Dashboard” set as their “Log Me In To” option should be redirected to your custom page.

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.
How the “Days in Stage” column appears in the Opportunity pipeline.

To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.

 

Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi 
		ON wi.Wkin_CurrentEntityId = 10 
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.
View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.
Parameters for a new oppo_daysinstage translation.

 

Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.
Adding the “Days in Stage” field as a new column.

When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!

My CRM buttons are no longer appearing in Outlook?

If the Sage CRM plugin for Outlook is working correctly, you should see  a “File e-mail” button in your Outlook toolbar.

Sage CRM plugin buttons in Outlook
Sage CRM plugin buttons in Outlook

If not, try the following steps to get it working:

Requirements

Check that your PC has the required libraries installed.  In the Add/Remove Programs window, you should see:

  • Visual Studio Tools for Office 2010 (VSTO)
  • Microsoft Office 2007 Primary Interop Assemblies
  • Microsoft .Net Framework 4 Client Profile

We’ve also encountered issues if the user account is setup as a roaming profile.  If so, try changing the account to a local profile.

Check Trust Centre

In the Outlook client, open the Trust Centre and check if the Sage CRM plugin is listed in the Add-ins section.  If it is, make sure that it is enabled and trusted by Outlook.

Re-install the plugin

You can find the installer for the plugin in the <Sage CRM Install Directory>\CRM\WWWRoot\Plugin directory (e.g. C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\Plugin).  Run the plugin appropriate for your setup (e.g. for Exchange integration, use CRMClientExSetup.exe; for just Outlook-client integration, use CRMClientSetup.exe).

Keep in mind that the Outlook user must have local administrator permissions on their PC, the first time that they use the plugin (e.g. in Windows 7, go to the Control Panel, select Users, click on Give other users access to this computer and then add the Outlook user account as an Administrator).

If this still does not work, try logging into the PC as Administrator and re-installing the plugin.

Cleaning up the registry

If the plugin is still not working, you might need to search the Windows registry to see if there are any old or corrupt entries that are confusing the plugin.  As always, make sure to backup your registry before making any changes.

Ideally, the plugin’s registry entries should be under HKEY_CURRENT_USER\SOFTWARE\Sage\Exchange Outlook Plugin.

Outlook itself will also keep registry entries for its plugins (e.g. HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Outlook\Addins\OutlookAddin2003).

Delete any CRM Outlook entries you find in these folders.

After that, just try searching the registry for any matches on ‘CRM’ and manually checking each.

Remove downloaded files

Check C:\Windows\Downloaded Program Files for any CRM Outlook related files (e.g. crmoutlookplugin1.dll).  Delete all of these files.

Restart the CRM server

Sometimes the plugin will be unable to connect to the CRM server (either during the install or after), due to corrupted metadata on the CRM server.  In these cases, refreshing the metadata on the CRM server (or even restarting the CRM server) will fix the issue.