Example query for conditional formatting

  1. Create a query for WIDGET7 and select in Visible in API check box. 
    SELECT FSACTIONWIPSTEPS.TARGET_OBJECT_KEY AS PROJECT_CODE, FSACTIONWIPSTEPS.ACTION_CODE, FSACTIONWIPSTEPS.DESCRIPTION,
(SELECT ENUM_LABEL AS STATUS FROM FSVALIDENUMVAL EV, FSVALIDENUMLABEL EL WHERE EV.ENUM_ORDER = EL.ENUM_ORDER AND EV.ENUM_VALUE = FSACTIONWIPSTEPS.QUEUE_STATUS_IND
AND EL.ENUM_CODE = 'QUEUESTATUSIND' AND EV.ENUM_CODE = 'QUEUESTATUSIND' AND EL.LANGUAGE_CODE = 'EN-US') AS 'QUEUE STATUS',
CONVERT(DATETIME, FSACTIONWIPSTEPS.START_DATE) AS START_DATE, CONVERT(DATETIME, FSACTIONWIPSTEPS.DUE_DATE) AS DUE_DATE, FSACTIONWIPSTEPS.TARGET_OBJECT_KEY AS DR_PROJECT_PROJECT_CODE,
CASE FSACTIONWIPSTEPS.DESCRIPTION
WHEN 'IDEATION STAGE' THEN '#CFF5F5'
WHEN 'DOCUMENT IDEA' THEN '#F4B8C4'
WHEN 'SUBMIT' THEN 'PALEGREEN'
WHEN 'APPROVAL STAGE' THEN 'RED'
WHEN 'MARKETING APPROVAL' THEN 'BLUE'
WHEN 'MANUFACTURING APPROVAL' THEN 'GREEN'
WHEN 'EXECUTIVE APPROVAL' THEN '#FF5733'
END AS F_BG_PROJECT_CODE,
CASE FSACTIONWIPSTEPS.ACTION_CODE
WHEN 'APPROVE/REJECT' THEN '#C3228B'
WHEN 'RETURN/APPROVE' THEN '#F4B8C4'
WHEN 'SUBMIT CONCEPT' THEN 'PALEGREEN'
WHEN 'CONCEPT REVIEW' THEN 'RED'
WHEN 'PLANNING' THEN 'BLUE'
WHEN 'DEVELOPMENT' THEN 'GREEN'
WHEN 'VALIDATION' THEN '#FF5733'
WHEN 'PRODUCT LAUNCH' THEN '#C4FF33'
WHEN 'PROJECT CLOSURE' THEN '#33FFF9'
WHEN 'SUBMIT CONCEPT' THEN '#FF33F3'
END AS F_FG_ACTION_CODE,
(SELECT CASE WHEN DATEADD(DAY, 5, GETDATE()) <= CONVERT(DATE,FSACTIONWIPSTEPS.DUE_DATE) THEN 'MAJOR_DELAY' WHEN DATEADD(DAY, 4, GETDATE()) <= CONVERT(DATE,
FSACTIONWIPSTEPS.DUE_DATE) THEN 'MINOR_DELAY' WHEN DATEADD(DAY, 3, GETDATE()) >= CONVERT(DATE, FSACTIONWIPSTEPS.DUE_DATE) THEN 'ON_TIME'
END) AS F_STATUS_ICON_PROJECT_STATUS
FROM FSACTIONWIPSTEPS
INNER JOIN FSACTIONWIP
ON FSACTIONWIP.ACTIONWIP_ID = FSACTIONWIPSTEPS.ACTIONWIP_ID WHERE FSACTIONWIPSTEPS.TARGET_OBJECT_SYMBOL = 'PROJECT' AND FSACTIONWIP.STAGE_GATE_IND = 1
    appendix B_image1
  2. Create a new Lookup Set ‘QUERYWIDGET’.
  3. Select the Lookup Set form and open the QUERYWIDGET lookup set. Add new row in the Lookup Code tab WIDGETQUERY7. Add the above query code ‘WIDGET7’ for the WIDGETQUERY7 lookup code.
    Appendix B_image2
  4. Add the column labels in the Lookup Language tab in the order of the columns mentioned in the query WIDGET7. The prefixes that indicate conditional formatting must be in upper case because prefixes are codes and not labels. Add the conditional formatting column labels after the actual data columns.
  5. Below are the labels mentioned for columns in Lookup Language tab.
    Project Code;Action Code;Description;Queue Status;Start Date;Due Date;DR_PROJECT_Project Code;F_BG_Description;F_FG_Action Code;F_STATUS_ICON_Project Delay Status
    • Project Code; Action Code; Description; Queue Status; Start Date; Due Date are the data columns.
    DR_PROJECT_Project Code;F_BG_Description;F_FG_Action Code;F_STATUS_ICON_Project Delay Status are the conditional formatting columns.
    • DR_PROJECT_Project Code: Adds hyperlink format to Project Code column and supports the drill back feature.
    Syntax: DR_<<ObjectSymbol>>_<<ObjectColumnLabel>>
    • F_BG_Description : Adds the background color to Description column.
    • F_FG_Action Code : Adds foreground color to Action Code column.
    • F_STATUS_ICON_Project Delay Status: Displays a new column in the UI with the column name ‘Project Delay Status’ as mentioned in the column labels, status icons are displayed in this column.
    Appendix B_image3
  6. In the widget, click Configure and provide ‘QUERYWIDGET\WIDGETQUERY7’ for Query Code. Click Save.
    Appendix B_image4
  7. Now, the query widget displays the data grid with the conditional formatting.
    Appendix B_image5