[SheetWorker/Help] Sum value from Static and Repeating rows

Hello Roll20 I am trying t o improve the AD&D 2E Revised character sheet. In my efforts I came across this issue. In the Currency section, there is a calculation for total GP value of gems. It looks like this: The first row in the table does not calculate to the total counter in the bottom of the table. This is because the first row is a static row and the remaining rows are repeating. Here is the HTML code and the SheetWorker code: HTML: <h4>Gem Pouch</h4> <table> <tr> <td class="sheet-default-header" style="text-align:left;">Description</td> <td class="sheet-default-header">Value</td> <td class="sheet-default-header"># Held</td> <td class="sheet-default-header">Cut/Size</td> </tr> <tr> <td><input type="text" name="attr_gemdesc" title="@{gemdesc}" class="sheet-medium" placeholder="Item Name"></td> <td><input type="text" name="attr_gemvalue" title="@{gemvalue}" class="sheet-short" value="0" placeholder="GP"></td> <td><input type="number" name="attr_gemqty" title="@{gemqty}" class="sheet-short" value="0"></td> <td><input type="text" name="attr_gemsizecut" title="@{gemsizecut}" class="sheet-medium" placeholder="Size/Cut details"></td> </tr> </table> <fieldset name="repeating_gem" class="repeating_gem"> <table> <tr> <td><input type="text" name="attr_gemdesc" title="@{repeating_gem_$X_gemdesc}" class="sheet-medium" placeholder="Item Name"></td> <td><input type="text" name="attr_gemvalue" title="@{repeating_gem_$X_gemvalue}" class="sheet-short" placeholder="GP"></td> <td><input type="number" name="attr_gemqty" title="@{repeating_gem_$X_gemqty}" class="sheet-short" value="0"><input style="display: none;" type="text" name="attr_gemvaluetemp" class="sheet-short" value="0" disabled></td> <td><input type="text" name="attr_gemsizecut" title="@{repeating_gem_$X_gemsizecut}" class="sheet-medium" placeholder="Size/Cut details"></td> </tr> </table> </fieldset> SheetWorker //Standard Currency on('change:repeating_gem remove:repeating_gem', function(){ TAS.repeating('gem') .attrs('gemstotalvalue') .fields('gemvalue','gemqty','gemstotalvalue') .reduce(function(m,r){ m.gemvalue+=(r.F.gemvalue*r.I.gemqty); r.gemvaluetemp=(r.F.gemvalue*r.I.gemqty); return m; },{gemvalue:0, desc: []},function(m,r,a){ a.gemstotalvalue=m.gemvalue; }) .execute(); }); I can see the issue, that the sheet worker is only looking for changes in the repeating rows, and therefore obviously does not include any values from the static row. I know that a "quick fix" would be to simply remove the one static row, and only use repeating rows. Then it would calculate all rows as expected. However since this is a preexisting sheet, I do not want to do that as I might remove data that various players has inputet in the first row. I would much rather improve the calculation to handle all the rows, static and repeating. I am totally new to sheetworkers, and I am not a Pro user (so I have a Pro user upload my sheet for tests), so development is slow and roundtrips for tests are long. Also I have no idea how to debug sheetworkers, as I cannot seem to find the code in the Chrome DevTools. If anyone knows how to modify the above sheet worker to also track the static row, and could give me a finished code snippet I would really appreciate it! There are multiple other table sets of static and repeating rows throughout the sheet that has the same problem, so a fix here would mean 7 other fixes down the road :)

I dont have any experience using TAS, but here's a sheet worker that doesnt use it, that hopefully does what you need. Replace the existign sheet worker with this: on('change:repeating_gem remove:repeating_gem change:gemvalue change:gemqty', () => {     getSectionIDs('repeating_gem', idarray => {         const fieldnames = [];         idarray.forEach(id => fieldnames.push(`repeating_gem_${id}_gemvalue`, `repeating_gem_${id}_gemqty`));         getAttrs(['gemvalue','gemqty', ...fieldnames], v => {             let totalvalue = (parseFloat(v.gemvalue) || 0) * (parseInt(v.gemqty) || 0);             idarray.forEach(id => {                 totalvalue += (parseFloat(v[`repeating_gem_${id}_gemvalue`]) || 0) * (parseInt(v[`repeating_gem_${id}_gemqty`]) || 0);             });             setAttrs({gemstotalvalue: totalvalue});         });     }); }); I dont have time to annotate it now, but if you show the other areas with this problem we can show you how to generalise it.

GiGs said: I dont have any experience using TAS, but here's a sheet worker that doesnt use it, that hopefully does what you need. Replace the existign sheet worker with this: I assume that TAS stands for The Aaron Sheet. It just hit my mind just now :) The rest of the code is exact duplicates, except that there is a number for each of the calculations. //Dragonlance Currency on('change:repeating_gem2 remove:repeating_gem2', function(){ TAS.repeating('gem2') .attrs('gemstotalvalue2') .fields('gemvalue2','gemqty2','gemstotalvalue2') .reduce(function(m,r){ m.gemvalue2+=(r.F.gemvalue2*r.I.gemqty2); r.gemvaluetemp2=(r.F.gemvalue2*r.I.gemqty2); return m; },{gemvalue2:0, desc: []},function(m,r,a){ a.gemstotalvalue2=m.gemvalue2; }) .execute(); }); ... And so on with 3, 4, 5, and 6 I am well aware that all of this could be generalized into something beautiful, but for now I am content with a bit of Copy-Paste of code. Thank you a lot for your provided code snippet! I will try and add it the next time my Pro User uploads, and then see if it works. Cheers mate!

Are you saying the sheet has 6 different repeating sections for holding gems? Why does it do that?

I think this is the change you'd make to that TAS code: //Standard Currency on('change:repeating_gem remove:repeating_gem change:gemvalue change:gemqty ', function(){ TAS.repeating('gem') .attrs('gemstotalvalue' ,'gemvalue','gemqty' ) .fields('gemvalue','gemqty') .reduce(function(m,r){ m.gemvalue+=(r.F.gemvalue*r.I.gemqty); r.gemvaluetemp=(r.F.gemvalue*r.I.gemqty); return m; },{gemvalue:0, desc: []}, function(m,r,a){ m.gemValue+=(a.F.gemvalue*a.I.gemqty); a.gemstotalvalue=m.gemvalue; }) .execute(); });

The Aaron said: I think this is the change you'd make to that TAS code: //Standard Currency on('change:repeating_gem remove:repeating_gem change:gemvalue change:gemqty ', function(){ TAS.repeating('gem') .attrs('gemstotalvalue' ,'gemvalue','gemqty' ) .fields('gemvalue','gemqty') .reduce(function(m,r){ m.gemvalue+=(r.F.gemvalue*r.I.gemqty); r.gemvaluetemp=(r.F.gemvalue*r.I.gemqty); return m; },{gemvalue:0, desc: []}, function(m,r,a){ m.gemValue+=(a.F.gemvalue*a.I.gemqty); a.gemstotalvalue=m.gemvalue; }) .execute(); }); Thank you Aaron, almigty Guru of SheetWorkers! :D This change is a lot smaller, and I will definitly go with this. Looking forward to try it and implement it everywhere! :D

GiGs said: Are you saying the sheet has 6 different repeating sections for holding gems? Why does it do that? Hey GiGs. As this sheet was made by the user Seth, about 3 years ago I can only guess and assume what his intentions were. I think the multiple sections is to reflect that, like currency, gems can have different values in different sections: The currency section holds 6 different campaign settings: As Currency has different convertion rates in the different settings: Standard: (100 Copper = 10 Silver = 2 Electrum = 1 Gold = 1/10 Platinum) Dragonlance: (100 Copper = 40 Gold = 20 Silver = 2 Iron/Bronze = 1 Steel = 1/5 Platinum) Dark Sun: (1,000 Bits = 100 Ceramics = 100 Copper = 10 Silver = 2 Electrum = 1 Gold = 1/5 Platinum) Maztica: (100 Cocoa Beans = 100 Ear of Maize = 10 Copper Blade = 5 Coral Bud = 1 Jade = 1/5 Gold Quill = 1/10 Turquoise) I believe that he wanted players to be able to add gems found from each setting, and calculate the total separately. To be honest I thought about joining the gem counter for all the 6 settings into one, but that would remove a lot of data from a lot of players. So that option is out. I am just trying to improve what is already here :)

Ah I see. What might be better is just to use the same attribute names in each setting, dropping the 2, 3, 4, 5, and 6 from the names. The sheet is for one character, and he will have the same belongings if he moves from setting to setting. By using the same attribute names, the same inventory will show up in each setting. 

GiGs said: Ah I see. What might be better is just to use the same attribute names in each setting, dropping the 2, 3, 4, 5, and 6 from the names. The sheet is for one character, and he will have the same belongings if he moves from setting to setting. By using the same attribute names, the same inventory will show up in each setting.  You are absolutly correct! If I had made the sheet from scratch I would totally have dont it that way. However as this is a very old sheet, used by many players for years, I cannot assume that all players have copied their gems across all tabs. A user might have "A green gem, 20 GP" in the Standard Tab and "A red gem, 40 GP" in the Dragonlance Tab. If I now remove the numbers and keep the inventory the same, then all the data that players have entered in the Dragonlance tab will disappear. So I cannot rightfully remove player data. I would have to somehow merge the data from all the tabs into a single inventory, and I do not know how to do that in the code. So for now the Copy-Paste stays, even though, coding wise, it is ugly and not that maintainable

Hello  The Aaron I need your help again. I am having problems with a new sheetworker that I've created. Here is what I've got so far The sheet: The HTML: <h4><b> Nonweapon Proficiencies </b></h4> <h4><b> Total Nonweapon proficiency slots: </b></h4> <input type ="text" name ="attr_profslotstotal" title ="@{profslotstotal}" class ="sheet-display" value ="4+floor(@{level-class1}/3)+@{intlang}" /> <!-- Invisible calculating input field because I do not know if sheet workers can calculate from Roll20 Macros --> <input type ="text" name ="attr_profslotstotalcalc" value ="[[@{profslotstotal}]]" style =" display : none ; " disabled /> <table class ="sheet-table-white" > <tr> <td></td> <td><input type ="number" name ="attr_profslotsremain" title ="@{profslotsremain}" class ="sheet-short" disabled /></td> </tr> <tr class ="sheet-default-header" > <td> Proficiency </td> <td> Slots </td> <td colspan ="2" > Ability Score </td> <td colspan ="3" > Modifier </td> </tr> <tr> <td><input type ="text" name ="attr_profname" title ="@{profname}" placeholder ="Name of NonWepProf" /></td> <td><input type ="number" name ="attr_profslots" title ="@{profslots}" class ="sheet-short" placeholder ="Slots" /></td> <td><input type ="text" name ="attr_profstatnum" title ="@{profstatnum}" placeholder ="@{abilityScore}" class ="sheet-maxtext" ></td> <td> ± </td> <td><input type ="text" name ="attr_profmod" title ="@{profmod}" value ="0" class ="sheet-short" /></td> <td><button type ="roll" name ="roll_Prof" title ="%{Prof}" value ="/roll [[1d20cs<1cf>20]] ≤ [[(@{profstatnum})+(@{profmod})]] @{profname}" ></button></td> </tr> </table> <fieldset name ="repeating_profs" title ="@{profs}" class ="repeating_profs" > <table class ="sheet-table-white" > <tr> <td><input type ="text" name ="attr_profname" title ="@{repeating_profs_$X_profname}" placeholder ="Name of NonWepProf" ></td> <td><input type ="number" name ="attr_profslots" title ="@{repeating_profs_$X_profslots}" class ="sheet-short" placeholder ="Slots" ></td> <td><input type ="text" name ="attr_profstatnum" title ="@{repeating_profs_$X_profstatnum}" placeholder ="@{abilityScore}" class ="sheet-maxtext" ></td> <td> ± </td> <td><input type ="text" name ="attr_profmod" title ="@{repeating_profs_$X_profmod}" value ="0" class ="sheet-short" ></td> <td><button type ="roll" name ="roll_Prof" title ="%{repeating_profs_$X_Prof}" value ="/roll [[1d20cs<1cf>20]] ≤ [[(@{profstatnum})+(@{profmod})]] @{profname}" ></button></td> </tr> </table> </fieldset> The sheetworker: //Nonweapon proficiency slots on('change:repeating_profs remove:repeating_profs change:profslots change:profslotstotalcalc', function(){ TAS.repeating('repeating_profs') .attrs('profslotsremain','profslotstotalcalc','profslots') .fields('profslots') .reduce(function(m,r){ m.profslots+=r.I.profslots; return m; // I have no idea what these values are // ↓ ↓ },{profslots:0, desc: []},function(m,r,a){ m.profslots+=a.I.profslots; a.profslotsremain=(a.I.profslotstotalcalc-m.I.profslots); }) .execute(); }); What I want the sheetworker to do: I would like the sheet worker to sum all the proficiency slots a player has used, and then subtract that number from the total available, thus showing how many remaining proficiency slots the player has left. However whenever I change a value in any of the rows, I get the following error: The errors from chrome console: TypeError: Cannot read property 'profslots' of undefined at Object.eval (eval at messageHandler (sheetsandboxworker.js?1590733541856:698), <anonymous>:2504:59) at eval (eval at messageHandler (sheetsandboxworker.js?1590733541856:698), <anonymous>:562:83) at Function.m.each.m.forEach (sheetsandboxworker.js?1590733541856:5) at Object.eval [as -M5QxlMvoZ48EYqpmRDZ//false//0.8344102887282447] (eval at messageHandler (sheetsandboxworker.js?1590733541856:698), <anonymous>:536:27) at _fullfillAttrReq (sheetsandboxworker.js?1590733541856:673) at messageHandler (sheetsandboxworker.js?1590733541856:705) I have pored over my code again and again, and I cannot seem to find why 'profslots' should be read of an undefined object. The HTML and code here is so similar to the gem code above that I find it hard to see what exactly is going wrong.  The only thing I am now noticing is that I am using m.I.profslots when doing the final calculation. Is it not allowed to "cast" values when reading from m? I haven't been able to find any guide or Wiki on how your sheet works, so I've had to guess and deduct my way through it. Here is what I have so far: attrs: Are the static attributes in the sheet fields: Are the attributes of the repeating fields in the sheet reduce (m,r) reduces many values to a single value     m: I read this as memory  and is just a temporary holder value that can be used. Any property can be added to this object for summing different groups of numbers     r: the repeating field to read. It can be read as r.I for Integer, r.F for Float and r.D[2], for double/decimal including how many decimal points it should include. The final section is for summing static values. So here we use a.I for Integer, a.F for float and a.D[2] for double/decimal with decimal points Right above the final section (marked with a comment) there are some values being written. I have seen this all over my sheet, and I am unsure what the exact value is for. I assume that it is some kind of initializer value for m , so in this case I initialize the m.profslots  to be 0 , because the value above states profslots:0 . This is just a guess though, and I would love for some clarification on what exactly is going on here. One final thing. As you can see in the HTML I have added an invisible input field to hold the calculated value from the Roll20 macro. Is this necessary, or can a sheetworker read the calculated value from a macro? Possible with some other identifyer, a.calc.profslotstotal  or something similar to that? Any help / answers you can give would be greatly appreciated!