Revision as of 17:19, 18 June 2020

One request that crops up on the roll20 forums over and over again, is how can you add up all the items in a repeating section?

Say you have an inventory section, listing the items you are carrying, and you need their total weight. Or you have section listing all the coins of different types, and you want their values. Or a skill or power section, and you want the total character points used to buy them.

The sheet worker function below will do that for you. Some examples of how to use it are listed below.

repeatingSum Function

Include the following function - without changes - in the sheet worker script section of your character sheet.

/* ===== PARAMETERS ==========
destinations = the name of the attribute that stores the total quantity
        can be a single attribute, or an array: ['total_cost', 'total_weight']
        If more than one, the matching fields must be in the same order. 
    section = name of repeating fieldset, without the repeating_
    fields = the name of the attribute field to be summed
          destination and fields both can be a single attribute: 'weight'
          or an array of attributes: ['weight','number','equipped']
*/
const repeatingSum = (destinations, section, fields) => {
    if (!Array.isArray(destinations)) destinations = [destinations.split(',')];
    if (!Array.isArray(fields)) fields = [fields];
    getSectionIDs(`repeating_${section}`, idArray => {
        const attrArray = idArray.reduce((m, id) => [...m, ...(fields.map(field => `repeating_${section}_${id}_${field}`))], []);
        getAttrs([...attrArray], v => {
            const getValue = (section, id, field) => v[`repeating_${section}_${id}_${field}`] === 'on' ? 1 : parseFloat(v[`repeating_${section}_${id}_${field}`]) || 0;
            const commonMultipliers = (fields.length <= destinations.length) ? [] : fields.splice(destinations.length, fields.length - destinations.length);
            const output = {};
            destinations.forEach((destination, index) => {
                output[destination] = idArray.reduce((total, id) => total + getValue(section, id, fields[index]) * commonMultipliers.reduce((subtotal, mult) => subtotal * getValue(section, id, mult), 1), 0);
            });
            setAttrs(output);
        }); 
    }); 
};

Using The Function

Simple Example

Let's say you have an fieldset called repeating_inventory, and in that set you have fields item_name and item_weight. You want to sum all the weights, and show that in an attribute outside the fieldset named encumbrance_total.

You'd add the above function, and the following worker:

on('change:repeating_inventory remove:repeating_inventory', function() {
	repeatingSum("encumbrance_total","inventory","item_weight");
});

Weight * Number

Most inventory lists are a little more complicated. You might have an extra field named item_number. For instance, your equipment list might include:

item_name: bow, item_weight: 3, item_number: 1
item_name: arrows: item_weight: 0.1, item_number: 20

and so on. In this case, you'd use the following function:

on('change:repeating_inventory remove:repeating_inventory', function() {
	repeatingSum("encumbrance_total","inventory",["item_weight","item_number"]);
});

When using multiple inputs multiplied together, set them up as an array of attribute names, like: ['weight','number'], instead of 'weight'.

Conditional Sums (e.g. using a Checkbox)

You could use it for conditional items. Let's say you have a repeating_armour fieldset, with field names, armour_piece and armour_worn. Armour_worn is a checkbox. So, you can list a variety of armours, and decide which ones you are wearing by ticking the checkbox. The following function would total the worn armour pieces and add it to an armour_weight attribute, and ignore the armour not being worn.

on('change:repeating_armour remove:repeating_armour', function() {
	repeatingSum("armour_weight", "armour",["armour_piece","armour_worn"]);
});

Note: this works because the a checked Checkbox has a default value of 'on' when checked, and '0' when unchecked. This script treats any text value as a number value of 1, so even if you don't set a value for the checkbox in the html, this will work.

Multiplying More Than One Column

Sometimes you want to total up more than one thing in a repeating section, like weight and cost. Let's say you have a a set of powers, potions, and abilities that can each buff your stats. Each one might buff different combinations of stats. You want to track what the bonus for each is. You can do that a function like this:

on('change:repeating_armour remove:repeating_armour', function() {
    repeatingSum(
        ['total_str_mod', 'total_dex_mod', 'total_con_mod', 'total_int_mod', 'total_wiz_mod' 'total_cha_mod'], 
        "buffs", 
        ['str_mod', 'dex_mod', 'con_mod', 'int_mod', 'wiz_mod' 'cha_mod', 'buff_active']);
});

I've put the destination, section, and fields parameters each on different rows, so you can see them more easily. Notice the six destinations are in an array. Notice in the fields, there are 6 corresponding attributes in the field, and an extra attribute: buff_active is a checkbox, which lets you switch the buffs on or off. So if you have a buff that gave +2 to DEX and CON, and another buff that gave +2 to STR and DEX, with both active you'd get +2 STR, +4 DEX, +2 CON. But you can set so only one is active.

The attributes listed in fields must be in the same order as the destination fields they match. Any extra fields (like buff_active) apply to all of the destinations.

Advanced Features

The above function works for most people. But there are some features people often want, that can't be done directly. The massively expanded function below provides several expansions:

Sum the cost and weight of all items in a repeating section.
Add attributes from outside the repeating section.
Subtract the repeating section total from an outside attribute.
Round the final total to a specific number of decimal places.
multiply the total of a repeating section
calculate several different columns in the section, and apply different modifiers to each.

The code for this version is below, and the new features will be described beneath it.

const repeatingSum = (destinations, section, fields, ...extras) => {
    const isNumber = value => parseFloat(value).toString() === value.toString();
    const isOption = value => [...checks.valid, ...checks.roundtypes].includes(value);
    const isRounding = value => checks.roundtypes.includes(value);
    const isFraction = value => value.includes('/') && !(value.includes(',') || value.includes('|'));
    const getTrimmed = value => value.toLowerCase().replace(/\s/g, '');
    const getRounded = (type, value, pow) => (Math[type](value * Math.pow(10, pow)) / Math.pow(10, pow)).toFixed(Math.max(0, pow));
    const getFraction = (value) => /*{ console.log(`value: ${value}`); */
        parseInt(value.split('/')[0]) / parseInt(value.split('/')[1]);
    const getMultiplier = (value, rounding = 1) => 'undefined' === typeof value ? (rounding ? 0: 1) : (
        isNumber(value) ? parseFloat(value) : (isFraction(value) ? getFraction(value) : value));
    if (!Array.isArray(destinations)) destinations = [getTrimmed(destinations)];
    if (!Array.isArray(fields)) fields = [getTrimmed(fields)];
    const fields_trimmed = fields.map(field => getTrimmed(field).split(':')[0]);
    const subfields = fields_trimmed.slice(0,destinations.length);
    const checks = { valid: ['multiplier'], roundtypes: ['ceil', 'round', 'floor'] };
    let properties = {attributes: {}, options: {}};
    extras.forEach(extra => {
        const [prop, v] = getTrimmed(extra).split(':');
        const multiplier_maybe = getMultiplier(v, isRounding(prop));
        const obj = isNumber(multiplier_maybe) ? subfields.reduce((obj,field) => {
            obj[field] = multiplier_maybe;
            return obj;
        },{}) : multiplier_maybe.split(',').reduce((obj, item) => {
            const [stat, value] = item.split('|');
            const multiplier = getMultiplier(value, isRounding(prop));
            obj[stat] = multiplier;
            return obj;
        }, {});
        properties[isOption(prop) ? 'options' : 'attributes'][prop] = obj;
    });
    getSectionIDs(`repeating_${section}`, idArray => {
        const attrArray = idArray.reduce((m, id) => [...m, ...(fields_trimmed.map(field => `repeating_${section}_${id}_${field}`))], []);
        getAttrs([...attrArray, ...Object.keys(properties.attributes)], v => {
            const getValue = (section, id, field) => v[`repeating_${section}_${id}_${field}`] === 'on' ? 1 : parseFloat(v[`repeating_${section}_${id}_${field}`]) || 0;
            const commonMultipliers = (fields.length <= destinations.length) ? [] : fields.splice(destinations.length, fields.length - destinations.length);
            const output = destinations.reduce((obj, destination, index) => {
                let sumTotal = idArray.reduce((total, id) => total + getValue(section, id, fields_trimmed[index]) * commonMultipliers.reduce((subtotal, mult) => subtotal * ((!mult.includes(':') || mult.split(':')[1].split(',').includes(fields_trimmed[index])) ? getValue(section, id, mult.split(':')[0]) : 1), 1), 0);
                sumTotal *= (properties.options.hasOwnProperty('multiplier') && Object.keys(properties.options.multiplier).includes(fields_trimmed[index])) ? (parseFloat(properties.options.multiplier[fields_trimmed[index]]) || 0): 1;
                sumTotal += Object.entries(properties.attributes).reduce((total, [key, value]) => 
                    total += (value.hasOwnProperty(fields_trimmed[index]) ? parseFloat(v[key] || 0) * (parseFloat(value[fields_trimmed[index]]) || 1): 0) , 0);
                checks.roundtypes.forEach(type => {
                    if (properties.options.hasOwnProperty(type)) {
                        if (Object.keys(properties.options[type]).includes(fields_trimmed[index])) {
                            sumTotal = getRounded(type, sumTotal, (+properties.options[type][fields_trimmed[index]] || 0));
                        } else if (properties.options[type] == '0' || !isNaN(+properties.options[type] || 'x') ) {
                            sumTotal = getRounded(type, sumTotal, +properties.options[type]);
                        } 
                    } 
                });
                obj[destination] = sumTotal;
                return obj;
            }, {});
            setAttrs(output);
        }); 
    }); 
};

Adding Attributes From Outside the Section

Let's say you have an equipment total, and a coinage total, and you want to add in the weight of the coins to your encumbrance. You need a total_encumbrance and attributes for gp, sp, and cp. Then a separate total_weight attribute that adds these altogether. But with this function you can add them all at the same time.

on('change:repeating_encumbrance:item_weight remove:repeating_armour change:gp, change:sp, change:cp', function() {
	repeatingSum('total_weight', "encumbrance",['item_weight','gp', 'sp', 'cp']);
});

This example will total up the weight of all items carried, and all the coinage too and save to one destination, the total_weight attribute. However, your coins probably aren't listed in pounds, so it would be handy to multiply them by a weight factor.

Multipliers and Subtractions

Imagine in your game, you get 20 gp to a pound, and 30 sp, and 50 cp respectively. You can multiply those attributes using either decimals or fractions, as shown below.

on('change:repeating_encumbrance:item_weight remove:repeating_armour change:gp, change:sp, change:cp', function() {
	repeatingSum('total_weight', "encumbrance",['item_weight'], 'gp:1/20', 'sp:1/30', 'cp:1/50');
});

When using fractions, never use the form 1 1/2; use 3/2 instead.

What if you were totalling cost and weight, it doesnt make sense to add coinage to the cost column, So you can declare that an attribute applies to just specific fields, like this:

on('change:repeating_encumbrance:item_weight remove:repeating_armour change:gp, change:sp, change:cp', function() {
	repeatingSum(['total_weight', 'total_cost'], "encumbrance",['item_weight','item_cost'], 'gp:item_weight|1/20', 'sp:item_weight|1/30', 'cp:item_weight|1/50');
});

In this example, we are adding up both cost and weight. But we can see that GP, SP, and CP are totalled up with item_weight, and are ignored for the item_cost calculation.

If the player had a separate coinage weight attribute that already totalled up the coinage weight, that section would be simpler:

on('change:repeating_encumbrance:item_weight remove:repeating_armour change:coinage_weight', function() {
	repeatingSum(['total_weight', 'total_cost'], "encumbrance",['item_weight','item_cost'], 'coinage_weight:item_weight');
});

Here we have a single external attribute, that is added just to the weight column.

-- to come --

multiplying the repeating section
subtractions

Rounding Totals

Ultra Complex Example

Author: GiGs(G-G-G on github), with help from The Aaron, inspired by a script created by Finderski.

Related Pages

Sheetworker Examples for Non-programmers
Sheetworker Snippets - short, practical examples of sheetworker use
Universal Sheet Workers - How to create one function that can handle a bunch of similar sheet workers