A customer maintenance form in Uniface 10, part 2 - the four things that turn CRUD into a business application

In [part 1] the customer form does the full CRUD cycle: search, select, edit, save, delete. It compiles, it runs, it looks fine in a demo.

Then you hand it to someone who actually works with it, and within ten minutes you get four bug reports:

1. "I created a customer and it overwrote another one."
2. "I typed a phone number, clicked the next row, and my change was gone. No warning."
3. "Searching for meier finds nothing, but Meier does. And I can't search by e-mail."
4. "We now have the same customer three times."

None of these are Uniface problems. They are the difference between a CRUD screen and a maintenance form. Here is how each one was solved, with the code.

1. IDs: why MAX(ID) + 1 is wrong, and what to do instead

The tempting version is one line:

SELECT COALESCE(MAX(CUSTOMER_ID), 0) + 1 FROM CUSTOMER

Two people saving in the same second get the same number, and one store fails on the primary key - or worse, in a system where the key is not enforced, they overwrite each other. And if you ever delete a customer, the number comes back and gets reused, which is exactly what you do not want on an identifier that appears on invoices.

The classic 4GL answer is a number range table:

CREATE TABLE IF NOT EXISTS CUSTOMER_SEQ (
  SEQ_NAME VARCHAR(30) NOT NULL PRIMARY KEY,
  LAST_ID  INTEGER     NOT NULL
);

INSERT INTO CUSTOMER_SEQ (SEQ_NAME, LAST_ID)
SELECT 'CUSTOMER', COALESCE(MAX(CUSTOMER_ID), 0) FROM CUSTOMER
WHERE NOT EXISTS (SELECT 1 FROM CUSTOMER_SEQ WHERE SEQ_NAME = 'CUSTOMER');

The seed statement is written so you can run it against an existing table without resetting anything.

In ProcScript, the counter is incremented and read back with the sql statement, which takes the statement and the path name:

public operation NEXT_ID
params
    numeric pId : OUT
    string pError : OUT
endparams
    pError = ""
    pId = 0
    sql "UPDATE CUSTOMER_SEQ SET LAST_ID = LAST_ID + 1 WHERE SEQ_NAME = 'CUSTOMER'", "CUSTOMERS"
    if ($status < 0)
        pError = $concat("The customer number range could not be updated (error ", $procerror, ").")
        return -1
    endif
    sql "SELECT LAST_ID FROM CUSTOMER_SEQ WHERE SEQ_NAME = 'CUSTOMER'", "CUSTOMERS"
    if ($status < 1)
        pError = "The customer number range CUSTOMER is missing in table CUSTOMER_SEQ."
        return -1
    endif
    pId = $result
    return 0
end

Three details that matter:

• The path name goes in without the $: the ASN defines $CUSTOMERS, the statement says "CUSTOMERS".
• $result holds the first column of the first row after a SELECT via sql. For a single-value lookup that is all you need; sql/data gives you the full result set.
• The UPDATE and the following store of the customer run in the same transaction, closed by one commit (or undone by one rollback). That is what makes the number safe: if the customer cannot be saved, the counter goes back too.

Gaps are expected. If someone starts creating a customer and cancels, that number is gone. On a number range that is normal and much better than reusing identifiers.

2. Unsaved changes: ask, with three answers

The grid row click is wired to the list entity's getFocus trigger:

trigger getFocus
    call SELECT_ROW
end

The naive SELECT_ROW just moves the detail area to the clicked occurrence. The useful one notices unsaved work first. askmess/question supports any answer list, and $status is the 1-based index of the button that was clicked:

entry SELECT_ROW
variables
    numeric vNew, vOld
    string vId
endvariables
    vNew = $curocc(LIST_DMY)
    vOld = $curocc(CUSTOMER)
    if (vNew = vOld)
        return 0
    endif
    if ($occdbmod(CUSTOMER) = 1)
        askmess/question "The current customer has unsaved changes. What do you want to do?~Unsaved changes", "Save,Discard,Stay"
        if ($status = 1)
            call DO_SAVE
            if ($status < 0)
                setocc "LIST_DMY", vOld
                return -1
            endif
            setocc "CUSTOMER", vNew
            setocc "LIST_DMY", vNew
            return 0
        elseif ($status = 2)
            setocc "CUSTOMER", vNew
            vId = CUSTOMER_ID.CUSTOMER
            call LOAD_LIST
            call SELECT_BY_ID(vId)
            return 0
        else
            setocc "LIST_DMY", vOld
            return 0
        endif
    endif
    setocc "CUSTOMER", vNew
    return 0
end

Worth noticing:

• Save can fail (validation, duplicate warning declined). In that case the selection is put back where it was, so the user stays on the record that still needs attention.
• Discard cannot simply move on: the modified occurrence is still in memory. Re-reading with LOAD_LIST throws the change away, and SELECT_BY_ID then restores the selection the user asked for.
• Stay puts the grid selection back (LIST_DMY), because the click already moved it.

The same $occdbmod check guards the New button, and its component-wide sibling $instancedbmod guards Search and the window's quit trigger. Four entry points, one rule.

3. Search: push it down to SQL

The first attempt did the obvious 4GL thing: put a value with a wildcard into the field profile and retrieve. That produced an empty list every time, and the reason is a nice little Uniface lesson - * is a profile character, but % is not, and the pattern I had built ("%%*") was neither a valid profile nor a literal. (The profile-character way to say "everything" is $string("&uALL;").)

Profiles also cannot express what a user expects from a search box: contains, across five columns at once, case-insensitive.

So the search is pushed down to the database. Uniface lets you attach a WHERE clause to an entity before retrieving it:

entry LOAD_LIST
variables
    string vWhere, vProps
endvariables
    clear/e "CUSTOMER"
    vProps = ""
    activate "CUSTOMER_SVC".BUILD_SEARCH_WHERE(SEARCH_TEXT.SEARCH_DMY, vWhere)
    if (vWhere != "")
        putitem/id vProps, "WHERE", vWhere
    endif
    $entityproperties(CUSTOMER) = vProps
    retrieve/e "CUSTOMER"
    ...

$entityproperties is a list of named items; the generated read trigger of a modeled entity evaluates an item called WHERE and appends it to the SQL it sends. Clearing the properties (vProps = "") before every retrieve is important - otherwise the last search sticks.

The clause itself is built in one place:

public operation BUILD_SEARCH_WHERE
params
    string pText : IN
    string pWhere : OUT
endparams
variables
    string vText, vPattern
endvariables
    pWhere = ""
    vText = pText
    call TRIM_TEXT(vText)
    if (vText = "")
        return 0
    endif
    call SQL_ESCAPE(vText)
    vPattern = $concat("'%", vText, "%'")
    pWhere = "LAST_NAME LIKE %%(vPattern) OR FIRST_NAME LIKE %%(vPattern) OR EMAIL LIKE %%(vPattern) OR PHONE LIKE %%(vPattern) OR CAST(CUSTOMER_ID AS TEXT) = '%%(vText)'"
    return 0
end

and the escaping is four lines:

entry SQL_ESCAPE
params
    string pText : INOUT
endparams
    if ($scan(pText, "'") > 0)
        pText = $replace(pText, 1, "'", "''", -1)
    endif
    return 0
end

You are building SQL from user input here, so this is not optional. $replace(text, start, from, to, -1) replaces all occurrences; O'Neil becomes O''Neil. (There is a test for exactly this in part 3.)

Two properties of this search that are worth stating in the UI, because users will notice them:

• SQLite's LIKE is case-insensitive for ASCII out of the box - so meier finds Meier, but müller vs Müller is not covered by that rule.
• % and _ typed into the search box act as SQL wildcards. For an internal tool that is a feature; if you dislike it, escape them too.

Searching for 4 now finds customer number 4 and everyone whose phone number contains a 4 - which surprised me for a second and is exactly right.

4. Sorting by clicking a column header

The grid widget fires a trigger with the clicked field name:

trigger ColumnHeader_LClicked(pFieldName : IN, pModifierKeys : IN)
    call SORT_LIST(pFieldName)
end

The entry maps the field name to a sort field, toggles the direction if it is the same column, and - this is the part that makes it feel right - keeps the selected customer selected:

entry SORT_LIST
params
    string pField : IN
endparams
variables
    string vField, vId
endvariables
    if ($scan(pField, "LAST_NAME") > 0)
        vField = "LAST_NAME"
    elseif ($scan(pField, "FIRST_NAME") > 0)
        vField = "FIRST_NAME"
    elseif ($scan(pField, "EMAIL") > 0)
        vField = "EMAIL"
    else
        return 0
    endif
    if ($sortField$ = vField & $sortDir$ = "a")
        $sortDir$ = "d"
    else
        $sortDir$ = "a"
    endif
    $sortField$ = vField
    if ($totdbocc(CUSTOMER) < 1)
        return 0
    endif
    vId = CUSTOMER_ID.CUSTOMER
    sort "CUSTOMER", $concat($sortField$, ":", $sortDir$, " ci")
    call FILL_LIST
    call SELECT_BY_ID(vId)
    return 0
end

• $sortField$ and $sortDir$ are component variables, declared once in the Declarations block:

variables
    string sortField, sortDir
endvariables

and addressed with $name$ in the script. They survive between trigger calls, which is what a sort state needs.

• The sort specification is "FIELD:a ci" / "FIELD:d ci" - ascending/descending plus ci for case-insensitive. Without ci, anna sorts after Zoe.
• sort reorders the hitlist in memory, so FILL_LIST has to run again to mirror it into the grid.

5. Duplicates: warn, do not block

The rule the business wanted: "tell me, but let me decide" - there are real people with the same name.

public operation COUNT_DUPLICATES
params
    numeric pId : IN
    string pLastName : IN
    string pFirstName : IN
    string pEmail : IN
    numeric pCount : OUT
    string pError : OUT
endparams
variables
    string vLast, vFirst, vEmail, vSql
endvariables
    pCount = 0
    pError = ""
    vLast = pLastName
    vFirst = pFirstName
    vEmail = pEmail
    call TRIM_TEXT(vLast)
    call TRIM_TEXT(vFirst)
    call TRIM_TEXT(vEmail)
    call SQL_ESCAPE(vLast)
    call SQL_ESCAPE(vFirst)
    vSql = "SELECT COUNT(*) FROM CUSTOMER WHERE CUSTOMER_ID <> %%(pId) AND ((LOWER(LAST_NAME) = LOWER('%%(vLast)') AND LOWER(FIRST_NAME) = LOWER('%%(vFirst)'))"
    if (vEmail != "")
        call SQL_ESCAPE(vEmail)
        vSql = $concat(vSql, " OR LOWER(EMAIL) = LOWER('%%(vEmail)')")
    endif
    vSql = $concat(vSql, ")")
    sql vSql, "CUSTOMERS"
    if ($status < 0)
        pError = $concat("The duplicate check failed (error ", $procerror, ").")
        return -1
    endif
    pCount = $result
    return 0
end

The CUSTOMER_ID <> %%(pId) part is the one that is easy to forget: without it, editing an existing customer reports the customer as a duplicate of themselves. For a new record pId is 0, which excludes nothing.

In the form, the warning is a question, not an error:

    activate "CUSTOMER_SVC".COUNT_DUPLICATES(vId, vLast, vFirst, vEmail, vCount, vError)
    if ($status < 0)
        message/error $concat(vError, " The customer was not saved.")
        return -1
    endif
    if (vCount > 0)
        askmess/warning "A customer with the same name or e-mail address already exists. Do you want to save anyway?~Possible duplicate", "Yes,No"
        if ($status != 1)
            return -1
        endif
    endif

The save path, end to end

With all four pieces in place, DO_SAVE reads like a checklist - validate, check duplicates, assign the number and timestamp for new records, store, commit, refresh the row:

entry DO_SAVE
variables
    string vLast, vFirst, vEmail, vPhone, vField, vError
    numeric vId, vNewId, vCount, vOcc, vStatus, vErrCode
    boolean vIsNew
endvariables
    vLast  = LAST_NAME.CUSTOMER
    vFirst = FIRST_NAME.CUSTOMER
    vEmail = EMAIL.CUSTOMER
    vPhone = PHONE.CUSTOMER
    activate "CUSTOMER_SVC".VALIDATE(vLast, vFirst, vEmail, vPhone, vField, vError)
    if ($status < 0)
        message/error $concat(vError, " The customer was not saved.")
        call PROMPT_FIELD(vField)
        return -1
    endif
    if (LAST_NAME.CUSTOMER != vLast)
        LAST_NAME.CUSTOMER = vLast
    endif
    ...
    vIsNew = ($dbocc(CUSTOMER) = 0)
    vId = 0
    if (!vIsNew)
        vId = CUSTOMER_ID.CUSTOMER
    endif
    activate "CUSTOMER_SVC".COUNT_DUPLICATES(vId, vLast, vFirst, vEmail, vCount, vError)
    ...
    if (vIsNew)
        if (CUSTOMER_ID.CUSTOMER = "")
            activate "CUSTOMER_SVC".NEXT_ID(vNewId, vError)
            if ($status < 0)
                rollback
                message/error $concat(vError, " The customer was not saved.")
                return -1
            endif
            CUSTOMER_ID.CUSTOMER = vNewId
        endif
        CREATED_AT.CUSTOMER = $datim
    endif
    store/e "CUSTOMER"
    vStatus  = $status
    vErrCode = $procerror
    if (vStatus < 0)
        rollback
        if (vIsNew)
            CREATED_AT.CUSTOMER = ""
        endif
        putmess $concat("DO_SAVE store failed: status ", vStatus, ", procerror ", vErrCode)
        message/error $concat("The customer could not be saved (status ", vStatus, ", error ", vErrCode, ").")
        return -1
    endif
    commit
    vOcc = $curocc(CUSTOMER)
    setocc "LIST_DMY", vOcc
    L_LAST_NAME.LIST_DMY  = LAST_NAME.CUSTOMER
    L_FIRST_NAME.LIST_DMY = FIRST_NAME.CUSTOMER
    L_EMAIL.LIST_DMY      = EMAIL.CUSTOMER
    call UPDATE_COUNT
    message/info "The customer was saved."
    return 0
end

Two small things in there that pay off:

• Writing the trimmed values back into the fields means the user sees what was actually stored.
• putmess writes a line into the IDE log (log\ide_<pid>.log) next to the user-facing message. When a customer reports "it says error -1", the log already has the status and the $procerror. It costs one line.

And PROMPT_FIELD puts the cursor where the problem is, so an error message is actionable rather than decorative:

entry PROMPT_FIELD
params
    string pField : IN
endparams
    selectcase pField
    case "LAST_NAME"
        $prompt = LAST_NAME.CUSTOMER
    case "FIRST_NAME"
        $prompt = FIRST_NAME.CUSTOMER
    case "EMAIL"
        $prompt = EMAIL.CUSTOMER
    case "PHONE"
        $prompt = PHONE.CUSTOMER
    endselectcase
    return 0
end

That is why VALIDATE returns a field name as well as a message - the rule and the UI reaction stay in sync.

Where this leaves the code

The form now behaves. It also now contains SQL strings, escaping logic, e-mail rules and a number range - in a component that can only be tested by a human clicking buttons. Every one of the rules above is exactly the kind of thing you want a test for, and none of it is reachable from a test.

[Part 3] moves all of it into a service, adds 30 automated tests that run from the IDE, and ends with a bug that only appeared because the ID assignment got safer.