Familiarising with SQL Server environments / SQL from Crystal Reports ►

◄ Batch extracting invoice images from an Agresso system

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.

<?xml version="1.0" encoding="utf-16"?>
<Function xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.caseware-idea.com/">
    <Author>Stuart.Denyer</Author>
    <DateModified>2012-12-20</DateModified>
    <FunctionName>REGEX_MID</FunctionName>
    <Help>Regular expressions enable pattern matching of data. This custom function uses VBScript regex syntax, returns all matches within the input string and is case sensitive. #REGEX_MID("123 QWERTY 456 qwerty", "[A-Za-z]+") may be helpful if you've ever wondered why a function called @JustLetters returns output that includes non-alphabetic characters, and gives 'QWERTYqwerty'. Reading up on regular expressions is highly recommended as they can do far more than can adequately be explained inside this box.</Help>
    <OutputType>Character</OutputType>
    <FunctionBody>Option Explicit
Function REGEX_MID(input_string As String,regex_pattern As String) As String
' Code adapted from http://www.tmehta.com/regexp/
Dim out As String
Dim i As Long
Dim matchCount As Integer
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = regex_pattern
RE.IgnoreCase = false
RE.Global = True
Set allMatches = RE.Execute(input_string)
matchCount = allMatches.Count
If matchCount >= 1 Then
For i = 0 To allMatches.Count - 1
out = out + allMatches(i).Value
Next i
REGEX_MID = out
Else
REGEX_MID = ""
End If
End Function
</FunctionBody>
    <Parameters>
        <Parameter>
            <Type>Character</Type>
            <Name>input_string</Name>
            <Help>The string to be parsed using REGEX_MID.</Help>
        </Parameter>
        <Parameter>
            <Type>Character</Type>
            <Name>regex_pattern</Name>
            <Help>A regular expression or range, such as [A-Z] or [0-9]. Ranges are frequently used with modifers such as '*' (0 or more matches) or '+' (1 or more matches) and you can follow those with ? to force non-greedy matching. Reserved special characters must be escaped using \ where necessary.</Help>
        </Parameter>
    </Parameters>
</Function>

<?xml version="1.0" encoding="utf-16"?>
<Function xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.caseware-idea.com/">
    <Author>Stuart.Denyer</Author>
    <DateModified>2012-12-20</DateModified>
    <FunctionName>REGEX_SUBSTITUTE</FunctionName>
    <Help>Regular expressions enable pattern matching of data. This custom function uses VBScript syntax and therefore supports back-referencing. For example, #REGEX_SUBSTITUTE("John Smith", "(.*?) (.*)", "$2, $1") will return 'Smith, John'. Regex are particularly useful to cope with situations such as address data that's been merged into one field, and reading up on them is highly recommended as they can do far more than can adequately be explained inside this box.</Help>
    <OutputType>Character</OutputType>
    <FunctionBody>Option Explicit
Function REGEX_SUBSTITUTE(input_string As String,regex_pattern As String,replace_pattern As String) As String
' Code adapted from http://www.tmehta.com/regexp/
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = regex_pattern
RE.Global = True
REGEX_SUBSTITUTE = RE.Replace(input_string, replace_pattern)
End Function
</FunctionBody>
    <Parameters>
        <Parameter>
            <Type>Character</Type>
            <Name>input_string</Name>
            <Help>The string to be parsed using REGEX_SUBSTITUTE.</Help>
        </Parameter>
        <Parameter>
            <Type>Character</Type>
            <Name>regex_pattern</Name>
            <Help>A regular expression or range, such as ([A-Za-z])([0-9]+). Ranges are frequently used with modifers such as '*' (0 or more matches) or '+' (1 or more matches) and you can follow those with ? to force non-greedy matching. Reserved special characters must be escaped using \ where necessary.</Help>
        </Parameter>
        <Parameter>
            <Type>Character</Type>
            <Name>replace_pattern</Name>
            <Help>Text to replace with, which can include back-references using $[num] syntax such as $1, $4, etc.</Help>
        </Parameter>
    </Parameters>
</Function>

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.

Function Housekeeping()
    CloseAll = MsgBox("Finished analysis. Close all open databases now to tidy up the work area?", MB_YESNO, "Payroll Analysis")
    If CloseAll = IDYES Then
        Client.CloseAll
    End If
    If exportspreadsheets = 1 Then
    OpenWorkingFolder = MsgBox("Would you like to open your working folder to find spreadsheets?", MB_YESNO, "Payroll Analysis")
    If OpenWorkingFolder = IDYES Then
        X = Shell("explorer.exe "+Chr(34)+Client.WorkingDirectory+Chr(34),1)
    End If
    End If
    CloseProgram = MsgBox("Do you need to leave IDEA open to run more scripts or do other work?", MB_YESNO, "Payroll Analysis")
    If CloseProgram = IDNO Then
        X = Shell(ScriptPath+"process.exe -q idea.exe",6)
    End If
End Function

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

Function ExportExcel(stub)
    wfolder = Client.WorkingDirectory
    empty = 0
    If FileExists(wfolder & stub & ".IMD") Then
        Set check = Client.OpenDatabase(wfolder & stub & ".IMD")
        If check.count = 0 Then empty = 1
    Else
        empty = 1
    End If
    If empty = 1 Then
        ' export empty ss
        Dim NewTable As Table
        Set NewTable = Client.NewTableDef
        Dim AddedField As Field
        Set AddedField = NewTable.NewField
        AddedField.Name = "NO_RECORDS"
        AddedField.Type = WI_CHAR_FIELD
        AddedField.Length = 20
        NewTable.AppendField AddedField
        NewTable.Protect = False
        Dim db As Database
        Set db = Client.NewDatabase(wfolder & "empty_db_for_export.IMD", "", NewTable)
        Dim rs As RecordSet
        Set rs = db.RecordSet
        Dim rec As Record
        Set rec = rs.NewRecord
        rec.SetCharValue "NO_RECORDS", ""
        rs.AppendRecord rec
        NewTable.Protect = True
        db.CommitDatabase
        Set db = Nothing
        Set AddedField = Nothing
        Set NewTable = Nothing
        Set rs = Nothing
        Set rec = Nothing
        Set db = Client.OpenDatabase(wfolder & "empty_db_for_export.IMD")
        Set task = db.ExportDatabase
        task.IncludeAllFields
        eqn = ""
        task.PerformTask wfolder & stub & ".xls", "Database", "XLS8", 1, db.Count, eqn
        Set db = Nothing
        Set task = Nothing
        Client.DeleteDatabase(wfolder & "empty_db_for_export.IMD")    
    Else
        Set db = Client.OpenDatabase(stub & ".IMD")
        Set task = db.ExportDatabase
        task.IncludeAllFields
        eqn = ""
        Wfolder = Client.WorkingDirectory    
        task.PerformTask wfolder & stub & ".xls", "Database", "XLS8", 1, db.Count, eqn
        Set db = Nothing
        Set task = Nothing
    End If
End Function

The VBScript FileExists is generally more reliable than alternatives:

Function FileExists%(filename$)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If (fso.FileExists(filename$)) Then
     FileExists% = TRUE
    Else
     FileExists% = FALSE
    End If
End Function

And just a simple replace function for string handling:

Function Replace(SourceString As String, SearchString As String, ReplaceString As String, CaseSensitive As Boolean)
    ' adapted from http://www.exceltip.com/st/Replace_text_in_a_text_file_using_VBA_in_Microsoft_Excel/494.html
    Dim p As Integer, NewString As String, use1 As String, use2 As String
    Do
    If CaseSensitive = True Then
    use1=SourceString
    use2=SearchString
    Else
    use1=UCase(SourceString)
    use2=UCase(SearchString)
    End If
    p = InStr(p + 1, use1, use2)
    If p > 0 Then ' replace
    NewString = ""
    If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
    NewString = NewString + ReplaceString
    NewString = NewString + Mid(SourceString, p + Len(SearchString), Len(SourceString))
    p = p + Len(ReplaceString) - 1
    SourceString = NewString
    End If
    If p >= Len(NewString) Then p = 0
    Loop Until p = 0
    Replace = SourceString
End Function

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.

BLOG  MORE  ABOUT  MAIL