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 , sh...
Formulas and functions
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
    ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      6 years ago
      Owen,

      Two changes. 

      1) I put in variables to check the values from your Case Statement.
      2) I removed the extra parantheses around the fields in the If clause

      // Source Control

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

      var text Status = [Status];
      var text EventFC = [Event - Filing Code];
      var text FilingCode = [Filing Code];

      // Logic and UI

      // Debug Fields
      $Status & "<br>"&
      $EventFC & "<br>"&
      $FilingCode & "<br>"& 

      // Case Statement

      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)


      Put this into the field and check the results.  I am hoping that you will see from the Debug fields that something is amiss in the data.

      ------------------------------
      Don Larson
      Paasporter
      Westlake OH
      ------------------------------