A customer maintenance form in Uniface 10, part 5 - "delete" should not mean delete

At the end of [part 4] the customer form could create, edit, search and delete customers, it warned about duplicates, it had a tested service behind it and it survived two people editing the same record. The next item on the list looked like the smallest one:

A customer who stops buying should disappear from the everyday list - but not from the database.

In real business data you almost never want a hard DELETE. Invoices, addresses, notes and audit entries hang off the customer. Removing the row either fails on a foreign key or, worse, silently orphans everything that pointed at it. The usual answer is a status flag. This post is how that went in Uniface 10 with SQLite - the logic part took an hour, the "paint one checkbox" part took longer than I would like to admit.

The data change

One column. The existing rows get a value so that nothing changes for them:

ALTER TABLE CUSTOMER ADD COLUMN IS_ACTIVE INTEGER;
UPDATE CUSTOMER SET IS_ACTIVE = 1 WHERE IS_ACTIVE IS NULL;

As in part 4, this ran through the IDE's EDIT SQL dialog - one statement per execution, followed by COMMIT. The dialog accepts a multi-statement script without complaint and then does nothing with it.

In the model, IS_ACTIVE became a field of data type Boolean (interface B). The first thing worth checking is what Uniface actually writes into SQLite for a Boolean, because every SQL condition later depends on it. I saved one active and one inactive customer and looked at the file:

IS_ACTIVE
1
0

Plain integers. That is good news: a Boolean in the form is 1/0 in the table, and SQL can filter on it without any conversion.

One condition, in one place

The list in the form is filled by a retrieve with a WHERE clause that comes from the service. Since part 2 the service has an operation that builds that clause from the search text:

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

Three details that matter more than they look:

• COALESCE(IS_ACTIVE, 1) instead of IS_ACTIVE = 1. Rows that somehow end up with NULL (an import, a manual INSERT, a forgotten default) count as active instead of vanishing from every list. A record that nobody can find is a support ticket waiting to happen.
• The new parameter pWithInactive makes "show everything" a decision of the caller. The customer form passes the value of a checkbox; the lookup dialog from part 7 always passes 0.
• The filter and the text search are combined with AND in one place. The form does not know any SQL. When a second screen needed the same list later, it got the same rules for free.

The search tests in the test service from part 3 got two new cases for the flag - the empty search that must still hide inactive customers, and the combination of text and flag:

PASS: SEARCH empty text with inactive gives no condition
PASS: SEARCH empty text filters inactive
PASS: SEARCH combines text and active filter
PASS: SEARCH escapes single quotes
Customer service: 35 tests, 0 failures

The form side

Three changes in the form:

1. DO_NEW sets IS_ACTIVE.CUSTOMER = 1, so a new customer is active without the user thinking about it.
2. The search area got a checkbox SHOW_INACTIVE on the non-database entity SEARCH_DMY, passed straight into BUILD_SEARCH_WHERE.
3. The detail area got the IS_ACTIVE checkbox, so an inactive customer that you do open can be reactivated by hand.

LOAD_LIST itself only changed by one argument:

activate "CUSTOMER_SVC".BUILD_SEARCH_WHERE(SEARCH_TEXT.SEARCH_DMY, SHOW_INACTIVE.SEARCH_DMY, vWhere)
if (vWhere != "")
    putitem/id vProps, "WHERE", vWhere
endif
$entityproperties(CUSTOMER) = vProps
retrieve/e "CUSTOMER"

"Delete" becomes a question

The interesting part is the Delete button. It used to ask "Delete this customer? Yes / No". It now asks something different depending on the state of the record:

if (IS_ACTIVE.CUSTOMER = 1)
    askmess/question $concat("Customer ", FIRST_NAME.CUSTOMER, " ", LAST_NAME.CUSTOMER, ": deactivate the customer, or delete it permanently?~Delete customer"), "Deactivate,Delete permanently,Cancel"
    vAnswer = $status
else
    askmess/question $concat("Customer ", FIRST_NAME.CUSTOMER, " ", LAST_NAME.CUSTOMER, " is inactive. Reactivate the customer, or delete it permanently?~Inactive customer"), "Reactivate,Delete permanently,Cancel"
    vAnswer = $status
    if (vAnswer = 1)
        vAnswer = 4
    endif
endif
if (vAnswer = 1)
    IS_ACTIVE.CUSTOMER = 0
endif
if (vAnswer = 4)
    IS_ACTIVE.CUSTOMER = 1
endif
if (vAnswer = 1 | vAnswer = 4)
    call DO_SAVE
    if ($status < 0)
        return -1
    endif
    call LOAD_LIST
    return 0
endif
if (vAnswer != 2)
    return 0
endif

askmess returns the number of the button that was pressed in $status. The mapping of "Reactivate" to an internal value 4 keeps the rest of the routine readable: 1 deactivate, 2 delete permanently, 3 cancel, 4 reactivate.

The important design decision is the line call DO_SAVE. Deactivating is not a special SQL update on the side. It is an ordinary change to an ordinary field, saved through the ordinary save path. That means everything from part 4 applies automatically:

• the version counter is incremented,
• CHANGED_AT and CHANGED_BY record who deactivated the customer and when,
• and if someone else changed the customer in the meantime, the same conflict dialog appears.

A dedicated UPDATE CUSTOMER SET IS_ACTIVE = 0 WHERE ... would have been two lines shorter and would have bypassed all of that.

"Delete permanently" still exists, deliberately. Test data, duplicates created by mistake and GDPR erasure requests are real reasons to remove a row. It is just no longer the default answer.

Painting one checkbox

Now the part I did not expect to write about. The logic was done and tested in about an hour. Getting two checkboxes onto the form took longer, and the reasons are worth knowing if you work with the Uniface 10 form painter.

"New frame cannot overlap existing frame". The first attempt to paint IS_ACTIVE into the detail area ended with

6464 - New frame cannot overlap existing frame

The area looked empty. It was not: the form is a character grid, and the cells to the right of the last field were still occupied by that field's frame. The fix was to find a column that is really free in the grid (here column 71 onwards), not one that looks free on screen.

Geometry is not typable. Top, Left, Height and Width appear in the property grid, but the values cannot be typed. Size changes only work through the handles on the canvas - and not everywhere. The height of an entity frame could not be changed at all, and later, when I wanted to make one field three characters narrower, dragging its handle did nothing either.

Labels attach themselves to the field on their left. A label object gets an "Associated Field" automatically, and the painter picks the field to its left. The label for a checkbox at the right edge of a row therefore belongs to the field before it. It works, it just is not what you would choose.

The combination of the last two produced the most visible blemish of the whole project: the label next to the checkbox is four characters wide, its text is "Active:" and the form shows "Activ". Since neither the label nor its neighbour could be resized, the pragmatic fix was a shorter caption, Act.:. Not pretty, but honest about the constraint.

The search-area checkbox has no visible caption at all - there was no free grid cell for a label - only a tooltip, "Show inactive customers in the list". That one is on the list of things to redo when the search area gets redesigned anyway.

Read-only fields should look read-only. CUSTOMER_ID and CREATED_AT already had a grey background from part 1. The new CHANGED_AT and CHANGED_BY fields did not, so for a while the form showed two grey read-only fields and two white read-only fields. The colours are widget properties, and they only stick when set through the ... dialog of the property, not typed as a list:

Background Color   #F1F5F9
Foreground Color   #475569

Typing BACKCOLOR=#F1F5F9;FORECOLOR=#475569 directly into the property looks right in the grid and has no effect at runtime, because the separator has to be Uniface's internal list separator, which the dialog writes and the keyboard does not.

Test run

The service tests were green first:

Customer service: 35 tests, 0 failures

The manual checklist for this step, which now lives in the project's handover file and is repeated after every change to the delete logic:

• New customer → the Act. checkbox is set, the customer appears in the list.
• Delete → Deactivate → the customer disappears from the list; CHANGED_AT, CHANGED_BY and VERSION_NO change like after any other save.
• Tick show inactive, search → the customer is back, checkbox cleared.
• Delete on the inactive customer → the dialog offers Reactivate → back in the normal list.
• Delete → Delete permanently → the row is gone from the database.

"Delete permanently" was exercised again several times in the following steps (with and without dependent addresses, see part 6), and the service tests have been re-run after every later change - still 35 of 35.

What I took away

Soft delete is a UX change more than a data change. The SQL was two statements. The real work was deciding what the button should ask, and making sure the answers go through the same save path as every other edit.

Put the filter where the SQL is. Because the active-only condition lives in BUILD_SEARCH_WHERE, the rule "inactive customers are hidden unless asked for" exists exactly once. Two screens later, it paid off.

Treat NULL as a state. COALESCE(IS_ACTIVE, 1) is a small decision about what an unknown value means. Making it explicit is cheaper than finding out through a missing record.

Budget time for the painter. In a 4GL with a grid-based form painter, layout constraints are real constraints. The logic in this step was fully tested; one label still says Act.:.

Next: customers need more than one address - billing, delivery, postal. That is the first real 1:n relation in the application, with its own number range, its own service and its own form.