Advanced Features

A facet is a collection of formulas. The formulas in a facet will form a library of calculations that ‘do a job’, that ‘job’ may involve calculating new column entries from the table data and/or aggregating the table data.

As an example, consider an invoice, it will have at least three columns; item, quantity, cost. In your library of facets you may have an invoice facet that calculates the subtotal of each line (quantity * cost) and finally a table aggregate that adds all the subtotals to give a grand total.

const invoice = function(table, row) { 
    /*
    This statement is executed for every row and will calculate the row subtotal.
    The subtotal column doesn't need to be defined prior to the facet being used - the new column
    will be automatically created and populated for each row.
    */
    row.subtotal = () => row.quantity * row.cost;

    /*
    This is a table aggregate, it is only calculated once after all rows have had their subtotal calculated.
    It uses the 'column' method of the context to get all the subtotal values from the rows as an array 
    and then passes them to a 'SUM' function.
    */
    table.grand_total = () => SUM(this.column('subtotal'));
}

A table may use multiple facets from your library. In this way you can mix and match the facets used to do calculations that is task dependent. So, if for instance, you want to see the gross margins for your invoice you can simply add the ‘gross margin’ facet from your library.

Read about what the differences between noSheet and a spreadsheet library are here.

Data Populations

Rows within a noSheet table exist in populations, if you don’t define a population when you load data, it will be assigned to the default population

table.load(data, 'lineitems' /*define the population for these rows*/);

What are populations for?

Populations give you a finer grained way of manipulating data in the table by:

Loading / unloading data before a calculation is done using hooks.
Querying rows by populations in facets using the columns method.

Querying table rows

You can use the column/columns context method inside facets to query data.

const facet1 = function(table, row) { 
    table.total = () => SUM(this.columns('column1', 'column2')); 
}

The columns(s) context methods will verify the column parameters to make sure that you are not selecting from columns that haven’t been calculated yet see formula precedents and dependants

The column/columns methods have a basic psuedo SQL SELECT fluent syntax allowing you select:

from one or more populations
where you can define your own filter conditions

const facet1 = function(table, row) { 
    this.de_minimis = () => SUM( tables('quantity').from('imported').where( r => r.value <= 800 ) );      
}

Stacks

You can define a stack of tables using the defineStack function.

A stack is a group of tables that all share the same facets but which have different data.

When you add tables to a stack you can identity them with tags:

let stack = defineStack('item', 'quantity', 'cost'),
    invoice_1 = stack.createTable('site1', 'east'),
    invoice_2 = stack.createTable('site2', 'east'),
    invoice_3 = stack.createTable('site3', 'west');

By using a stack and table tags you can:

Dynamically add/remove tables

let invoice_4 = stack.createTable('site4', 'south');

stack.tables('east').detach();

Query table aggregate data

 //return an array of grand_total aggregates for all the tables tagged with 'west'
let grand_totals = stack.tables('west').aggregates('grand_total');

Selectively calculate tables

stack.tables('east').calculate();

Selectively consolidate tables

Consolidation

A stack gives you ability to consolidate tables using facets.

Lets say you have stack of multiple invoices:

let stack = defineStack('item', 'quantity', 'cost'),
    invoice_1 = stack.createTable('east'),
    invoice_2 = stack.createTable('east'),
    invoice_3 = stack.createTable('west');

    stack.addFacets(invoice);

You consolidation facets could look like this:

/**
 * Consolidate table stack invoices
 * @param {*} columns   Table columns query object
 */
const all_invoices = function(columns) {
    this.total  = () => SUM( ...columns('subtotal') );
};

stack.consolidate(all_invoices);
stack.calculate();

let total = stack.total,
    east  = stack.aggregates(['east']).total,
    west  = stack.aggregates(['west']).total;

References

References allow you to define facets that require external data in order to function:

const currency_exchange = function(table, row) { 

    row.Euro = () => row.USD * ref.exchange_rate; 
}

let order = createTable(['item', 'quantity', 'cost'], invoice, currency_exchange);

order.setReferences( function() {
    //Define a 'pull' reference that will be used whenever the facet is calculated
    this.exchange_rate = 0.87;
});

References can also be ‘push’ references that force a table recalculation whenever they are changed

let handle = order.getReferenceHandle('exchange_rate', 0.87);

handle.value = 0.88; //this statement will force a recalculation of the order table

Table references

References can also be aggregates from other tables

order.setReferences( function(uses) {
    //define a reference based on another tables aggregate, if the 'another_no_sheet_table' is recalculated then
    //the order table will be recalculated
    this.exchange_rate = uses(another_no_sheet_table).average_exchange_rate;
});

Stack References

Stacks can have default references which can then be overriden based on table tags:

//This default reference will be used by all tables - even tables added to the stack later.
stack.setDefaultReferences( function(uses) {
    this.exchange_rate = uses(another_no_sheet_table).average_exchange_rate;
});   

//Any table tagged with 'old-invoices' will now use a different exchange rate reference in it's facet calculations.
stack.tables('old-invoices').setReferences( function(uses) {
    this.exchange_rate = old_exchange_rate;
});

Consolidation references

Consolidations facets can use references too.

const all_invoices = function(columns, refs) {
    this.total  = () => SUM( ...columns('subtotal') ) * refs.markup;
};

stack.consolidate(all_invoices);

stack.setConsolidationReferences( function(uses) {
    this.markup = 1.25;
});

Facets can reference consolidation aggregates

const consolidate = function(tables) {
    this.grand_total = () => SUM( tables.total );
};

stack1.consolidate(consolidate);

stack2.tables().setReferences( function(uses) {

    this.reference = uses(stack1).grand_total;
});

Hooks

You can define hooks on tables or stacks that get called:

Before or After Calculations

table.beforeCalculate( theTable => ... );
stack.tables().beforeCalculate( aTable => ... );

table.afterCalculate( theTable => ... );
stack.tables().afterCalculate( aTable => ... );

beforeCalculate hooks are good becuase they allow you to preprocess the table data before each calculation, as an example consider a Bill Of Materials where you may need to add/remove additional rows based on the data in the table:

table.beforeCalculate( theTable => {

    //remove all rows in the 'fixings' population
    theTable.unload('fixings');

    //find all quantities where the length of any entry in the 'panels' population is greater than 1200mm
    //and add in a matching row for fixings
    theTable.select('quantity').from('panels').where( r => r.length > 1200 ).forEach( (quantity) => theTable.load([['sku001', quantity, 2.3]], 'fixings') );
});

Before and After Consolidation

stack.beforeConsolidation( theStack => ... );        
stack.afterConsolidation( theStack => ... );