Skip to content

items.Count

Overview

This is the "Count Method" of the "Items Object". It counts the number of records in the specified table. You can specify selection conditions to narrow down the records to be counted.

Syntax

items.Count(siteId, view)

Parameters

Parameter Type Required Description
siteId object Yes Specify the site ID of the target table
view string No Specify the conditions for the records to select

Return Value

Return the number of records.

Usage Example 1

The following example counts the number of records registered in the table with site ID 2.

JavaScript
let count = items.Count(2);

Usage Example 2

The following example counts the number of records registered in the table with site ID 2 and with a Status of 900 (completed).

JavaScript
let view = {
    "View": {
        "ColumnFilterHash": {
            "Status": "[\"900\"]"
        }
    }
};
let count = items.Count(2, JSON.stringify(view));
context.Log(count);

Code Samples

1. Summary processing over three or more levels
Overview

This implements summary processing over three or more levels, which cannot be achieved with the standard functions. In this sample, three tables (Work results → Project → Department) are prepared, and the actual man-hours are summarized from the task level → the project level → the department level.

The server script is set on the Work results table, which is the grandchild.

Tables used

Prepare tables like the following.

  • Department table
Column Display name Remarks
Title Department
NumA Total monthly man-hours (hours) Summed into
NumB Monthly budget man-hours (hours) Entered manually
  • Project table
Column Display name Remarks
Title Project
ClassA Department Link column to the Department table
NumA Total project man-hours (hours) Summed into
  • Work results table
Column Display name Remarks
Title Task name
ClassA Project Link column to the Project table
NumA Working time (hours) Input value

The sample code controls processing by the site names "Project table" and "Work results table", so adjust them to match your actual table settings.

HIERARCHY_CONFIG specification

HIERARCHY_CONFIG is a definition that controls all the behavior of the hierarchical summary. By editing only this array, you can freely customize the table structure, the aggregation, and the number of levels.

  • Structure
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
];
  • Properties of a level
Property Type Required Description
childSiteName string ✓ Site name of the source table. Resolved with items.GetClosestSite()
linkColumn string ✓ Name of the link column with which the child table points to the parent record. Also used to go on to the next level
operations array ✓ List of aggregation operations performed at the same level (1 or more)
  • Properties of operations
Property Type Required Description
aggregateType Constant ✓ Aggregation type. Use the AGGREGATE_TYPE constants
sourceColumn string △ Name of the numeric column to aggregate. Can be omitted for AGGREGATE_TYPE.COUNT
writeColumn string ✓ Name of the column in the parent table to which the aggregation result is written
  • AGGREGATE_TYPE constants
Constant Processing sourceColumn
AGGREGATE_TYPE.COUNT Counts the records Optional
AGGREGATE_TYPE.SUM Totals a numeric column Required
AGGREGATE_TYPE.AVERAGE Averages a numeric column Required
AGGREGATE_TYPE.MIN Gets the minimum of a numeric column Required
AGGREGATE_TYPE.MAX Gets the maximum of a numeric column Required
Behavior
  • Write the array in order from the lower tables to the upper tables
  • linkColumn serves both as "the column with which the child narrows down to the parent" and "the column to follow from the parent to the next parent ID"
  • If an error occurs at any level, the processing of the subsequent levels is stopped
Example setting: performing multiple aggregations at the same level
{
    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
    ],
},
JavaScript

Condition: After create, After update

// ============================================================
// 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);

Cautions

This is a method used in "Server Script". It cannot be used in "Script".