# Problem summing column's values of a table in the footer **URL:** \ **Category:** Ignition\ **Created:** [September 29, 2023, 11:42am UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832 "2023-09-29T11:42:21Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 11:42am UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/1 "2023-09-29T11:42:21Z")
Hello gentlemen, I have a problem with a table that I don't know how to solve: I have a table that is populated by a sql query ==\> return format as json: ![table json](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/e/aed1089b815a039b3e376ceb84b251fd934fc6d1.jpeg) I use this script in order to sum "Quantity" column on the footer (tnx to @[pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)) ![sum script](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/5/e5c997d5e273fc9467b8fe7ab3572ea7671af337.jpeg) Here the results: ![table befor](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/f/cf84b8511835b75b49b3a6a10276b46d2ec8feb5.jpeg) So far so good everything works fine! I wanted to color the background of the rows based on a value. I changed the return format of the query from json to dataSet, (I used a script by @[ynejati](https://forum.inductiveautomation.com/u/ynejati)). ![table json transform](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/b/ab362912274811cfabbd338cbc6707c0df9242e7.jpeg) Changing backgroundColor based on a value works, but now the sum "Quantity" in the footer column no longer works and I don't know where I'm going wrong. ![table after](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/8/88fb7d6c211591b941254eec617e88c5d5696416.jpeg) Thanks for any suggestions ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 29, 2023, 11:50am UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/2 "2023-09-29T11:50:22Z")
Why change the output to dataset to then re convert it to json ? Keep the json as the base return, and add the style to it directly. The sum doesn't work anymore because you changed the structure of the json. I can't do much more right now, I have a sick kid to take care of, but I'll come back to it when I can. ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 12:34pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/3 "2023-09-29T12:34:03Z")
Hi Pascal, good to hear from you. - "Why change the output to dataset to then re convert it to json ?" I'm trying to persuade my company to switch to Ignition from other scadas and then I have to show something charming 🙂 - "Keep the json as the base return, and add the style to it directly." Yes, you're right, I just need to figure out how to do it - "The sum doesn't work anymore because you changed the structure of the json." I imagined that was the problem, I just don't know how to point to the values of the new json structure. I'll focus on keeping the json as the base return and add the style directly. Can I possibly contact you if I can't? I'm sorry that your kid is not well, I hope he gets well soon. Bye. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 29, 2023, 12:36pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/4 "2023-09-29T12:36:55Z")
> [@eMax](#): > > Can I possibly contact you if I can't? Keep the conversation on the forum. That way everyone benefits! ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 29, 2023, 12:42pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/5 "2023-09-29T12:42:39Z")
> [@eMax](#): > > I just don't know how to point to the values of the new json structure. I recommend working through the examples in the Table documenation. [https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table#PerspectiveTable-Examples](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table#PerspectiveTable-Examples) ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 1:30pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/6 "2023-09-29T13:30:52Z")
of course, I didn't mean in "pvt" 🙂 ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 29, 2023, 3:07pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/7 "2023-09-29T15:07:04Z")
Can you copy and paste what the `data` looks like ? And I mean the actual data, not the binding. You can just right click on the property and select copy. ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 3:37pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/8 "2023-09-29T15:37:56Z")
ok. this one i "normal" json ![json orig](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/d/3d8f5fafb51fb9643518b0c788e0ccf28cf184f1.jpeg) this one instead is a dataSet converted to json ![dataset to json](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/9/995e0e71d6a5086f4c548e7fdc03385dbbc4dc5d.jpeg) ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 29, 2023, 3:58pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/9 "2023-09-29T15:58:11Z")
data transform script (with json source format): ```python return [ { 'style': {'backgroundColor': "#FF8A8A66" if row['quantita'] < 20000 else "none"}, 'value': row } for row in value ] ``` You might want to replace `"none"` by whatever default color you want. footer's sum: ```python data = value.filtered or value.data total = round(sum(row.value['quantita'] for row in value)) return "{:.9n} kg".format(total) ``` ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 4:10pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/10 "2023-09-29T16:10:35Z")
first one works great with return format json footer's sum don't ![footer sum](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/0/c02d22d0be7a2275b210bab1f3768091e2d3a1aa.png) it says "parse error in script transform" ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 29, 2023, 4:57pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/11 "2023-09-29T16:57:18Z")
Tip: while posting a screengrab is useful for seeing the context of your code, you should post the text of the code as well so that your readers can copy and edit it. See [this topic](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831) if you're not familiar with code formatting on this site. ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 4:58pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/12 "2023-09-29T16:58:00Z")
ok, I found an error... should be: - total = round(sum(row.value['Quantita'] for row in data)) instead of: - total = round(sum(row.value['Quantita'] for row in value)) in thist case I got the sum, but if I write somethind in the filter I got error ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 29, 2023, 4:58pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/13 "2023-09-29T16:58:37Z")
> [@pascal.fragnoud](#): > > `data = value.filtered or value.data` Hi, Pascal. Can you explain how this works? I've never seen it before! ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 5:00pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/14 "2023-09-29T17:00:29Z")
> [@Transistor](#): > > you're not familiar with code formatting on this site. yes, I'm not familiar with code formatting on this site. I will try in next posts. thanks ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [September 29, 2023, 5:44pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/15 "2023-09-29T17:44:51Z")
> [@Transistor](#): > > Hi, Pascal. Can you explain how this works? I've never seen it before! `or` is a boolean operator that acts as an expression, and automatically treats the inputs as booleans. So if the left hand expression is 'falsey' (such as an empty list, dict, or string), the right hand expression will be returned instead. It's equivalent to: `data = value.filtered if bool(value.filtered) else value.data` Or more imperatively: ```auto if value.filtered: data = value.filtered else: data = value.data ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 29, 2023, 6:37pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/16 "2023-09-29T18:37:54Z")
My bad, I went a bit too fast and didn't test it. I can't spot the parse error from here, maybe an indentation mismatch ? make sure every line start with exactly the same indentation, It can be 13 spaces, 2 tabs, whatever you like, as long as it's consistent. Also try without the format flags: `"{}".format(total)`, see if there's not something wrong here... But I can spot a logic error though: line 2 should end with `for row in data` instead of `for row in value` > [@Transistor](#): > > Can you explain how this works? It's basically `coalesce`. Lazy man null check ;p ---
**Author:** ![eMax](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@eMax](https://forum.inductiveautomation.com/u/eMax)\ **Post date:** [September 29, 2023, 8:19pm UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/17 "2023-09-29T20:19:03Z")
> [@pascal.fragnoud](#): > > ```auto > data = value.filtered or value.data > total = round(sum(row.value['quantita'] for row in value)) > return "{:.9n} kg".format(total) > > ``` Hi Pascal, I tried to modify your code al follow: ```auto def transform(self, value, quality, timestamp): if value.filtered: data = value.filtered total = round(sum(row.value['Quantita'] for row in data)) else: data = value.data total = round(sum(row.value['Quantita'] for row in data)) return "{}".format(total) ``` and I got following message: line 4, in Attribute Error: object has no attribure 'value' So I modified the code as follow: ```auto def transform(self, value, quality, timestamp): if value.filtered: data = value.filtered total = round(sum(v['Quantita'] for v in data), 0) else: data = value.data total = round(sum(row.value['Quantita'] for row in data)) return "{:.9n}".format(total)+" kg" ``` and finally everything works as expected. wow, what a long day! 🥴 thank you @pascal.fragnoud for your support I hope your son is doing better bye ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [September 30, 2023, 2:08am UTC](https://forum.inductiveautomation.com/t/problem-summing-columns-values-of-a-table-in-the-footer/79832/18 "2023-09-30T02:08:09Z")
Right, only one of those changed… Glad you got it working. Not a son, daughter, 20 months old and she’s crying right now at 4am ☹ It’s gonna be a very long day