Multiple Input/Output ETL: duplicate rows in same batch not resolving to latest record

[HarishKumar6668]

Hi all,

I'm using Integration Hub ETL with a Multiple Input/Output Script transform to import software packages into cmdb_ci_spkg, and my goal is to keep only the record with the latest u_created_datetime when duplicate software names come in across multiple import runs.

(function(batch, output) {
    for (var i = 0; i < batch.length; i++) {
        var softwareName = batch[i].u_display_name;
        var createdDate = batch[i].u_created_datetime;
        var impVersion;
        softwareName = softwareName.trim();
        var uniqueName;

        if (!/^\[(App|Update)\]/.test(softwareName)) {
            uniqueName = softwareName;
            impVersion = '';
        } else {
            var parts = softwareName.split(' - ');
            if (parts.length < 3) {
                uniqueName = softwareName;
                impVersion = '';
            } else {
                uniqueName = parts.slice(1, -1).join(' - ').trim();
                impVersion = parts[parts.length - 1].trim();
            }
        }

        var spkgGR = new GlideRecord('cmdb_ci_spkg');
        spkgGR.addQuery('name', uniqueName);
        spkgGR.setLimit(1);
        spkgGR.query();
        var existingDate, existingVersion;
        if (spkgGR.next()) {
            existingDate = spkgGR.getValue('u_created_date');
            existingVersion = spkgGR.getValue('version');
        }

        var createdDateObj = new GlideDateTime(createdDate);
        if (existingDate) {
            var existingDateObj = new GlideDateTime(existingDate);
            if (existingDateObj.after(createdDateObj)) {
                output[i].u_transformed_created_datetime = existingDate;
                output[i].u_transformed_version = existingVersion;
            } else {
                output[i].u_transformed_created_datetime = createdDate;
                output[i].u_transformed_version = impVersion;
            }
        } else {
            output[i].u_transformed_created_datetime = createdDate;
            output[i].u_transformed_version = impVersion;
        }
        output[i].u_transformed_display_name = uniqueName;
    }
})(batch, output);

The problem:

When multiple staging rows for the same software name exist within the same batch execution, the final record in cmdb_ci_spkg does not end up with the row that has the latest u_created_datetime. Instead it seems to pick an arbitrary row from the batch.

For example, for "7-Zip ARM" I have 5 staging rows with dates ranging from 2026-03-03T12:46:15Z to 2026-06-27T09:52:41Z (the latest). But the resulting cmdb_ci_spkg record ended up with version 26.00 and date 2026-03-03T12:46:56Z — a row that is neither the earliest nor the latest by timestamp. Screenshots of the staging data (Image 1) and the resulting target record (Image 2) attached.

Assumption:

All rows within a batch are passed to the script together. However, the spkgGR.query() executed inside the loop can only retrieve records that were already committed to the cmdb_ci_spkg table before the batch processing started. It does not see the other rows from the same batch that are being processed within the loop, because the values assigned to the output[] array are applied to the target table only after the entire script execution is completed.

Therefore, when multiple rows in the same batch have the same software name, each iteration may see the same existing state in cmdb_ci_spkg. The final value in the target can consequently depend on the order in which the platform applies the transformed output[] records, rather than necessarily being the record with the latest u_created_datetime.

Is this understanding correct — that  the output[] array is only applied to the target table after the entire script finishes, so GlideRecord queries inside the loop cannot see writes from other rows in the same batch(Only retrieves the record that is already present)?

Thanks in advance for any clarification.

Regards,
Harish