Forum Discussion

OwenMorgan's avatar
OwenMorgan
Qrew Member
6 years ago

multiple reference criteria for a status icon field

I have a status visual header for records. 
This works fine when only looking at one field [status] but I want to add an additional criteria. If [status] is closed AND [filing code] is not blank , show a different header. 

This is what I have

Case([Status],
"Logging","<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/rb/eh/va/logging.jpg\", width=\"700\">",
"Allocated","<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/rc/eh/va/Allocated.jpg\", width=\"700\">",
"Advice","<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/rc/eh/va/Allocated.jpg\", width=\"700\">",
"Awaiting Reply","<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/rd/eh/va/awaiting_reply.jpg\", width=\"700\">",
"Closed","<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/re/eh/va/closed.jpg\", width=\"700\">",
If([Filing Code]<>"", "<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/rb/eh/va/records.jpg\", width=\"700\">",
[Event - Filing Code]<>"", "<img src=\"https://****.quickbase.com/up/bpxgbkbcm/g/rb/eh/va/records.jpg\", width=\"700\">"))

I tried closing the Case() before IF but then I get two versions of the image which is not right. 
in this scenario I am seeing the closed icon but not the records icon on cases that do meet the IF criteria

Can i do this easily without creating a variable?

------------------------------
Owen Morgan
------------------------------
Formulas and functions

4 Replies

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    Owen,

    Getting the syntax right is always a trick when you get beyond one or two sets of parentheses. In the Case Statement you can put additional criteria after the value of the case object.  I also recommend putting the images in variables so that your logic is easier to read.

    // Variable Images
    var text IMGOne =  long url string  one ;
    var text IMGTwo=   long url string two;

    // Logic and UI
    Case ( [Status],

    "Logging" ,  If (  isnull([Event Code]), $IMGOne, IMGTwo),
    "Closed"  ,   If (  [Checkbox]=true, $IMGOne, $IMGTwo)
      )

     
    This keeps your Formula Rich Text Box nice and clean.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------
    • OwenMorgan's avatar
      OwenMorgan
      Qrew Member
      6 years ago
      thanks Don, 
      I am not getting any closer I feel. 

      It's a formula to determine the status header image

      The field is called [status header]

      I want it to show the formula based on the status field EXCEPT when there is a filing Code [Filing Code] or [Event – Filing Code] both text fields.

      I cannot make it work! AM I missing something obvious? Is there a better way to separate the override that the Filing Codes mean for the formula??


      // Variable Images
      var text LoggingHeader = "<img src=\"https://****.quickbase.com/up/bp9dy8gr2/g/rb/eh/va/logging.jpg\", width=\"700\">";
      var text AllocatedHeader = "<img src=\"https://****.quickbase.com/up/bp9dy8gr2/g/rc/eh/va/Allocated.jpg\", width=\"700\">";
      var text AwaitReplyHeader = "<img src=\"https://****.quickbase.com/up/bp9dy8gr2/g/rc/eh/va/awaiting_reply.jpg\", width=\"700\">";
      var text ClosedHeader = "<img src=\"https://****.quickbase.com/up/bp9dy8gr2/g/rc/eh/va/closed.jpg\", width=\"700\">";
      var text RecordsHeader = "<img src=\"https://****.com/up/bp9dy8gr2/g/rc/eh/va/records.jpg\", width=\"700\">";

      // Logic and UI
      Case ([Status],
      "Logging" ,
      If (([Event - Filing Code])<>"" and ([Filing Code])<>"", $LoggingHeader, $RecordsHeader), $LoggingHeader,
      "Allocated" ,
      If (([Event - Filing Code])<>"" and ([Filing Code])<>"", $AllocatedHeader, $RecordsHeader),  $AllocatedHeader,
      "Awaiting Reply" , 
      If (([Event - Filing Code])<>"" and ([Filing Code])<>"", $AwaitReplyHeader, $RecordsHeader),  $AwaitReplyHeader,
      "Closed" ,
      If (([Event - Filing Code])<>"" and ([Filing Code])<>"", $ClosedHeader, $RecordsHeader), $ClosedHeader)

      ------------------------------
      Owen Morgan
      ------------------------------
      • OwenMorgan's avatar
        OwenMorgan
        Qrew Member
        6 years ago
        I created a bool to combine the two filing code fields, called "has no records filing code?" and is true when both fields are blank
        so working with that I tried this but I can't close it properly

        // Logic and UI
        Case ([has no records filing code?]=true,
        Case([Status],
        "Logging" , $LoggingHeader,
        "Allocated" , $AllocatedHeader,
        "Awaiting Reply", $AwaitReplyHeader,
        "Closed" , $ClosedHeader)
        , $RecordsHeader)

        ------------------------------
        Owen Morgan
        ------------------------------