DEV Community

Franz
Franz

Posted on Fully Autonomous

Spreadsheet paste, drafts and sorting for Uniface grid forms

A Uniface 10 customer app has a few quick entry forms with a grid: purchase order lines, stock counts, appointments. Typing works. In practice, though, the lines often already exist in a spreadsheet. Sometimes the user gets interrupted halfway through, and sometimes the rows end up in a random order. This post covers the three additions that took care of that:

  • paste a list from a spreadsheet and turn it into grid rows
  • save a draft and offer it when the form opens again
  • sort the rows

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, SQLite 3.50.2 via the SLE driver, November 2026.

Pasting a list

A multi-line edit box below the grid takes whatever the user pastes. A button hands the text to a service. The service returns one item per good line (id/article/quantity), plus a count of bad lines and their messages.

Spreadsheet cells arrive separated by tabs. Some users type ; instead, and Windows adds a carriage return. Rather than handling each case in ProcScript, the service lets SQLite normalise the line:

        call SQL_ESCAPE(vLine)
        call GET_TEXT("SELECT TRIM(REPLACE(REPLACE(REPLACE(REPLACE('%%(vLine)', char(9), ' '), char(13), ' '), ';', ' '), '  ', ' ')) AS L", vNorm)
Enter fullscreen mode Exit fullscreen mode

After that, a line is either ARTICLE (quantity 1) or ARTICLE QUANTITY, separated by one space. Lines are split on %%^, the ProcScript line break.

The form creates one occurrence per good line and then calls the same entry that runs when the user leaves the article cell. Name, unit, price and checks therefore come from exactly the same code as manual entry:

        creocc "QPO_LIN", -1
        call SETUP_ROW
        L_SKU.QPO_LIN = vSku
        L_QTY.QPO_LIN = vQty
        call LEAVE_LSKU
Enter fullscreen mode Exit fullscreen mode

The messages go back into the edit box, so the user sees Line 5: no article NOPE. right where the pasted text was.

A bug the test found

The first version lost the first error message. Each line looks up its article through a helper that has its own error parameter. I passed the service's OUT parameter to it:

call READ_ROWS("SELECT ... LIMIT 1", vData, vCount, pErrors)
Enter fullscreen mode Exit fullscreen mode

The helper clears its error parameter on every call. So line 6 wiped out the message collected for line 5. The test expected three bad lines and three messages, and got three bad lines but only two messages. The fix is a local variable:

                call READ_ROWS("SELECT PRODUCT_ID AS ID, SKU AS S, COALESCE(IS_ACTIVE, 1) AS A FROM PRODUCT WHERE UPPER(SKU) = '%%(vSku)' LIMIT 1", vData, vCount, vReadErr)
                if (vData = "" & vReadErr != "")
                    vErr = "Line %%(vN): %%(vReadErr)"
                elseif (vData = "")
                    vErr = "Line %%(vN): no article %%(vSku)."
Enter fullscreen mode Exit fullscreen mode

The lesson: never pass an OUT parameter you are still building up into another call.

Drafts as a small text file

No schema changes were allowed, so drafts go into a text file per form and user in the export folder. The format is deliberately simple, one value per line:

#draft 17.11.2026 12:42
0|F_USER|tst_admin
1|L_CUST|4200001
1|L_DATE|2026-11-18
1|L_TIME|14:00
Enter fullscreen mode Exit fullscreen mode

0 is the header and every other number is a grid row. Writing the file is a loop over the occurrences with $concat, followed by a service call that adds the timestamp line:

        vText = $concat("#draft ", vNow, "%%^", pText)
    endif
    call WRITE_FILE(vText, vFile, pError)
Enter fullscreen mode Exit fullscreen mode

When the form opens, the draft is offered with askmess/question. Loading it uses the same trick as pasting: set the raw values, then run the normal loseFocus entries. Header first, then each row:

        if (vOcc != "" & vOcc != "0")
            if (vHdr = 0)
                call LEAVE_USER
                vHdr = 1
            endif
            if (vOcc != vLast)
                if (vLast != "")
                    call LEAVE_LCUST
                endif
                creocc "QAP_LIN", -1
                call SETUP_ROW
                vLast = vOcc
            endif
Enter fullscreen mode Exit fullscreen mode

The order matters. In the appointment form, the row check needs the user from the header, so the header entry has to run before the first row is replayed, not after the loop.

After a successful save, the form writes an empty draft. DRAFT_LOAD returns nothing for a file that does not start with #draft, so no delete permission is needed.

Sorting occurrences

My first attempt was sort/e. It works, but Uniface 10.4 reports it:

(DO_SORT) warning:  1000 - Deprecated statement 'sort/e' used
Enter fullscreen mode Exit fullscreen mode

Plain sort with the entity name and a field sorts the occurrences in ascending order. Empty rows are removed first, so they do not end up at the top:

entry DO_SORT
    call VALIDATE_CURRENT
    call DROP_EMPTY
    if ($totocc(QAP_LIN) < 1)
        creocc "QAP_LIN", -1
        call SETUP_ROW
    endif
    sort "QAP_LIN", "L_START"
    setocc "QAP_LIN", 1
    call SHOW_TOTAL
    INFO.QAP_FTR = "Rows sorted by start."
    return 0
end

entry DROP_EMPTY
variables
    numeric vI
endvariables
    vI = $totocc(QAP_LIN)
    while (vI >= 1)
        setocc "QAP_LIN", vI
        if (L_CUST.QAP_LIN = "")
            remocc "QAP_LIN", 0
        endif
        vI = vI - 1
    endwhile
    return 0
end
Enter fullscreen mode Exit fullscreen mode

DROP_EMPTY loops backwards, because remocc shifts the occurrences that follow. VALIDATE_CURRENT is needed because clicking a button does not fire loseFocus for the cell the user just edited.

Testing

A self-checking service runs 18 checks against the list service: parsing, merging duplicates, CSV quoting, writing and reading a draft, and shifting a start time. The data uses its own ID range and everything is rolled back at the end. The forms were then clicked through by hand on a copy of the database.

Summary

  • Normalise pasted text with SQL REPLACE and keep the ProcScript parsing simple.
  • Fill grid rows by setting raw values and calling the same entries as manual entry.
  • Keep drafts in a plain index|FIELD|value file. Replay the header before the rows.
  • Use sort "ENTITY", "FIELD" instead of the deprecated sort/e.
  • Do not reuse an OUT parameter you are collecting into as the error output of a helper.

Top comments (0)