Will Howard – Technical Consultancy – Sharing knowledge to empower the world

Coming soon – Report Scheduling out of Exo.

Please note, this is a problem that has been solved repeatedly, most notably by Cellophane

I’m not trying to replace Cellophane, but I’ve got slightly different needs, and I want to show the full how to on how to write a Cellophane-lite application, something which I’ve been doing with Powershell at a few places already.

This is an out of hours project, like pretty much everything else here, (Some of the API support information comes from in-hours billed work, so that’s more proactive than others).

Screenshot of work in progress — Teaching myself WinForms

So far I’ve gotten this far, I’ve written the Winform in Microsoft Visual Studio (Community), and it can capture the data I want on screen.

Continue reading “Coming soon – Report Scheduling out of Exo.”

Timetrak – 3CX – Batch File/Powershell script to open browser tabs anytime you answer a call.

TimeTrak is a service management software/website that ties into ERP (Accounting) packages and allows for easy coordination of staff/management of tasks. I wanted to have TimeTrak open the tab for any client I was talking to whenever I answered my phone. My work uses 3CX to manage our telephony, and I figured that meant I could trigger things. The below doesn’t use the 3CX API, which is paid, but instead the integration function which is free. Continue reading “Timetrak – 3CX – Batch File/Powershell script to open browser tabs anytime you answer a call.”

MYOB Exo Business – Faster Creditors Recalc script

The logic of the last two posts extends to Creditor Recalculations, which can be done fast using the attached script.

SQL Code for X_RECALC_CREDITORS

MYOB EXO – Stored Procedure to recalc GL (Fast)

The standard utilities for Exo are slow to recalculate GL Balances and movement. This is because:

They delete records line by line, which gets really slow for the GLMovements table in most databases.
They take forever to update the resulting data, because most Exo functions work on a line-by-line basis, rather than calculating as a set.

Based on this, we had a need to recalculate GL Balances faster than the standard functions. Behind the cut is a function that does this fast.

This is much faster, because it uses a TRUNCATE on the GLMovements table, and then repopulates using set based transforms. It can still take awhile for larger databases though.

Click here for the code to X_RECALC_GL

MYOB Exo – Useful SQL Server Scalar Functions

The following are a collection of User Defined Functions I’ve found variously on the Internet. I use these extensively now to do simple data transforms when pulling reports, such as converting all line breaks to pipe characters so that copy/paste into Excel works nicely.

Behind the cut I’ve got several functions, and they’re all wrapped in handy dynamic scripts to install them easily.

UDF_GetNumeric – which pulls all numbers from a string. (I use it for parsing account numbers from text strings)
UDF_LineCleaner – which removes characters that can’t be used in Windows Filenames from character strings, useful for folder/file manipulation.
UDF_StandardLineBreaks – Useful if you’re having problems with line breaks not always being line breaks. Hunts for and fixes all line breaks into being the standard windows format. (Useful if getting data from Macs or Linux machines)
UDF_LineBreaksToPipes – Useful for copy/pasting from SQL management studio into Excel or bad CSV formats.

SQL Code to add these functions behind the cut.

MYOB EXO API – Simple Test Script in Powershell

This will be primairily of use to Exo Consultants who need to verify what data an endpoint produces, or to Developers just getting started with the API who want to see what a very simple formula might look like.
It’s worth noting that like most long-time Exo consultants, I’m so comfortable with database calls that I never use the API myself to write interactions, preferring to write my own stored procedures and staging tables to handle inserting/updating data, so the below is about as complicated as I need to get.

Read Get started with MYOB Exo API behind here.ore

MYOB Exo API – Some errors explained.

This post will be a catalogue of errors I’ve had interacting with the MYOB Exo API, and how to resolve them.

Code: Unable to load ModuleSecurity for ‘appid=4400’
Appears: Error appears when doing any requests apart from discovery.
Meaning: The module ‘4400’ is unlicensed. 4400 is the API.
Resolution: Make sure the API License is loaded in Exo Config. If you don’t have one, contact your business partner.

Code: “An error has occurred.”
Appears: Can appear anywhere.
Meaning: Indicates either Business Logic or Database failure with the data sent through.
Resolution: Most commonly appears if trying to submit a description that is too long. Older Exo databases have a 40 character maximum for Description fields, which was increased in 2018.5 to 100 characters by default. Both database tables AND a hidden profile in Exo Config need to be updated to enable longer description lengths. Similar problems can appear with Addresses.
Check for Truncation errors as your first port of call. Otherwise, get the Business Partner involved and send them a copy of the packet.

Symptom: API unable to connect via LiveRelay service. (Especially if works via Local API connections.
Appears: On any API call via appropriate service.
Example Code: (.NET example)

System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IO
Exception: Authentication failed because the remote party has closed the transport stream.
   at System.Net.TlsStream.EndWrite(IAsyncResult asyncResult)
   at System.Net.ConnectStream.WriteHeadersCallback(IAsyncResult ar)
   --- End of inner exception stack trace ---
   at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.GetResponse(WebRequest request)
   at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.ProcessRecord()

Meaning: You’re attempting to access the LiveRelay service
Resolution: Force your code to use TLS 1.1 or above. For .NET/Powershell this can be done by:
Upgrading to Powershell 6.
Upgrading to .NET 4.6.1
Adding this line to your code:

 
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

Symptom: API Discovery endpoint works, but attempts to use any other endpoint return no information. Especially via LiveRelay.
Appears: Can appear anywhere.
Meaning: The user did not authenticate properly, and no data was returned.
Resolution: When experienced, the API user was using a valid API Token, and a validly hashed Username/Password combination, however the User had been removed from the staff table. (Test database had been overwritten and user hadn’t exist in live database.) Adding the user again and reissuing an ExoToken worked, so suspect this is the resolution generally for this kind of problem.