Using Manager on cloud on Safari version 9.1.2 on MacBook Pro laptop OS X El Capitan 10.11.6
I am bookkeeper to a garage.
One of the lines on the sales invoices often has M.O.T. as the description.
I have recently been including a tracking code on this line. I did this thinking it would help me obtain a report.
I need a monthly report (therefore need to have ability to give dates required) that gives:-
the name of the customer
the registration of the vehicle (this is the description that appears below the billing address)
the date invoiced
the line on the sales invoice which has M.O.T. as the description as it includes the MOT ref.
I have tried each of the reports that show on Manager but nothing seems to include all this info. Particularly the ability to choose dates.
Suggestions please.
Unfortunately, I think you’ll have to wait until Custom Reports arrive.
The problem as I see it, you are trying to get information from both the Balance Sheet side (top half of the Sales Invoice) and the Profit & Loss side (bottom half of the Sales Invoice) but then only an extract - being the MOT line.
A work around (?) could be to separate the Accounts Receivable Customers into two by allocating the MOT clients into their own Custom Control Account. This probably wont give you the report but would isolate the MOT transactions into a group. If work other then the MOT was completed, then that could be separately invoiced. Also all cash MOT would have to be Sales Invoiced.
@patchworkfields, here is another idea. I don’t know if it will work, because you haven’t shared what sort of information this MOT stuff is. I’m assuming MOT stands for Ministry of Transport and that there is some sort of administrative information or numbers that has to be provided on receipts. Plus you want to show vehicle registration.
The summary Description field on sales invoices will accept HTML code. So if you use the <br> tag as shown below:
It will show up as two distinct lines on the sales invoice like this:
More importantly for your purpose, it will show up in the Sales Invoices tab register like this:
You could use various search and sort functions to locate a group of invoices, or not, then export and manipulate in a spreadsheet. If the MOT line you’ve been using actually represents income, you could still have that line (which doesn’t show up on any report).
Thank you Brucanna - I may try separating the Accounts Receivable Customers into two going forward from today but unfortunately the report I need to produce now is the 12 months just gone. Separate invoicing shouldn’t be a problem and all cash MOT are already sales invoiced.
Thank you Tut. I very much like the idea regarding HTML code to give both registration and MOT number information but regret that I have absolutely no idea of how to go about doing it. I am very conversant with Excel so am happy to export info if I can find it in the first place. I will try searching and sorting in various ways. Unfortunately the report needed is for the last 12 months.
Hi @patchworkfields,
Have a look at the description field in @Tut’s example above.
The magic code is <br>
Just put <br> wherever you want the line break to appear… that’s it!
Thank you very much. Will give it a try.
@patchworkfields, my suggestion only works going forward, because you did not put the information in the Description field to begin with. If you want to use that technique to generate a report for the last 12 months, you will have to go back and edit that field on every sales invoice.
Thanks Tut. I am a very patient person and may well do that; mainly because I started this business’s accounts just a year ago.
The question, then, will be whether exporting the sales invoice register gives you enough information. In your opening post, you mentioned having the MOT information as a line item. Line item information won’t be in the export. But if the only reason you were doing this was to capture some form of administrative information, it should work out.
Of course, you’ll have vehicle registration and MOT info in the same cell of the spreadsheet. I don’t know if that will be a problem or not.
If you delimit the data with something, you could use macros/functions to split the data as necessary. If <br> exports to the data ok, you could use that.
Thanks Zark. Am trying the
at the moment. Will let you know how I get on.
Could you please explain - “delimit the data with something, you could use macros/functions to split the data as necessary”
@patchworkfields
A delimiter is a unique marker that indicates the end of a string of data.
A csv file is a comma delimited file that uses a comma to denote the end of one column and the beginning of the next.
In the example we are looking at, @tut proposed the description text could contain two lots of data with a <br> (html line break) to display the data on two lines.
I.e. Registration Information<br>MOT numbers information
On the report would be on two lines like
Registration Information
MOT numbers information
Now if the whole string is exported as is to excel, I suspect the description column would have the following data in it.
Registration Information<br>MOT numbers information
<br> is then the delimiter which is unique and will not appear anywhere in normal text, so it is safe to assume that what is on the left of <br> is the first bit of information and what is on the right side of <br> is the second bit of information.
Using the function here How to split text by space/comma/delimiter in Excel?, you can specify <br> as the delimiter and get your data split into two columns.
Col A
Registration Information
Col B
MOT numbers information
Thank you Tut and Zark - problem solved and have done some learning along the way. What I do now:- on the MOT line of the sales invoice I put in the
between the MOT details and the car type and reg.
This then appears as two lines with MOT details above and car type/reg below.
At the end of the month I go to “report”, “general ledger summary” and run the report for the date range required. I click on the blue “Sales” and that gives me a line by report which I export to Excel. By using data sort and then only keeping the lines with MOT
, and replace all “
” with nothing I have the required report.
By the way Tut I did go back and change the whole year’s MOT lines on all the invoices!
Have also been practicing with the other info that Zark directed me to which is very useful knowledge to have.
Thanks again for solving problem.