topic Re: Computed Field to calculate TOTAL in a GRID with some empty fields in Fusion Manage Forum

Computed Field to calculate TOTAL in a GRID with some empty fields

Anonymous — Thu, 03 Nov 2016 18:06:57 GMT

I have a grid which contains 8 columns with potential volume data. (year 1 to year 8). There may be in some year columns BLANK data. If there is BLANK data I have found the TOTAL field does not calculate.

All fields in the calculation are integers. TOTAL is a computed field where the calculation is YEAR_1 + YEAR_2 +...+ YEAR_8. Is there a way to calculated the TOTAL when there is BLANK data?

Re: Computed Field to calculate TOTAL in a GRID with some empty fields

bastien.mazeran — Fri, 04 Nov 2016 21:39:43 GMT

Hi Jason,

Could you consider setting the integer fields with a default value of 0?

Regards,

Re: Computed Field to calculate TOTAL in a GRID with some empty fields

Anonymous — Sat, 05 Nov 2016 00:42:35 GMT

It needs to be blank to give the right message to our users. Having 0 means no volumes projected for that year. Blank would mean no data is available for that year currently.

Using coalesce in the computed field works well.

[http://www.nucap.com/images/image001.jpg] Jason Leung - Design Engineer
NUCAP Industries Inc.
3370 Pharmacy Avenue, Toronto ON
Ph.416 494 1444 FAX.416 494 2505
Jason.Leung@nucap.com

Please visit us at www.nucap.com www.gripmetal.com

[http://www.nucap.com/images/banner-pistoncushion.png]

Re: Computed Field to calculate TOTAL in a GRID with some empty fields

tony.mandatori — Sat, 05 Nov 2016 16:36:10 GMT

Have you tried checking for null with CASE statements or a coalesce function?

I tried returning 0 when i have an empty cell and this formula does the trick - where the THENULL is a column name.

CAST(coalesce(THENULL, '0') AS integer)

So instead of adding the values, add the cast instead.

Re: Computed Field to calculate TOTAL in a GRID with some empty fields

Anonymous — Sat, 05 Nov 2016 17:16:06 GMT

Coalesce worked fine. Thank You.

[http://www.nucap.com/images/image001.jpg] Jason Leung - Design Engineer
NUCAP Industries Inc.
3370 Pharmacy Avenue, Toronto ON
Ph.416 494 1444 FAX.416 494 2505
Jason.Leung@nucap.com

Please visit us at www.nucap.com www.gripmetal.com

[http://www.nucap.com/images/banner-nrx.png]