Introduction:
Oracle APEX Interactive Grid allows users to drag and fill values across rows. However, by default, dragging a numeric value simply copies the same value to all selected rows instead of incrementing it like Microsoft Excel.
This blog explains how to implement Excel-like auto-increment functionality for numeric columns using JavaScript, while keeping the existing Interactive Grid and save process unchanged.
Technologies Used
- Oracle APEX
- JavaScript
- CSS
Why we need to do :
In many business applications, users need to enter sequential values such as serial numbers, sequence IDs, or row numbers. They expect the Interactive Grid to behave like Excel, where dragging a value automatically increments it (1, 2, 3, 4…).
Since Oracle APEX only copies the same value during drag-fill, users have to update each value manually, which is time-consuming and prone to errors.
How do we solve:
The solution uses JavaScript to capture the starting row and calculate the row offset while dragging. If the selected value is numeric, the value is automatically incremented for each row. For non-numeric values, the existing copy behavior is retained.
The implementation involves:
Step 1: Create the Interactive Grid
Create an Interactive Grid and assign the Static ID as my_ig.
Step 2: Add CSS Class
For every column that should support Excel-like drag fill, add the following CSS class under:
Column Attributes → CSS Classes
Step 3: Add JavaScript
Add the following JavaScript under:
Page Designer → Execute When Page Loads
This script captures the starting row, calculates the row offset during drag, and increments only numeric values.
$(document).ready(function () {
let draggedValue = null,
draggedColumn = null,
startRowIndex = null,
isNumeric = false;
function getRowIndex($row) {
return $row.closest("tbody").find("tr").index($row);
}
$(document).on("mousedown", ".checkname", function (event) {
let $cell = $(this),
$row = $cell.closest("tr"),
ig$ = apex.region("my_ig").widget(),
model = ig$.interactiveGrid("getViews", "grid").model,
record = model.getRecord($row[0].dataset.id),
columnName = Object.keys(
ig$.interactiveGrid("getViews", "grid").modelColumns
)[$cell.index()];
draggedValue = model.getValue(record, columnName);
draggedColumn = columnName;
startRowIndex = getRowIndex($row);
isNumeric = !isNaN(parseFloat(draggedValue));
event.preventDefault();
});
$(document).on("mousemove", "tr", function () {
if (!draggedColumn) return;
let $row = $(this),
ig$ = apex.region("my_ig").widget(),
model = ig$.interactiveGrid("getViews", "grid").model,
record = model.getRecord($row[0].dataset.id);
if (record) {
let value = isNumeric
? parseFloat(draggedValue) + (getRowIndex($row) - startRowIndex)
: draggedValue;
model.setValue(record, draggedColumn, String(value));
}
});
$(document).on("mouseup", function () {
draggedValue = draggedColumn = startRowIndex = null;
isNumeric = false;
});
});
Step 4: Add CSS
Add the following CSS under:
Page → CSS → Inline
This displays a small drag indicator on the selected columns.
.checkname{
position:relative;
padding-right:25px;
}
.checkname::after{
content:”^”;
position:absolute;
right:5px;
top:50%;
transform:translateY(-50%);
width:18px;
height:18px;
display:flex;
align-items:center;
justify-content:center;
background:#fff;
color:#db3338;
font-weight:bold;
border-radius:50%;
box-shadow:0 0 3px rgba(0,0,0,.3);
}
Step 5: Save the Grid
No changes are required to the save process. Continue using the standard Interactive Grid DML process.
BEGIN
CASE :APEX$ROW_STATUS
WHEN ‘C’ THEN
INSERT INTO EMPLOYEES (…)
VALUES (…);
WHEN ‘U’ THEN
UPDATE EMPLOYEES
SET …
WHERE ROW_ID = :ROW_ID;
WHEN ‘D’ THEN
DELETE FROM EMPLOYEES
WHERE ROW_ID = :ROW_ID;
END CASE;
END;
When users click Save, Oracle APEX automatically saves the incremented values.
Conclusion: This solution enhances the Oracle APEX Interactive Grid by providing an Excel-like auto-increment feature for numeric values during drag-fill. It improves data entry efficiency, reduces manual effort, and minimizes errors without modifying the existing Interactive Grid structure or save process. The same functionality continues to copy text values as before, providing a seamless experience for users.
