ESSEMBI HELP GUIDE

Drill Downs

Overview

Drill-downs allow users to navigate from a high-level view to a detailed view with context-specific filters applied. This creates an interactive and intuitive data exploration experience.

Basic Implementation Pattern

// Applied to charts/tables view
// 1. Get the value you want to filter on
const selectedValue = value; // Value from the clicked cell/row

// 2. Load the target view
const view = app.loadSheetView('WorkspaceName', 'ViewName');

// 3. Apply filters
view.addFilter('TableName', 'FieldName', 'Operator', value1, value2);

// 4. Set any additional options
view.addOption('OptionName', 'OptionValue');

// 5. Set a custom title if desired
view.name = `Custom Title`;

// 6. Return the view
return view;

Core Components

Loading a View

const view = app.loadSheetView('WorkspaceName', 'ViewName');
  • WorkspaceName: The name of the workspace containing the target view
  • ViewName: The name of the view to load

Adding Filters

view.addFilter('TableName', 'FieldName', 'Operator', value1, value2);
  • TableName: The name of the table containing the field
  • FieldName: The field to filter on
  • Operator: The comparison operator (see list below)
  • value1: Primary filter value
  • value2: Secondary filter value (required for some operators like 'Between')

Setting Options

view.addOption('OptionName', 'OptionValue');
  • Use to configure additional settings in the target view

Setting Custom Title

view.name = `Custom Title`;
  • Creates a descriptive title for the drilled-down view

Filter Operators

Basic Operators (All Data Types)

Operator Description Example
Equal Exact match (=) view.addFilter('Users', 'Status', 'Equal', 'Active')
NotEquals Not matching (!=) view.addFilter('Products', 'Category', 'NotEquals', 'Discontinued')

String Operators

Operator Description Example
Contains String contains value view.addFilter('Incident', 'Description', 'Contains', 'error')
StartsWith String starts with value view.addFilter('Incident', 'Code', 'StartsWith', 'ERR-')
EndsWith String ends with value view.addFilter('Incident', 'Reference', 'EndsWith', '-2024')

Numeric/Date Operators

Operator Description Example
GreaterThan Greater than (>) view.addFilter('Incident', 'Priority', 'GreaterThan', 2)
LessThan Less than (<) view.addFilter('Inventory', 'Stock', 'LessThan', 10)
GreaterThanOrEqual Greater than or equal (>=) view.addFilter('Performance', 'Score', 'GreaterThanOrEqual', 75)
LessThanOrEqual Less than or equal (<=) view.addFilter('Tasks', 'Priority', 'LessThanOrEqual', 3)
Between Between two values view.addFilter('Incident', 'Count', 'Between', 10, 20)

Date/Time Specific Operators

Operator Description Example
Overlaps For date ranges view.addFilter('Incident', 'StartDate', 'Overlaps', startDate, endDate)
InTheLastYear Last 12 months view.addFilter('Incident', 'StartDate', 'InTheLastYear')
InTheLastQuarter Last 3 months view.addFilter('Incident', 'StartDate', 'InTheLastQuarter')
InTheLastMonth Last month view.addFilter('Incident', 'StartDate', 'InTheLastMonth')
InTheLastWeek Last 7 days view.addFilter('Incident', 'StartDate', 'InTheLastWeek')
InTheNextYear Next 12 months view.addFilter('Incident', 'StartDate', 'InTheNextYear')
InTheNextQuarter Next 3 months view.addFilter('Incident', 'StartDate', 'InTheNextQuarter')
InTheNextMonth Next month view.addFilter('Incident', 'StartDate', 'InTheNextMonth')
InTheNextWeek Next 7 days view.addFilter('Incident', 'StartDate', 'InTheNextWeek')

User/Team Specific Operators

Operator Description Example
AssignedToMe Current user view.addFilter('Incident', 'AssignedTo', 'AssignedToMe')
AssignedToMyTeam Current user's team view.addFilter('Incident', 'AssignedTo', 'AssignedToMyTeam')
AssignedToTeam Specific team view.addFilter('Incident', 'AssignedTo', 'AssignedToTeam', ['Team Name'])

Accessing Field Values

When writing drill-down code, you have several ways to access field values:

1. Using the record object

// The record object is available in drill-down scripts
const fieldValue = record.getValue('FieldName');

2. Using the value variable

// The value variable contains the clicked cell's value
const fieldValue = value;

3. Using the result object

// The result object contains the full record data
const fieldValue = result.FieldName;

4. Accessing fields from specific tables

// Access fields from a specific table
const fieldValue = record.getFieldValue('TableName', 'FieldName');

Example: Drill-down using record values

// Applied to charts view
// Get the selected record
const selectedRecord = record;

// Check if record exists
if (!selectedRecord) {
    return;
}

// Get the Work Order Number from the Work Order table's Number field
const workOrderNumber = selectedRecord.getFieldValue('Work Order', 'Number');

// Check if work order number exists
if (!workOrderNumber) {
    return;
}

// Load the Incident Details view
const view = app.loadSheetView('Incidents', 'Incident Details');
if (!view) {
    return; // Handle case where view couldn't be loaded
}

// Apply filter using the record's Work Order Number
view.addFilter('Incident', 'WorkOrderRouting', 'Equal', workOrderNumber);

// Set a custom title for the drilled-down view
view.name = `Incident Details for Work Order #${workOrderNumber}`;

// Return the view
return view;

Complete Example with Multiple Field Access Methods

// Applied to charts view
// Get the selected value
const selectedValue = value;
if (!selectedValue) {
    return;
}

// Load the target view
const view = app.loadSheetView('MyWorkspace', 'DetailView');
if (!view) {
    return;
}

// Example of getting field values from the record
const recordId = record.getValue('Id');
const recordName = record.getValue('Name');
const recordDate = record.getValue('StartDate');

// Example of getting field values from the result object
const resultId = result.Id;
const resultName = result.Name;
const resultDate = result.StartDate;

// Calculate date range using the field value
const startDate = new Date(selectedValue);
const endDate = new Date(selectedValue);
endDate.setHours(23, 59, 59, 999);

// Apply filters using the field values
view.addFilter('Incident', 'StartDate', 'Overlaps', startDate, endDate)
    .addFilter('Incident', 'Id', 'Equal', recordId)
    .addFilter('Incident', 'Name', 'Contains', recordName);

// Set view options
view.addOption('Time Segment', 'Number of Days', 1)
    .addOption('Time Segment', 'End Date', selectedValue);

// Set custom title using field values
view.name = `${recordName} details for ${selectedValue.toLocaleDateString("en-US")}`;

return view;

Key Points About Field Value Access

  • The record object provides getValue() and getFieldValue() methods to access field values
  • The value variable contains the clicked cell's value
  • The result object provides direct access to field values as properties
  • Field values are automatically converted to the appropriate type (dates, numbers, strings, etc.)
  • These values can be used for filters, date calculations, custom titles, view options, and conditional logic

Data Type Examples

Date Filtering

// Applied to charts view
// Single date
const view = app.loadSheetView('Workspace', 'View');
view.addFilter('Incident', 'StartDate', 'Equal', new Date('2024-03-20'));

// Date range
const startDate = new Date('2024-03-01');
const endDate = new Date('2024-03-31');
view.addFilter('Incident', 'StartDate', 'Between', startDate, endDate);

// Date overlap (for start/end date pairs)
view.addFilter('Incident', 'StartDate', 'Overlaps', startDate, endDate);

// Relative date ranges
view.addFilter('Incident', 'StartDate', 'InTheLastMonth');
view.addFilter('Incident', 'StartDate', 'InTheNextWeek');

User Filtering

// Applied to charts view
// Current user context
view.addFilter('Incident', 'AssignedTo', 'AssignedToMe');
view.addFilter('Incident', 'AssignedTo', 'AssignedToMyTeam');

// Specific users (by name)
view.addFilter('Incident', 'AssignedTo', 'Equal', ['John Smith', 'Jane Doe']);

// Team filtering
view.addFilter('Incident', 'AssignedTo', 'AssignedToTeam', ['Team Name']);

String Filtering

// Applied to charts view
view.addFilter('Incident', 'Description', 'Contains', 'error');
view.addFilter('Incident', 'Code', 'StartsWith', 'ERR-');
view.addFilter('Incident', 'Reference', 'EndsWith', '-2024');

Numeric Filtering

// Applied to charts view
view.addFilter('Incident', 'Priority', 'GreaterThan', 2);
view.addFilter('Incident', 'Count', 'Between', 10, 20);

Special Fields

// System-level fields available for filtering
view.addFilter('Incident', 'Valid', 'Equal', true);      // Record validity
view.addFilter('Incident', 'Archived', 'Equal', false);  // Archive status
view.addFilter('Incident', 'Created', 'InTheLastMonth'); // Creation date/time
view.addFilter('Incident', 'CreatedBy', 'Equal', 'John'); // Creator
view.addFilter('Incident', 'PortalPublic', 'Equal', true); // Portal visibility

Complete Example with Multiple Filters

// Applied to charts view
// Get the selected value
const selectedValue = value;
if (!selectedValue) {
    return;
}

// Load the target view
const view = app.loadSheetView('MyWorkspace', 'DetailView');
if (!view) {
    return;
}

// Calculate date range
const startDate = new Date(selectedValue);
const endDate = new Date(selectedValue);
endDate.setHours(23, 59, 59, 999);

// Apply multiple filters
view.addFilter('Incident', 'StartDate', 'Overlaps', startDate, endDate)
    .addFilter('Incident', 'Status', 'Equal', 'Active')
    .addFilter('Incident', 'Priority', 'GreaterThan', 2)
    .addFilter('Incident', 'AssignedTo', 'AssignedToMyTeam')
    .addFilter('Incident', 'Description', 'Contains', 'critical');

// Set view options
view.addOption('Time Segment', 'Number of Days', 1)
    .addOption('Time Segment', 'End Date', selectedValue);

// Set custom title
view.name = `Critical Incidents for ${selectedValue.toLocaleDateString("en-US")}`;

return view;

Best Practices

1. Input Validation

Always check if required values exist before using them

if (!selectedValue) return;

2. Error Handling

Handle cases where views cannot be loaded

const view = app.loadSheetView('Workspace', 'View');
if (!view) {
    return; // Handle the case where view couldn't be loaded
}

3. Method Chaining

Use method chaining for cleaner code

view.addFilter('Table1', 'Field1', 'Equal', value1)
    .addFilter('Table2', 'Field2', 'Contains', value2)
    .addOption('Option1', 'Value1');

4. Descriptive Titles

Use informative titles that describe the filtered view

view.name = `Sales for ${region} - ${quarter}`;

5. Use Appropriate Operators

Select operators that match your data type and filtering needs

  • For dates, use Overlaps when dealing with start/end date pairs
  • For users, use AssignedToMe or AssignedToMyTeam for current context
  • For relative time ranges, use operators like InTheLastMonth

6. Date/Time Handling

Handle time zones appropriately for date/time fields

// Set end of day for date range
const endOfDay = new Date(selectedDate);
endOfDay.setHours(23, 59, 59, 999);

Troubleshooting

  • If your drill-down isn't working, verify workspace and view names
  • Check that field names match exactly in your filter calls
  • Ensure your operator is compatible with the field type
  • Verify that the values you're filtering on exist in the data
  • For date filters, check that the date format is valid
  • For user/team filters, verify that the specified users/teams exist
  • Use console logging to debug filter values during development

Real-World Examples

Example: Work Order to Incident Details

// Applied to Work Order table view
// Get the selected record
const selectedRecord = record;

// Check if record exists
if (!selectedRecord) {
    return;
}

// Get the Work Order Number from the Work Order table's Number field
const workOrderNumber = selectedRecord.getFieldValue('Work Order', 'Number');

// Check if work order number exists
if (!workOrderNumber) {
    return;
}

// Load the Incident Details view
const view = app.loadSheetView('Incidents', 'Incident Details');
if (!view) {
    return; // Handle case where view couldn't be loaded
}

// Apply filter using the record's Work Order Number
view.addFilter('Incident', 'WorkOrderRouting', 'Equal', workOrderNumber);

// Set a custom title for the drilled-down view
view.name = `Incident Details for Work Order #${workOrderNumber}`;

return view;

This example shows how to create a drill-down from a Work Order view to the Incident Details view, filtering by the work order routing field.

Back to help

Manufacturing efficiency starts here.

Schedule a demo to see how Essembi can rapidly help you drive efficiency at your manufacturing business.

Book a Demo