Excel-Like Auto-Increment Fill
in Oracle APEX Interactive Grid
This is Part 2 of the series. Part 1 covers the basic drag-fill copy behavior. Read it first if you haven't done the initial setup yet. Part 1: Excel-Like Drag Fill in Oracle APEX IG →
When copy-fill is not enough
In Part 1 we built drag-fill where selecting a cell and dragging down copies the same value to every row. Users liked it and it worked well. Then one of the team members pointed out something obvious: "In Excel when you drag a number it goes 1, 2, 3. Ours just repeats the same value. Can we fix that?"
I thought it would be a quick fix. It was not. A couple of my first ideas were wrong and wasted time. This post is the honest story of what I tried and what finally worked.
What I tried before it worked
I went through a few approaches before landing on the right one. Here is exactly what happened.
Adding 1 on every mousemove
First attempt: add 1 each time mousemove fires. But mousemove fires many times per pixel. After dragging two rows the value had already jumped to 50. Not going to work.
Counting dirty rows in the model
Count how many rows were already modified in the IG model and use that as the offset. The dirty count included old edits from earlier in the session and was never accurate.
Row index offset from the start row
Save the row index on mousedown. On each mousemove calculate the current row index and subtract the start. Add that offset to the starting value. Always correct.
What actually changed
If you already have Part 1 running, the only thing that changes is the JavaScript in Execute When Page Loads. The CSS, PL/SQL process, column setup, and region Static ID are all untouched.
| Scenario | Part 1 (Copy Fill) | Part 2 (Auto-Increment) |
|---|---|---|
| Start = 10, drag 5 rows | 10, 10, 10, 10, 10 | ✓ 10, 11, 12, 13, 14 |
| Start = "Mumbai", drag 3 rows | Mumbai, Mumbai, Mumbai | Mumbai, Mumbai, Mumbai |
| Save to DB after drag | ✓ Works | ✓ Works (same PL/SQL) |
| CSS changes needed | None | None |
| PL/SQL changes needed | None | None |
Complete guide from scratch
Make sure your IG source query is ready
No change from Part 1. A simple SELECT from your table. The region Static ID must be my_ig since the JavaScript references it directly.
SELECT ROW_ID,
EMPLOYEE_NAME,
MANUAL_VALUE,
PHONE_NUMBER,
CITY,
PIN_CODE
FROM EMPLOYEES;Add checkname CSS class to columns in Page Designer
In APEX Page Designer, open each column you want drag-fill to support. Find the CSS Classes field under Column Attributes and type checkname. Only columns with this class respond to the drag gesture.
One issue I faced: I forgot to add the class to one column and spent a few minutes wondering why that column was not incrementing. Always double check the CSS Classes field after saving in Page Designer.
Replace the Execute When Page Loads JavaScript
This is the only real change from Part 1. Three things are new: we track startRowIndex on mousedown, we detect if the value is a number using isNumeric, and on mousemove we calculate startValue + offset instead of copying the same value. Text columns still copy as before.
Why getRowIndex matters: The APEX IG model does not give you a direct row number. We get it by finding the position of the current TR inside its TBODY using jQuery's .index(). Subtract the start index to get the offset.
$(document).ready(function () {
let draggedValue = null;
let draggedColumn = null;
let startRowIndex = null;
let isNumeric = false;
function getRowIndex($row) {
return $row.closest("tbody").find("tr").index($row);
}
$(document).on("mousedown", ".checkname", function (event) {
let $cell = $(this);
let $row = $cell.closest("tr");
let ig$ = apex.region("my_ig").widget();
let model = ig$.interactiveGrid("getViews", "grid").model;
let record = model.getRecord($row[0].dataset.id);
let columnIdx = $cell.index();
let view = ig$.interactiveGrid("getViews", "grid");
let config = view.modelColumns;
let columnName = Object.keys(config)[columnIdx];
if (columnName) {
let val = model.getValue(record, columnName);
draggedValue = val;
draggedColumn = columnName;
startRowIndex = getRowIndex($row);
isNumeric = !isNaN(parseFloat(val)) && val !== "" && val !== null;
}
event.preventDefault();
});
$(document).on("mousemove", "tr", function () {
if (draggedValue !== null && draggedColumn !== null) {
let $row = $(this);
let ig$ = apex.region("my_ig").widget();
let model = ig$.interactiveGrid("getViews", "grid").model;
let record = model.getRecord($row[0].dataset.id);
let currentIndex = getRowIndex($row);
if (record) {
let fillValue;
if (isNumeric) {
let offset = currentIndex - startRowIndex;
fillValue = parseFloat(draggedValue) + offset;
} else {
fillValue = draggedValue;
}
model.setValue(record, draggedColumn, String(fillValue));
}
}
});
$(document).on("mouseup", function () {
draggedValue = null;
draggedColumn = null;
startRowIndex = null;
isNumeric = false;
});
});CSS stays exactly the same as Part 1
The drag handle indicator on .checkname::after and all other rules are unchanged. Paste this in your page Inline CSS section if you are starting fresh.
.checkname {
position: relative;
padding-right: 25px;
}
.checkname::after {
content: "^";
font-size: 16px;
font-weight: bold;
color: #db3338;
position: absolute;
top: 50%;
right: 5px;
transform: translateY(-50%);
background: white;
border-radius: 50%;
width: 18px;
height: 18px;
display: flex;
align-items: center;
justify-content: center;
box-shadow: 0 0 3px rgba(0,0,0,0.3);
cursor: pointer;
}PL/SQL process is unchanged from Part 1
Your existing DML process handles everything automatically. After the user drags and increments values, APEX marks those rows as dirty. Clicking Save sends each dirty row through this process with the already-incremented bind variable values.
BEGIN
CASE :APEX$ROW_STATUS
WHEN 'C' THEN
INSERT INTO EMPLOYEES (
ROW_ID, EMPLOYEE_NAME, MANUAL_VALUE,
PHONE_NUMBER, CITY, PIN_CODE
)
VALUES (
:ROW_ID, :EMPLOYEE_NAME, :MANUAL_VALUE,
:PHONE_NUMBER, :CITY, :PIN_CODE
);
WHEN 'U' THEN
UPDATE EMPLOYEES
SET EMPLOYEE_NAME = :EMPLOYEE_NAME,
MANUAL_VALUE = :MANUAL_VALUE,
PHONE_NUMBER = :PHONE_NUMBER,
CITY = :CITY,
PIN_CODE = :PIN_CODE
WHERE ROW_ID = :ROW_ID;
WHEN 'D' THEN
DELETE FROM EMPLOYEES
WHERE ROW_ID = :ROW_ID;
END CASE;
END;Key point: The bind variable values going into the process already contain the incremented numbers because the JS set them in the IG model before Save was clicked. APEX passes whatever is in the model directly to PL/SQL.
Full flow from drag to database
Once the drag is done, saving is fully automatic through APEX's own change tracking. Here is what happens end to end when a user drags on MANUAL_VALUE starting from 10.
Key point: We only changed JavaScript. The APEX model handles dirty-row tracking automatically, which means Save button activation and row-level DML all work without any extra PL/SQL code.
Lessons from building this
Region Static ID must match
The JS calls apex.region("my_ig"). If your Static ID in Page Designer is different you will get a silent error and nothing works.
isNumeric prevents text issues
Without the number check, dragging a text column like "Mumbai" would try to add an offset and produce NaN. The guard keeps text columns in copy-only mode.
Bind variable names must match
In the PL/SQL process every :BIND_VAR must match the column name exactly including case. A mismatch fails silently on Save.
checkname class controls columns
Only columns with the checkname CSS class respond to drag. You choose exactly which columns support increment.
Working and the client loves it 🎉
Drag-fill now increments exactly like Excel. APEX handles the save automatically. Zero bug reports since going live.
Comments
Post a Comment