items.Average

// Carry over the average with UserData
if (!context.UserData.average) {
    context.UserData.average = items.Average(context.SiteId, 'NumA');
}
// Change the row color by comparing with the average
if (context.UserData.average < model.NumA) {
    model.ExtendedRowCss = 'high';
} else {
    model.ExtendedRowCss = 'low';
}

const HIERARCHY_CONFIG = [
    // Level 1 (from the lowest table up one level)
    {
        childSiteName: 'Source table name',
        linkColumn:    'Name of the link column to the parent',
        operations: [
            { 
                aggregateType: AGGREGATE_TYPE.SUM, 
                sourceColumn: 'Column name to aggregate', 
                writeColumn: 'Column name to write to' 
            },
        ],
    },
    // Level 2 (up one more level)
    {
        childSiteName: 'Source table name',
        linkColumn:    'Name of the link column to the parent',
        operations: [
            {
                aggregateType: AGGREGATE_TYPE.SUM, 
                sourceColumn: 'Column name to aggregate', 
                writeColumn: 'Column name to write to' },
        ],
    },
    // Add as many levels as needed after this
];

{
    childSiteName: 'Work results',
    linkColumn:    'ClassA',
    operations: [
        { 
            aggregateType: AGGREGATE_TYPE.SUM,     
            sourceColumn: 'NumA', 
            writeColumn: 'NumA' 
        }, // Total
        { 
            aggregateType: AGGREGATE_TYPE.AVERAGE, 
            sourceColumn: 'NumA', 
            writeColumn: 'NumB' 
        }, // Average
        { 
            aggregateType: AGGREGATE_TYPE.COUNT,
            writeColumn: 'NumD' 
        }, // Count
    ],
},

// ============================================================
// Aggregation type constants
// ============================================================
const AGGREGATE_TYPE = {
    COUNT: 'count',
    SUM: 'sum',
    AVERAGE: 'average',
    MIN: 'min',
    MAX: 'max',
};
// ============================================================
// Each element of the array represents one "level".
// Even if multiple operations are listed at the same level, the parent record is retrieved and updated only once.
// To add or change levels, only this definition needs to be modified.
// ============================================================
const HIERARCHY_CONFIG = [
    // Level 1: Work results → Project
    {
        childSiteName: 'Work results',
        linkColumn: 'ClassA',
        operations: [
            {
                aggregateType: AGGREGATE_TYPE.SUM,
                sourceColumn: 'NumA',
                writeColumn: 'NumA',
            },
        ],
    },
    // Level 2: Project → Department
    {
        childSiteName: 'Project',
        linkColumn: 'ClassA',
        operations: [
            {
                aggregateType: AGGREGATE_TYPE.SUM,
                sourceColumn: 'NumA',
                writeColumn: 'NumA',
            },
        ],
    },
    // To extend to level 3 or higher, add it here
];
// ============================================================
// Get the site ID (with cache)
// ============================================================
const _siteIdCache = new Map();
function getSiteIdOrNull(siteName) {
    if (_siteIdCache.has(siteName)) return _siteIdCache.get(siteName);
    const site = items.GetClosestSite(siteName);
    if (!site || !site.SiteId) {
        logs.LogUserError(
            `Failed to get the site information: ${siteName}`,
            getSiteIdOrNull.name,
        );
        return null;
    }
    _siteIdCache.set(siteName, site.SiteId);
    return site.SiteId;
}
// ============================================================
// Dispatch by aggregation type
// ============================================================
function aggregate(type, siteId, sourceColumn, view) {
    switch (type) {
        case AGGREGATE_TYPE.COUNT:
            return items.Count(siteId, view) || 0;
        case AGGREGATE_TYPE.SUM:
            return items.Sum(siteId, sourceColumn, view) || 0;
        case AGGREGATE_TYPE.AVERAGE:
            return items.Average(siteId, sourceColumn, view) || 0;
        case AGGREGATE_TYPE.MIN:
            return items.Min(siteId, sourceColumn, view) || 0;
        case AGGREGATE_TYPE.MAX:
            return items.Max(siteId, sourceColumn, view) || 0;
        default:
            logs.LogUserError(`Unknown aggregateType: ${type}`, aggregate.name);
            return null;
    }
}
// ============================================================
// Run the hierarchical summary
// ============================================================
function runHierarchySummary(config, startParentId) {
    let currentParentId = startParentId;
    for (const level of config) {
        if (!currentParentId) {
            logs.LogUserError(
                'Stopping the processing because the parent ID could not be retrieved',
                runHierarchySummary.name,
            );
            break;
        }
        const siteId = getSiteIdOrNull(level.childSiteName);
        if (!siteId) break;
        // Get the parent record only once per level
        const parent = Array.from(items.Get(Number(currentParentId)))[0];
        if (!parent) {
            logs.LogUserError(
                `Failed to get the parent record: ID=${currentParentId}`,
                runHierarchySummary.name,
            );
            break;
        }
        // Aggregate all operations at the same level and set the values in the parent record
        let hasError = false;
        for (const op of level.operations) {
            const view = {
                View: {
                    ColumnFilterHash: {
                        [level.linkColumn]: `["${currentParentId}"]`,
                    },
                },
            };
            const total = aggregate(
                op.aggregateType,
                siteId,
                op.sourceColumn,
                JSON.stringify(view),
            );
            if (total === null) {
                hasError = true;
                break;
            }
            parent[op.writeColumn] = total;
        }
        if (hasError) break;
        // After all operations are complete, update the parent record only once
        parent.Update();
        // To the next level: follow linkColumn to get the parent's parent ID
        currentParentId = parent[level.linkColumn];
    }
}
// ============================================================
// Entry point
// ============================================================
runHierarchySummary(HIERARCHY_CONFIG, model.ClassA);