RepeatingSum
From Roll20 Wiki
Main Article: Sheet Worker Scripts
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.
Contents |
repeatingSum Function
Include the following function - without changes - in the sheet worker script section of your character sheet.
The Code
/* ===== 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.replace(/\s/g, '').split(',')];
if (!Array.isArray(fields)) fields = [fields.replace(/\s/g, '').split(',')];
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);
});
});
};
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:item_weight 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_buffs remove:repeating_buffs', 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 (enclosed by square brackets [ ]).
Then notice in the fields, there are 6 corresponding attributes, that each match one of the destinations, in the same order, followed by 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 check or uncheck those boxes so only one, both, or neither buffs are active.
The attributes listed in fields must be in the same order as the destination fields they match. Any extra fields (like buff_active) are applied to all of the destinations.
repeatingSum Version 2
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 any number of columns in a repeating section. (This has been backported into the above function.)
- 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.
the Code
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. The additional attributes are just listed after the fields section, separated by commas.
However, your coins probably aren't listed in pounds, so it would be handy to multiply them by a weight factor.
Multipliers and Subtractions
Multiplying Bonus Attributes
You can multiply bonus attributes using either decimals or fractions. Imagine in your game, you get 20 gp to a pound, and 30 sp, and 50 cp respectively.
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');
});
Note: When using fractions, never use the form 1 1/2 for 1.5; use 3/2 instead.
Multiplying Specific Rows
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 change:repeating_encumbrance:item_cost 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.
Notice that when using multiple fields from the repeating section, they have to be put inside array brackets [ ], but the extra attributes and any additional properties are just added after the fields, separated by commas. They can be in any order.
Adding Specific Rows
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 change:repeating_encumbrance:item_cost 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.
Multiplying the Repeating Section
The previous operations have been applied to bonus attributes, those that are added to the repeating section. But you can multiply the repeating section total itself. You do this just by adding the multiply property.
on('change:repeating_encumbrance:item_weight change:repeating_encumbrance:item_cost remove:repeating_armour', function() {
repeatingSum(['total_weight', 'total_cost'], "encumbrance",['item_weight','item_cost'], 'multiply: 2');
});
The above will total up item weight and cost, and then double the final total. You can apply the multiplier to specific columns.
on('change:repeating_encumbrance:item_weight change:repeating_encumbrance:item_cost remove:repeating_armour', function() {
repeatingSum(['total_weight', 'total_cost'], "encumbrance",['item_weight','item_cost'], 'multiply: total_weight|2/3');
});
This multiplies the weight column by 2/3, and leaves the cost column unchanged.
Subtractions
Sometimes its handy to be able to express a repeating section as a subtraction In Shadowrun, for example, characters buy cyberware, and each reduces the Essence attribute. The more cyberware you get, the lower your essence becomes. By using a multiplier of -1, and adding an external attribute, Essence, we can manage that:
on('change:repeating_cyberware:essence_cost change:repeating_cyberware:item_cost remove:repeating_cyberware', function() {
repeatingSum(['essence_score', 'total_cost'], "cyberware",['essence_cost','item_cost'], 'essence_base: essence_cost', multiply:essence_cost|-1);
});
Here we have a repeating section that totals up the essence_cost and item_cost columns.
The essence_cost column is totalled up, multiplied by -1 and added to a bonus attribute: essence_base. This is saved to the characters essence_score attribute.
The item cost is totalled up and saved to total_cost, so you can see just how much money you can get by killing this character and pillaging their cyberware...
Rounding Totals
Being able to round totals is very handy. You can apply round, ceil, or floor to the final totals, in whatever digits you want, applying different rounding to each column. Here's a worker to calculate the mass, volume, power requirements, and cost of various parts of a starship. Mass and volume are both rounded to thousandths, power to whole numbers, and cost to whole millions.
on('change:repeating_starship:mass change:repeating_starship:volume change:repeating_starship:power change:repeating_starship:cost remove:repeating_starship', function() {
repeatingSum(['starship_mass', 'starship_volume', 'starship_power', 'starship_cost'],
'starship', ['mass:round|3','volume:round|3', 'power:round', 'cost:round|-6']);
});
If you just wanted round off the final total to thousandths, it's a lot simpler:
on('change:repeating_starship:mass change:repeating_starship:volume change:repeating_starship:power change:repeating_starship:cost remove:repeating_starship', function() {
repeatingSum(['starship_mass', 'starship_volume', 'starship_power', 'starship_cost'],
'starship',['mass','volume', 'power', 'cost6'], 'round: 3');
});
You can apply round, ceil, and floor functions to individual columns, or to the whole section.
Dont combine them: if you apply rounding to a specific column, you cant use the non-specific rounding modifier, . In that case, any rounding you want to apply must be applied individually to each column.
Syntax Summary
Here's a summary of the function's syntax, in the same format as the code at the top of the page.
/* ===== PARAMETERS ==========
destinations = the name of the attribute that stores the total quantity
section = name of repeating fieldset, without the repeating_
fields = the name of the attribute field to be summed
can be a single attribute: 'weight'
or an array of attributes: ['weight','number','equipped']
extras: everything after the fields parameter is optional and can be in any order:
'ceil'
'round'
'floor'
'ceil: 3'
'round: -2'
'round: equipment_weight, equipment_cost|2
you want to round the final total.
If you supply a field name, it will round just that total. You can supply multiple fields, separated by commas.
If you supply a number, it will round to that many digits.
round:1 rounds to tenths; floor:-3 rounds down to thousands, so 3567 would be shown as 3000.
If you dont supply a number, it assumes 0, and returns an integer (whole numbers).
IMPORTANT: if you list ANY field, then ALL fields to be rounded must be specifically stated.
Don't do this: floor:equipment_weight|2, round,
'multiplier: 2'
'multiplier:equipment_weight|2'
'multiplier: equipment_weight|2, equipment_cost|3'
Multiplier will apply a multiple to the final total. You can multiple all fields, or specific fields.
It doesnt apply to attributes being added from outside the repeating section.
Multiplier can be negative, representing a subtraction.
'an_attribute'
'an_attribute:-1'
'an_attribute:0.5'
'an_attribute:equipment_cost'
'an_attribute:equipment_cost|-1'
'an_attribute:equipment_cost|-1,equipment_weight|2'
You can also list attributes from outside the repeating section. Don't try to add attributes from other repeating sections.
by default, the listed attribute will be added to all fields.
You can list one or more fields, and it will only be added to those fields.
You can list a number: the attribute will be multiplied by that ammount. So -1 subtracts the attribute.
*/
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
See Also
- Sheet Worker Optimization by Scott C.
- How to integrate table of stats into a sheet -Forum post
- JavaScript Best Practices - MDN web docs
- Introduction to JavaScript - MDN web docs
