Automated email reminders for customers on Sage Line 50 / Sage Instant

The good thing is that, if you already have Excel, this will cost you nothing. The idea goes like this:

• In the Memo field in the Sage Customer Record, start a dedicated line with something like "#Service date:" and then enter, say, 0630 for the 30th of June each year.
• Run an Excel macro periodically to identify and automatically email customers where the Memo field indicates that the next service is due soon.

SMS messages are possible too, but you may need to pay for each message sent.

Please drop me a note if you are interested in the Excel file that will facilitate this functionality.

Import into Sage from Excel to create a multi-line sales invoice in the Invoicing module

The standard response from most is that if you want to create an invoice in the Invoicing module, then the built in import routines do not allow it, so you'd need a 3rd party import program. You can import the invoice as a transaction to the Audit Trail using the Audit Trail Excel import template though.

However, there is a way to create an invoice in the Invoicing module using standard Sage routines - just not using the Excel import templates supplied by Sage. Sage help files outline the process whereby sales invoices can be created in either Sage Line 50 or Sage Instant here. Whilst this process requires Sage 50 Accounts Professional to raise a Purchase Order, instead, we can create a "virtual" Sage 50 Accounts Professional Purchase Order using only Excel.

A summary of the process is given below. In the summary, Company B is the company wishing to create multi-line invoices in the Invoicing module.

• Company A (or someone in Company B) creates a virtual Purchase Order using Excel and sends it using Transaction email to Company B. Company B receives this as a Sales Order.
• The Sales Order details are matched and updated.
• Then Transaction Email automatically creates a Sales Invoice in Sage 50 Accounts / Sage Instant.
• Company B sends this Sales Invoice to Company A using Transaction Email. Company A receives this as a Purchase Invoice.

The folks at Onion Reporting Software have a free Excel template to create the "virtual" Purchase Order needed for this process.

OpenCart order notification doesn't identify the customer - Part 2

After the Part 1 post I realised that direct edits to the OpenCart source files were going to be problematic at upgrade time. I'd lose all my previous edits when the upgraded files were copied over the edited ones. I'd seen references to vQmod but had never fully understood what it was or how it worked. A little investigation revealed that it was an elegant solution to making modificatons to the site that would not be lost on upgrade. I decided to adopt vQmod as my method of preference for adjustments to my OpenCart site.

I installed vQmod to the root directory of my site in accordance with the simple instructions. I already knew the information I needed to get the job done (see the Part 1 post). The file is identified in blue below. The line in that file which is to be replaced is coloured red below. The replacement I want for that line is coloured green below. The rest of the code is as per the guidance on the vQmod site.

The only thing I wasn't clear on from the guidance was where to save the file I put the code below into. I decided to call the file add-email-to-order-notification.xml and I uploaded it to the vqmod/xml directory on my site. It seemed like a reasonable guess. It worked. 

I'd come to understand that vQmod doesn't change any files. It duplicates them in the VQmod cache which are served (if they exist) instead of the core files and any necessary changes are applied from the XML markup in VQmod

I went back to look at order.php to confirm. It still contains the line I'm replacing with the vQmod. So, when order.php gets replaced in my next upgrade, my site should continue to provide me with the customer's email. In effect, the vQmod self-documents my previous editing changes and executes them on the fly.

<?xml version="1.0" encoding="UTF-8"?>
<modification>
<id>Add customer email address to new order notification</id>
<version>1.0</version>
<vqmver>2.5.1</vqmver>
<author>Onion Reporting Software Ltd</author>
<file name="catalog/model/checkout/order.php">
<operation info="Add customer email address to new order notification">
<search position="replace"><![CDATA[$text = $language->get('text_new_received') . "\n\n";
]]></search>
<add><![CDATA[$text = 'You have received an order from customer ' . $order_info['email'] . "\n\n";
]]></add>
</operation>
</file>
</modification>

Run-time error '1004': Unable to set the Visible property of the PivotItem class

There are two circumstances in which this error will arise when issuing a [PivotItem].Visible = True command in Visual Basic:

1. The sort order of the PivotField containing the PivotItem is set to anything other than xlManual; and,
2. The ShowAllItems value of the PivotField containing the PivotItem is set to False and an attempt is made to set a PivotItem containing no data (i.e. [PivotItem].RecordCount = 0) to True.

To process [PivotItem].Visible = True safely you should issue the following commands first:

[PivotField].AutoSort xlManual, [PivotField].SourceName
[PivotField].ShowAllItems = True

Sage Line 50 / Instant month end reporting using Onion Reporting Software's Onion product

One of the issues users frequently ask about is how they can monitor any “back postings” made in Sage since their previous monthly reporting cycle.  For example, a business may have a reporting cycle that produces reports for a month end five working days after that month end.  Using Onion as the reporting option will produce month by month spend figures for each month in the year to date as at working day five of the new month.  If, on working day six of the new month, a late posting is made to the previous month, how is the user of the Onion reporting pack next month to be made aware of the prior month posting?

The [Onion info] sheet of each Onion workbook records the “Last transaction number” at the time of workbook creation.  This identifies the cut-off point for the reporting pack.  The next time an Onion workbook is created, the user can ask to have postings made after that cut-off point identified in the new Onion workbook. The user ticks a check box, provides the last transaction number from the previous reporting pack, and runs the report. On completion the user can set the NEW_POST filter on the [TB Pivot (YTD)] sheet to Y to see what months have been posted to since the last report. Double clicks on the month totals will show the transaction detail of what was posted.

Excel VBA range find method not working

Just beat my head off a brick wall for the best part of a day on this!

The Find method failed to locate the reference of a text value in a row because I had hidden the row. I was using LookIn:=xlValues. When I changed it to LookIn:=xlFormulas, it worked as expected. Who knew!

P.S. Excel 2003 - don't know about other versions.

Adding Debits and Credits in Excel without a helper column

Irritatingly, I keep coming across exports from accounting systems where the figures are always stated as absolute amounts with a separate column showing Dr or Cr to indicate the signage, Dr being + and Cr being -.

It isn't always convenient to add a helper column but a formula in the form of 
=SUMIF($A$1:$A$10,"Dr",$B$1:$B$10) -SUMIF($A$1:$A$10,"Cr",$B$1:$B$10) will subtract the sum of all the Cr values in column B from the sum of the Dr values in column B based on the Dr/Cr designations in column A.

I've noted that the range for SUMIF isn't necessarily a single column array. So, if you had Dr/Cr designators in columns A and C and amounts in columns B and D the formula could have the form =SUMIF($A$1:$C$10,"Dr",$B$1:$D$10) -SUMIF($A$1:$C$10,"Cr",$B$1:$D$10).