populate and color the value of a FORMULA RICH TEXT field based on the values of two other fields

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Hello:
I have created an input form that has some fields that will be used as look up fields for an end user report. 

Fields:

CLIENT:
Related table within the larger app.  Will be a required field from input form.  Will be visible on table report.

LEADER.SELECT
Field type is Text-Multiple choice with value options of "LEADER A" or "LEADER B".  Will be visible on input form only. Will be a required field from input form.

STATUS.SELECT
Field type is Text-Multiple choice with value options of "IN PROGRESS" or "COMPLETE".  Will be visible on input form only. Will be a required field from input form.

STATUS.REPORT
Field type is Formula-Rich Text.  Will NOT be visible on input form, but will be visible on report table.  Desired function is to have the VALUE populated based on LEADER.SELECT value and the COLOR determined by the STATUS.SELECT value:

So, within the field STATUS.REPORT, I'm having trouble with the syntax to execute the following logic. Is it even possible?:

IF [LEADER.SELECT]="LEADER A" AND [STATUS.SELECT]="IN PROGRESS", then:
(1)populate STATUS.REPORT field with text value of "LEADER A" 

AND 

(2)color field cell within table report as Green background/black text.

I've been able to successfully execute a content lookup/populate + color function based on the value of one field in a form. But am struggling to populate the STATUS.REPORT field based on the value of the LEADER.SELECT field AND also color the STATUS.REPORT field based on STATUS.SELECT value.  Please assist.
Photo of Tresa Tearpak

Tresa Tearpak

  • 70 Points

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,704 Points 50k badge 2x thumb
You should be able to use this formula to calculate [Status.Report]


var text Result = 
IF([LEADER.SELECT]="LEADER A" AND [STATUS.SELECT]="IN PROGRESS",  "LEADER A");

"<div style=\"background-color:green;\">"& $Result & "</div>"

In the place of green you can use a hex colour code including the leading #.  There is a great list of colours here

https://laurahillier.quickbase.com/db/bhy8pumuk?a=q&qid=1