Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

script for calculated-read only field that will change color based on condition

Avatar

Level 1

I need help creating a script that would make a calculated-read only field change color if a condition is met.  Should the script placed in the calculated event?

 

The fields are as follows:

  

CourseNum (auto sum calculates 11 numeric different fields)

A1P (numeric calculated read only field)

AP (numeric calculated read only field)

HIGHA (the sum of A1P and AP) - this is also a calculated read-only field

So the condition would be:

  

if CourseNum >20 and HIGHA >=50%

then change the background color of HIGHA to yellow

otherwise it should be white

1 Accepted Solution

Avatar

Correct answer by
Level 7

it's your if statement. The "or" is the last operation used. So, you're actually saying, "if year is 4 and (B1P+BP)>".45" OR if (B1P+BP)<".30" and CourseNum>="20", then change the background."

Try:

if (form1.#subform[0]...YearLevel == 4 & ((B1P+BP)>".45" or (B1P+BP)<".30") & CourseNum>="20")

View solution in original post

8 Replies

Avatar

Level 5

It should be the following script in javascript not formcalc:

//Perhaps you have to delete the % of the second value if the % it's only in the display pattern

if(CourseNum.rawValue > "20" && HIGHA.rawValue >= "50%")

{

    HIGHA.fillColor = "245,245,0";

}else

{

    HIGHA.fillColor = "255,255,255";

}

Hope it's helpful?

Mandy

Avatar

Level 1

update - I've provided a screen shot below.  I'm a newbie, so my terminology may not be correct as I describe my question.

When the user enters data in the "number of students" row - the "percent of class total" is auto-calculated.  Depending on the year level, class size, and percent of class the percent range the fields in the chart belwo change colour (see screen shot).  My problem is that more than one field changes colour once the data is enetered although the script specifics the year level.  In the shot below only <5% should be highlighted, but the 30-45% is also highlighted because it is true (but only if the year level was 4).  How can I revise the script to ensure checks the year first on the year?

The script for the 5% is as follows:

form1.#subform[0].#subform[1].#subform[2].YEAR1[2].TextField20::calculate - (FormCalc, client)

if (form1.#subform[0].#subform[1].#subform[2].Row[0].YearLevel == 1 & (EP+FP)>".05" & CourseNum>="20") then

$.fillColor = "255,225,0";

else

$.fillColor = "255,255,255";

endif

The script for the 30-45% is as follows:

form1.#subform[0].#subform[1].#subform[2].YEAR4.TextField17::calculate - (FormCalc, client)

if (form1.#subform[0].#subform[1].#subform[2].Row[0].YearLevel == 4 & (B1P+BP)>".45" or (B1P+BP)<".30" & CourseNum>="20")

then

$.fillColor = "225,225,0";

else

$.fillColor = "255,255,255";

endif

LC sample.jpg

Hoping someone can shed some light as I've looked up a lot of stuff and can't find anything.

Thanks for your help.

Avatar

Correct answer by
Level 7

it's your if statement. The "or" is the last operation used. So, you're actually saying, "if year is 4 and (B1P+BP)>".45" OR if (B1P+BP)<".30" and CourseNum>="20", then change the background."

Try:

if (form1.#subform[0]...YearLevel == 4 & ((B1P+BP)>".45" or (B1P+BP)<".30") & CourseNum>="20")

Avatar

Level 1

Thank you for taking the time to answer!!!!!

Avatar

Level 7

No problem! Thanks for marking as the answer.

Avatar

Level 1

I'm having the same issue now that my script includes more than one option from the drop-down list.  Using the example above, I now have included the option of year 4,6, or 8:

form1.#subform[0].#subform[1].#subform[2].YEAR4.TextField17::calculate - (FormCalc, client)

if (form1.#subform[0].#subform[1].#subform[2].Row[0].YearLevel == "4" or "6" or "8" & ((B1P+BP)>".45" or (B1P+BP)<".30") & CourseNum>="20")

then

$.fillColor = "225,225,0";

else

$.fillColor = "255,255,255";

endif

Hope you can help.

Avatar

Level 7

I'm thinking you're giving the user the option of picking 4, 6, or 8 and all of those being valid choices. If so, try parenthesis around that part of your condition.

if((form1.#subform[0]...YearLevel eq "4" or "6" or "8") & ((B1P+BP)...

I don't use Formcalc enough to tell you if using "or" like that is going to work, though. You may have to spell out the entire condition.

if ((form1.#subform[0]...YearLevel eq "4" or form1.#subform[0]...YearLevel eq "6" or ... eq "8") & ((B1P+BP)...

Avatar

Level 1

Thanks once again!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You are correct (for anyone who did not understand my question) - it is a drop down-list where 3 of the 8  options have the same condition to follow.

The second suggestion of spelling out the entire condition worked like a charm.