MoneyWorks

Some time ago I had the need to find a pair of transactions that added or subtracted to a particular number. In fact when you are chasing down a problem in a set of accounts this is not an uncommon thing to want to do.

So I wrote a report to do it. The report would load every transaction amount into a table, and then test every possible combination of two transactions to see if they added or subtracted to the number in question, then print the matches.

Since the report was quite compute-intensive, it was a candidate for optimising by rewriting the loading and searching of the amounts in MWScript. The algorithm is exactly the same, but since MWScript is compiled, it can run the algorithm faster than the report interpreter. It turned out to be 7x faster.

Here's the original report:

The logic for this can be rewritten using an MWScript function in the report's custom controls:

on Exhaustive(sel, Look_For) public
	let seqs = CreateTable()
	let results = ""
	syslog("loading...")
	foreach t in transaction sel
		TableAccumulate(seqs, t.sequencenumber, t.gross)
	endfor
	syslog("searching...")
	foreach i in (1, TableGet(seqs))
		foreach J in (1, TableGet(seqs))
			if TableGet(seqs, i, 1)  + TableGet(seqs, j, 1)  = Look_For or TableGet(seqs, i, 1)  - TableGet(seqs, j, 1)  = Look_For
				let results = results + TableGet(seqs, i, 0) + "\t" + TableGet(seqs, j, 0) + "\n"
			endif
		endfor
	endfor
	return results
end

Here's the report containing both implementations (it's set to always use the faster implementation, but if you change the Faster control to a checkbox you can choose to run the old implementation).

If there is a highlighted selection of transactions, it will only search those.

Find Pairs.crep