Familiarising with SQL Server environments / SQL from Crystal Reports ►

◄ Batch extracting invoice images from an Agresso system

2018-09-16 📌 A couple of IDEA regex functions and some simple code snippets

Tags 🏷 All 🏷 Tech 🏷 Audit

Another quick post mainly to refer people to.

I'll say now that these bits were all developed under CaseWare IDEA 8 and I'm a relatively infrequent user of IDEA myself, preferring SQL (with CLR functions if much text processing is involved). They seem fine on version 10 but no claims are made on best practice standards or elegance.

If memory serves the encoding of functions has to match your version of IDEA (ASCII or Unicode) but most people these days will have the latter. Certainly various functions posted on https://www.ideascripting.com/ (run by this chap who's a longtime user and trainer) seem to consistently use utf-16.

Paste each into an .ideafunc file (eg regex_mid.ideafunc and regex_substitute.ideafunc) and import them. You can do this by copying them into a "Custom Functions.ILB" folder, but note that the text file encoding must match what IDEA is expecting -- i.e. CR LF line endings and what Notepad++ identifies as UCS-2 LE BOM encoding. If in doubt, copy one of the .ideafunc files in the IDEA sample project and paste into it.

And a few simple functions I've used with IDEAScript.

The first simply throws a series of questions at the user to tidy up after they've run a script. You'd probably want to change the order of the actions; they're like this for historical org-specific reasons.

This one ensures that a file gets exported even if the target database has no records, to avoid confusion:

The VBScript FileExists is generally more reliable than alternatives:

And just a simple replace function for string handling:

Thanks also to https://mothereff.in/html-entities

💬 Comments are off, but you can use the mail form to contact or see the about page for social media links.