Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

Colour change on Calculated field

TarnG
Level 2
Level 2

I have a calculated field: TotalDiff. It is the sum of amounts in repeating subforms. I want this calculated amount to display in red if it is a negative amount. I have used FormCalc as this was the only way I could figure out how to sum the amounts from the repeating subforms. I have never used FormCalc before and cannot figure out how to do the font colour change. From reading other discussion threads, I thought the following should work, but it doesn't. The sum formula works fine by itself, but when I add the if/else for the font colour, even that stops working. Where am I going wrong?

  form1.TotalBlock.TotalDiff::calculate - (FormCalc, client)

TotalDiff = Sum(ItemBlock[*].Diff)

 

if (form1.TotalBlock.TotalDiff.rawValue<0) then form1.TotalBlock.TotalDiff.font.fill.color.value="255,0,0"

else form1.TotalBlock.TotalDiff.font.fill.color.value="0,0,0"

endif

6 Replies
try67
Level 4
Level 4

[Question moved to the LCD forum]

Josh_Geurian
Level 4
Level 4

If this was me I would use the Action Builder instead of FormCalc. If the field you have is a numeric Field go to the toolbar at the top, select Tools, from the drop downbox select "Action Builder". Then create a new action where when the numeric field is less than 0  the result is to set the foreground color of the numeric field to red. In just my personal use FormCalc is great for mathematical equations and not font or format changing. So for example I had to build a PDF that had a littler over 100 cells that all calculated from each other and that was FormCalc. To make changes to the actual form structure like font changes that would be JavaScript or sometimes the easiest route is the Action Builder if you are like me with scripting.

radzmar
Level 10
Level 10

Ok, I'm suggesting to not use the Action Builder as it creates clunky scriptings with a lot of limitations.

To change the fields color you can use the following script (FormCalc).


$ = Sum(ItemBlock[*].Diff)



if ($ lt 0) then


  $.font.fill.color.value="255,0,0"


else


  $.font.fill.color.value="0,0,0"


endif


TarnG
Level 2
Level 2

Thanks Josh, I did try using the action builder first because I'm really bad with code, but I only lets me do it as an exit script so it doesn't actually do the colour change unless the user tabs through or clicks in and out of that total field (which they wouldn't usually do 'coz it's a calculated field). Do you know of a way to get it do this as a calculate or validate action?

TarnG
Level 2
Level 2

Thanks radzmar, but I just can't get it to work

It is doing the same as the original script I had - calculation formula works fine but once the colour script goes in even the formula stops working. I tried moving the colour script portion to validate event but that doesn't work either. Would it make a difference that I'm not using tables, just fields?

I have other fields in the form that change font colour based on calculated result but I ended up doing them via javascript as it was a simple num=a-b type formula. However, it appears there is no simple formula in java to sum fields in repeating subforms, and I'm not having any luck with the scripting I can find online for that either! But I shall persevere and try and find a solution one way or another.

radzmar
Level 10
Level 10

Did you save your form as dynamic PDF?