Discussions

Expand all | Collapse all

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

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

    Posted 05-18-2018 21:00
    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.


  • 2.  RE: populate and color the value of a FORMULA RICH TEXT field based on the values of two other fields

    Posted 05-18-2018 23:52
    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