Consolidating Data from SFDC Opportunity Field History

I think your best bet might be to use a script for this, and to get this working you’d want to create the following fields:

1. A link to the same table that acts as a link to the predecessor record
2. A lookup field from that predecessor linked field to display the predecessor’s Edit Date value
3. A formula field to calculate the difference between the current record’s Edit Date and the predecessor’s Edit Date
4. A link to a table called ‘Opportunity’ where each record represents a single opportunity

This would give you something like this:

And in the Opportunity table you’d be able to create a rollup to display the total turnaround time:

 And here’s how it’d look in action and I’ve set it up here for you to check out!

And here’s the script:

// Links each record to its previous record within the same opportunity + field name group.
// Sort order is ascending by edit date.
//
// Required fields:
// - Opportunity name
// - Edit date
// - Field name
// - Predecessor date/record field: must be a linked-record field pointing to this same table

const config = input.config({
    title: "Link predecessors",
    description: "Groups records by opportunity name + field name, sorts by edit date, and links each record to its predecessor.",
    items: [
        input.config.table("table", {
            label: "Table",
        }),
        input.config.view("view", {
            label: "View",
            parentTable: "table",
        }),
        input.config.field("opportunityNameField", {
            label: "Opportunity name field",
            parentTable: "table",
        }),
        input.config.field("editDateField", {
            label: "Edit date field",
            parentTable: "table",
        }),
        input.config.field("fieldNameField", {
            label: "Field name field",
            parentTable: "table",
        }),
        input.config.field("predecessorField", {
            label: "Predecessor linked-record field",
            parentTable: "table",
        }),
    ],
});

const {
    table,
    view,
    opportunityNameField,
    editDateField,
    fieldNameField,
    predecessorField,
} = config;

const query = await view.selectRecordsAsync({
    fields: [
        opportunityNameField,
        editDateField,
        fieldNameField,
        predecessorField,
    ],
});

function getComparableValue(record, field) {
    const value = record.getCellValue(field);

    if (value == null) return "";

    if (typeof value === "string") return value;

    if (value.name) return value.name;

    if (Array.isArray(value)) {
        return value.map(item => item.name || item.id || String(item)).join(", ");
    }

    return String(value);
}

function getEditDate(record) {
    const value = record.getCellValue(editDateField);

    if (!value) return null;

    if (value instanceof Date) return value;

    const parsed = new Date(value);
    return isNaN(parsed.getTime()) ? null : parsed;
}

function getExistingLinkedId(record) {
    const linkedRecords = record.getCellValue(predecessorField);
    return linkedRecords?.[0]?.id || null;
}

const groups = new Map();

for (const record of query.records) {
    const opportunityName = getComparableValue(record, opportunityNameField);
    const fieldName = getComparableValue(record, fieldNameField);
    const editDate = getEditDate(record);

    if (!opportunityName || !fieldName || !editDate) continue;

    const groupKey = `${opportunityName}|||${fieldName}`;

    if (!groups.has(groupKey)) {
        groups.set(groupKey, []);
    }

    groups.get(groupKey).push({
        record,
        editDate,
    });
}

const updates = [];

for (const records of groups.values()) {
    records.sort((a, b) => a.editDate - b.editDate);

    for (let i = 0; i < records.length; i++) {
        const currentRecord = records[i].record;
        const predecessorRecord = i === 0 ? null : records[i - 1].record;

        const newLinkedValue = predecessorRecord
            ? [{ id: predecessorRecord.id }]
            : [];

        const existingLinkedId = getExistingLinkedId(currentRecord);
        const newLinkedId = predecessorRecord?.id || null;

        if (existingLinkedId !== newLinkedId) {
            updates.push({
                id: currentRecord.id,
                fields: {
                    [predecessorField.name]: newLinkedValue,
                },
            });
        }
    }
}

const updateCount = updates.length;

while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates.splice(0, 50);
}

output.text(`Done. Updated ${updateCount} records.`);

​@TheTimeSavingCo Thank you for this - will take me a minute to review and put in place - will let you know if I have any additional questions! Appreciate the help!