Reset field color format after manual update of formula driven autopopulate.

[JLS_Compliance]

I have created a document where certain fields are updated based on triggers in other fields.  Part of this update includes changing color formatting from black on white to white on black.  The autopopulated fields are updated as the trigger fields are also updated.  I have also added the ability for users to manually override the autopopulated data.  (4 wins)

After an autopopulated field has been manually updated, I need the color formatting to return to black on white.

I'm not sure where I'm missing, and I may be double dipping.  Here is the scripting I've put together for the calculation, validation and keystroke.

CALCULATION:

event.rc = !event.target.bDetect

var v = this.getField("Award Value").valueAsString; 
var z = this.getField("USG").value;

if (z=="NO")
{

event.value = "   NOT A USG CONTRACT";
event.target.textColor=color.white
event.target.fillColor=color.black
}

else
if (v<10000000)
{
event.value = "   BELOW THRESHOLD REQUIREMENT   ";
event.target.textColor=color.white
event.target.fillColor=color.black
}

else
if (z!=="NO" && event.value=="   NOT A USG CONTRACT")
{
event.target.value=""
event.target.textColor=color.black
event.target.fillColor=color.transparent
}

else
if (v>=10000000 && event.value=="   BELOW THRESHOLD REQUIREMENT   ")
{
event.target.value=""
event.target.textColor=color.black
event.target.fillColor=color.transparent
}

else
if (v>=10000000 && event.value!="")
{
SetFieldValues(event.value)
event.target.textColor=color.black
event.target.fillColor=color.transparent
}

VALIDATE:

event.rc = true;

if (event.value!== "   BELOW THRESHOLD REQUIREMENT   ")

{
event.target.fillcolor=color.WHITE
event.target.textcolor=color.BLACK
}

else

if (event.value!=="   NOT A USG CONTRACT")

{
event.target.fillcolor=color.WHITE
event.target.textcolor=color.BLACK
}

else
{
event.target.fillcolor=color.BLACK
event.target.textcolor=color.WHITE
}

KEYSTROKE:

if(!event.willCommit)event.target.bDetect = true;else if(event.value == "")event.target.bDetect = false;

[try67]

Not sure if it's the only issue, but this is incorrect:

color.WHITE

It should be:

color.white

Same for "color.BLACK"...

[JLS_Compliance]

Fixed the case size.  No change.  I can see where that would be important now, but I'm thinking I might have some circular or other logic that is overriding any format change. 

[try67]

Yeah, the code seems quite odd. For starters, why do you need the bDetect variable? What is it supposed to do?

And why did you put the same code (pretty much) in both the Calculation event and the Validation event? One should be enough.

[JLS_Compliance]

To be perfectly honest, I'm pretty new at this.  I know just enough to poke around to find answers.

Why the bDetect variable?  I need a way to 'unlock' a data field once it has been autopopulated by the calculation, and to restore the calculated inputs if the manual input is deleted.  When I ran the script prior to adding this, the autopopulated field could not be modified.  I'm wide open to any suggestions here.

As for the duplication, again, I'm going to play the novice card. A lot of it was just a belt & suspenders approach.  Which is the better location, calculate or validate?

[try67 · Accepted solution]

I would consolidate it to a single script. It's a bit hard for me to follow what you're trying to do, though.
If you're trying to set the field's value based on the values of several other fields then use a calculation script.

I've noticed several other issues:

- What is the SetFieldValues function, and how does it come into play here?

- You've defined the "v" variable as a string, but are treating it as a number. That won't work. You've also not defined the "z" variable as a string, explicitly.

So change these two lines:

var v = this.getField("Award Value").valueAsString; 

var z = this.getField("USG").value;

To:

var v = Number(this.getField("Award Value").valueAsString); 

var z = this.getField("USG").valueAsString;