Sheet worker to replace Autocalc (sheet worker help)

Hello! I am wondering if anyone might be able to help me with a sheetworker to replace an autocalc (which I understand will not add if all values are not filled). I am trying to add together the following attributes and have an autocalc that does seem to work (but only if every input is filled in with a number).  This is my autocalc:  (@{rationweight} + @{ingredientweight} + @{metalweight} + @{woodweight} + @{stringweight} + @{leatherweight} + (@{assetweight1} * @{assetquantity1}) + (@{assetweight2} * @{assetquantity2}) + (@{assetweight3} * @{assetquantity3}) + (@{assetweight4} * @{assetquantity4}) + (@{assetweight5} * @{assetquantity5}) + (@{assetweight6} * @{assetquantity6}) + (@{assetweight7} * @{assetquantity7}) + (@{assetweight8} * @{assetquantity8}) + (@{assetweight9} * @{assetquantity9}) + (@{assetweight10} * @{assetquantity10}) + (@{assetweight11} * @{assetquantity11}) + (@{assetweight12} * @{assetquantity12})) Does anyone know how to turn this into a sheet worker? I'd like the resulting total to be called "inventoryweight". (I will be taking a coding class next year, but for now I am very in the dark.) Any help would be extremely appreciated! Thanks!

Hi Winona, this should work (untested) It will calculate a new total anytime there's a change to any of the related attributes.  Make sure to remove HTML calcs you might have for "attr_inventoryweight". <script type="text/worker"> const primaryAssets = ['rationweight', 'ingredientweight', 'metalweight', 'woodweight', 'stringweight', 'leatherweight']; const secondaryAssets = ['assetweight1','assetquantity1','assetweight2','assetquantity2','assetweight3','assetquantity3','assetweight4','assetquantity4','assetweight5','assetquantity5','assetweight6','assetquantity6','assetweight7','assetquantity7','assetweight8','assetquantity8','assetweight9','assetquantity9','assetweight10','assetquantity10','assetweight11','assetquantity11','assetweight12','assetquantity12']; const allAttributes = [...primaryAssets, ...secondaryAssets]; const eventString = allAttributes.map((attr) => `change:${attr}`).join(' '); on(eventString, (eventInfo) => { getAttrs(allAttributes, (v) => { let primaryTotal = 0; let secondaryTotal = 0; // primary assets primaryAssets.forEach(function (attr) { const thisItem = parseInt(v[attr], 10) || 0; primaryTotal += thisItem; }); // secondary assets const weight = 'assetweight'; const quantity = 'assetquantity'; let count = 1; do { const thisWeight = parseInt(v[`${weight}${count}`], 10) || 0; const thisQuantity = parseInt(v[`${quantity}${count}`], 10) || 0; secondaryTotal += thisWeight * thisQuantity; count++; } while (count < 13); setAttrs( { inventoryweight: primaryTotal + secondaryTotal, }, {silent: true}, ); }); }); </script>

Hi again Vince! It seems to half work. All of the secondary totals for assets are working normally, but the primary inputs don't change anything. Could it be because they are the results of an autocalc. Do I need to completely remove these? Ex: a ration counts one to one, but ingredient, metal, wood, string, leather are multiplied by .2 to get their total weight. This is my code for those: <input name="attr_rationweight" value="(@{ration})" disabled="true" data-sm-id="30" type="number" class="sm-prop sm-num" style="left: 88px; top: 591px; width: 48px; height: 30px;"><input name="attr_ingredientweight" value="(@{ingredient} * .2)" disabled="true" data-sm-id="25" type="number" class="sm-prop sm-num" style="left: 87px; top: 686px; width: 48px; height: 30px;"><input name="attr_metalweight" value="(@{metal} * .2)" disabled="true" data-sm-id="26" type="number" class="sm-prop sm-num" style="left: 87px; top: 731px; width: 48px; height: 30px;"><input name="attr_woodweight" value="(@{wood} * .2)" disabled="true" data-sm-id="27" type="number" class="sm-prop sm-num" style="left: 87px; top: 774px; width: 48px; height: 30px;"><input name="attr_stringweight" value="(@{string} * .2)" disabled="true" data-sm-id="28" type="number" class="sm-prop sm-num" style="left: 87px; top: 817px; width: 48px; height: 30px;"><input name="attr_leatherweight" value="(@{leather} * .2)" disabled="true" data-sm-id="29" type="number" class="sm-prop sm-num" style="left: 87px; top: 862px; width: 48px; height: 30px;"> I am attempting to display the result using an autocalc as well: <input name="attr_inventorytotal" value="(@{inventoryweight})" disabled="true" data-sm-id="14" type="number" class="sm-prop sm-num" style="left: 251px; top: 358px; width: 60px; height: 29px;"> Any thoughts on how to get around this? Thanks again! vÍnce said: Hi Winona, this should work (untested) It will calculate a new total anytime there's a change to any of the related attributes.  Make sure to remove HTML calcs you might have for "attr_inventoryweight". <script type="text/worker"> const primaryAssets = ['rationweight', 'ingredientweight', 'metalweight', 'woodweight', 'stringweight', 'leatherweight']; const secondaryAssets = ['assetweight1','assetquantity1','assetweight2','assetquantity2','assetweight3','assetquantity3','assetweight4','assetquantity4','assetweight5','assetquantity5','assetweight6','assetquantity6','assetweight7','assetquantity7','assetweight8','assetquantity8','assetweight9','assetquantity9','assetweight10','assetquantity10','assetweight11','assetquantity11','assetweight12','assetquantity12']; const allAttributes = [...primaryAssets, ...secondaryAssets]; const eventString = allAttributes.map((attr) => `change:${attr}`).join(' '); on(eventString, (eventInfo) => { getAttrs(allAttributes, (v) => { let primaryTotal = 0; let secondaryTotal = 0; // primary assets primaryAssets.forEach(function (attr) { const thisItem = parseInt(v[attr], 10) || 0; primaryTotal += thisItem; }); // secondary assets const weight = 'assetweight'; const quantity = 'assetquantity'; let count = 1; do { const thisWeight = parseInt(v[`${weight}${count}`], 10) || 0; const thisQuantity = parseInt(v[`${quantity}${count}`], 10) || 0; secondaryTotal += thisWeight * thisQuantity; count++; } while (count < 13); setAttrs( { inventoryweight: primaryTotal + secondaryTotal, }, {silent: true}, ); }); }); </script>

You really don't want to mix HTML calcs with sheetworker calcs.  They can very easily get out-of-sync because of how they detect attribute changes.  It's best to convert all calcs to sheetworkers. If you have included all the HTML above for the relevant primary assets..., I might be able to update my worker to accommodate the calcs to handle those as well.

These are the primary assets I am referring to:  <input name="attr_ration" data-sm-id="19" type="number" class="sm-prop sm-num" style="left: 170px; top: 592px; width: 48px; height: 30px; font-size: 18px;"><input name="attr_ingredient" data-sm-id="20" type="number" class="sm-prop sm-num" style="left: 169px; top: 686px; width: 48px; height: 30px; font-size: 18px;"><input name="attr_metal" data-sm-id="21" type="number" class="sm-prop sm-num" style="left: 169px; top: 731px; width: 48px; height: 30px; font-size: 18px;"><input name="attr_wood" data-sm-id="22" type="number" class="sm-prop sm-num" style="left: 169px; top: 774px; width: 48px; height: 30px; font-size: 18px;"><input name="attr_string" data-sm-id="23" type="number" class="sm-prop sm-num" style="left: 169px; top: 817px; width: 48px; height: 30px; font-size: 18px;"><input name="attr_leather" data-sm-id="24" type="number" class="sm-prop sm-num" style="left: 169px; top: 862px; width: 48px; height: 30px; font-size: 18px;"> The ration attribute weight is the same as the number of rations (x1). The others (ingredient, metal, wood, string, leather) are multiplied by .2 to get the weight.  To display the total on the sheet, should I use an autocalc, or is there a better way? (@{inventoryweight}) Thanks again!! vÍnce said: You really don't want to mix HTML calcs with sheetworker calcs.  They can very easily get out-of-sync because of how they detect attribute changes.  It's best to convert all calcs to sheetworkers. If you have included all the HTML above for the relevant primary assets..., I might be able to update my worker to accommodate the calcs to handle those as well.

Try this version.  I believe this sheetworker handles all the relevant HTML auto-calculations.  I changed the defaults of the inputs to value="0". Removed 'disabled' (prevents sheetworkers from setting the value) and changed them to 'readonly' (prevents user editing, but can be set by sheetworkers).  This worker will set the total weight to attr_inventoryweight. <input name="attr_rationweight" value="0" data-sm-id="30" type="number" class="sm-prop sm-num" style="left: 88px; top: 591px; width: 48px; height: 30px;" readonly /> <input name="attr_ingredientweight" value="0" data-sm-id="25" type="number" class="sm-prop sm-num" style="left: 87px; top: 686px; width: 48px; height: 30px;" readonly /> <input name="attr_metalweight" value="0" data-sm-id="26" type="number" class="sm-prop sm-num" style="left: 87px; top: 731px; width: 48px; height: 30px;" readonly /> <input name="attr_woodweight" value="0" data-sm-id="27" type="number" class="sm-prop sm-num" style="left: 87px; top: 774px; width: 48px; height: 30px;" readonly /> <input name="attr_stringweight" value="0" data-sm-id="28" type="number" class="sm-prop sm-num" style="left: 87px; top: 817px; width: 48px; height: 30px;" readonly /> <input name="attr_leatherweight" value="0" data-sm-id="29" type="number" class="sm-prop sm-num" style="left: 87px; top: 862px; width: 48px; height: 30px;" readonly /> <input name="attr_inventoryweight" value="0" data-sm-id="14" type="number" class="sm-prop sm-num" style="left: 251px; top: 358px; width: 60px; height: 29px;" readonly /> <script type="text/worker"> const primaryAssets = ['ration', 'ingredient', 'metal', 'wood', 'string', 'leather']; const primaryAssetsWeight = ['rationweight', 'ingredientweight', 'metalweight', 'woodweight', 'stringweight', 'leatherweight']; const secondaryAssetsWeight = [ 'assetweight1','assetquantity1','assetweight2','assetquantity2','assetweight3','assetquantity3','assetweight4','assetquantity4', 'assetweight5','assetquantity5','assetweight6','assetquantity6','assetweight7','assetquantity7','assetweight8','assetquantity8', 'assetweight9','assetquantity9','assetweight10','assetquantity10','assetweight11','assetquantity11','assetweight12','assetquantity12' ]; const allAttributes = [...primaryAssets, ...primaryAssetsWeight, ...secondaryAssetsWeight]; const eventString = allAttributes.map((attr) => `change:${attr}`).join(' '); on(eventString, (eventInfo) => { getAttrs(allAttributes, (v) => { const output = {}; let primaryWeightTotal = 0; let secondaryWeightTotal = 0; // primary primaryAssets.forEach((asset) => { const rawQuantity = parseInt(v[asset], 10) || 0; const weightAttrName = `${asset}weight`; // Apply the 0.2 weight multiplier if it's not 'ration' const calculatedWeight = asset === 'ration' ? rawQuantity : rawQuantity * 0.2; output[weightAttrName] = calculatedWeight; primaryWeightTotal += calculatedWeight; }); // secondary const weight = 'assetweight'; const quantity = 'assetquantity'; let count = 1; do { const thisWeight = parseInt(v[`${weight}${count}`], 10) || 0; const thisQuantity = parseInt(v[`${quantity}${count}`], 10) || 0; secondaryWeightTotal += thisWeight * thisQuantity; count++; } while (count < 13); output.inventoryweight = primaryWeightTotal + secondaryWeightTotal; setAttrs(output, {silent: true}); }); }); </script>

It works! Thank you so much! I only have one more question (at least for now). Any idea why certain numbers with decimals might be written like this (with a bunch of repeating zeros)? vÍnce said: Try this version.  I believe this sheetworker handles all the relevant HTML auto-calculations.  I changed the defaults of the inputs to value="0". Removed 'disabled' (prevents sheetworkers from setting the value) and changed them to 'readonly' (prevents user editing, but can be set by sheetworkers).  This worker will set the total weight to attr_inventoryweight. <input name="attr_rationweight" value="0" data-sm-id="30" type="number" class="sm-prop sm-num" style="left: 88px; top: 591px; width: 48px; height: 30px;" readonly /> <input name="attr_ingredientweight" value="0" data-sm-id="25" type="number" class="sm-prop sm-num" style="left: 87px; top: 686px; width: 48px; height: 30px;" readonly /> <input name="attr_metalweight" value="0" data-sm-id="26" type="number" class="sm-prop sm-num" style="left: 87px; top: 731px; width: 48px; height: 30px;" readonly /> <input name="attr_woodweight" value="0" data-sm-id="27" type="number" class="sm-prop sm-num" style="left: 87px; top: 774px; width: 48px; height: 30px;" readonly /> <input name="attr_stringweight" value="0" data-sm-id="28" type="number" class="sm-prop sm-num" style="left: 87px; top: 817px; width: 48px; height: 30px;" readonly /> <input name="attr_leatherweight" value="0" data-sm-id="29" type="number" class="sm-prop sm-num" style="left: 87px; top: 862px; width: 48px; height: 30px;" readonly /> <input name="attr_inventoryweight" value="0" data-sm-id="14" type="number" class="sm-prop sm-num" style="left: 251px; top: 358px; width: 60px; height: 29px;" readonly /> <script type="text/worker"> const primaryAssets = ['ration', 'ingredient', 'metal', 'wood', 'string', 'leather']; const primaryAssetsWeight = ['rationweight', 'ingredientweight', 'metalweight', 'woodweight', 'stringweight', 'leatherweight']; const secondaryAssetsWeight = [ 'assetweight1','assetquantity1','assetweight2','assetquantity2','assetweight3','assetquantity3','assetweight4','assetquantity4', 'assetweight5','assetquantity5','assetweight6','assetquantity6','assetweight7','assetquantity7','assetweight8','assetquantity8', 'assetweight9','assetquantity9','assetweight10','assetquantity10','assetweight11','assetquantity11','assetweight12','assetquantity12' ]; const allAttributes = [...primaryAssets, ...primaryAssetsWeight, ...secondaryAssetsWeight]; const eventString = allAttributes.map((attr) => `change:${attr}`).join(' '); on(eventString, (eventInfo) => { getAttrs(allAttributes, (v) => { const output = {}; let primaryWeightTotal = 0; let secondaryWeightTotal = 0; // primary primaryAssets.forEach((asset) => { const rawQuantity = parseInt(v[asset], 10) || 0; const weightAttrName = `${asset}weight`; // Apply the 0.2 weight multiplier if it's not 'ration' const calculatedWeight = asset === 'ration' ? rawQuantity : rawQuantity * 0.2; output[weightAttrName] = calculatedWeight; primaryWeightTotal += calculatedWeight; }); // secondary const weight = 'assetweight'; const quantity = 'assetquantity'; let count = 1; do { const thisWeight = parseInt(v[`${weight}${count}`], 10) || 0; const thisQuantity = parseInt(v[`${quantity}${count}`], 10) || 0; secondaryWeightTotal += thisWeight * thisQuantity; count++; } while (count < 13); output.inventoryweight = primaryWeightTotal + secondaryWeightTotal; setAttrs(output, {silent: true}); }); }); </script>

My bad.  Try this updated sheetworker (used toFixed(1) to show only the first decimal position); <script type="text/worker"> const primaryAssets = ['ration', 'ingredient', 'metal', 'wood', 'string', 'leather']; const primaryAssetsWeight = ['rationweight', 'ingredientweight', 'metalweight', 'woodweight', 'stringweight', 'leatherweight']; const secondaryAssetsWeight = [ 'assetweight1','assetquantity1','assetweight2','assetquantity2','assetweight3','assetquantity3','assetweight4','assetquantity4', 'assetweight5','assetquantity5','assetweight6','assetquantity6','assetweight7','assetquantity7','assetweight8','assetquantity8', 'assetweight9','assetquantity9','assetweight10','assetquantity10','assetweight11','assetquantity11','assetweight12','assetquantity12' ]; const allAttributes = [...primaryAssets, ...primaryAssetsWeight, ...secondaryAssetsWeight]; const eventString = allAttributes.map((attr) => `change:${attr}`).join(' '); on(eventString, (eventInfo) => { getAttrs(allAttributes, (v) => { const output = {}; let primaryWeightTotal = 0; let secondaryWeightTotal = 0; // primary primaryAssets.forEach((asset) => { const rawQuantity = parseInt(v[asset], 10) || 0; const weightAttrName = `${asset}weight`; const rawWeight = asset === 'ration' ? rawQuantity : rawQuantity * 0.2; const calculatedWeight = Number(rawWeight.toFixed(1)); output[weightAttrName] = calculatedWeight; primaryWeightTotal += calculatedWeight; }); // secondary const weight = 'assetweight'; const quantity = 'assetquantity'; let count = 1; do { const thisWeight = parseInt(v[`${weight}${count}`], 10) || 0; const thisQuantity = parseInt(v[`${quantity}${count}`], 10) || 0; secondaryWeightTotal += thisWeight * thisQuantity; count++; } while (count < 13); output.inventoryweight = Number((primaryWeightTotal + secondaryWeightTotal).toFixed(1)); setAttrs(output, {silent: true}); }); }); </script>