Forum Discussion

ElizabethSchlag's avatar
ElizabethSchlag
Qrew Trainee
2 months ago

IF AND Formula

I am trying to write a report formula that is triggered off a checkbox and two different dates that I would typically write an IF AND formula. Anyone know how to make this work? It is giving me an 'expecting ,' error. 

  • The Cybersecurity field is a checkbox.
  • The Engagement Begins is a date field.
  • The Cybersecurity Discovery Engagement Ends is a date field.

The logic should read if the Cybersecurity checkbox is checked AND the Engagement Begins field is blank, then the field should read "Upcoming", but if the Cybersecurity checkbox is checked AND the Engagement Begins field is not blank, and the Cybersecurity Discovery Engagement Ends field is blank, then the field should read "Assigned", otherwise show nothing.

 

formula query
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 months ago

    try this

    IF(

    [Cybersecurity] = true and IsNull([Engagement Begins), "Upcoming",

    [Cybersecurity] = true

    and not IsNull([Engagement Begins])

    and

    IsNull([Cybersecurity Discovery Engagement Ends]), "Assigned")

     

    .....

    Some notes about how Quickbase formulas work. The first is that unlike Excel, in general you don't need to nest your IFs. You just have one IF( at the beginning and you list your conditions and the result in sequence, and the formula will look for the first condition which is true.  

    The IsNull function can use can be used on (almost) any field type but it cannot be used to check for a text field being blank.

    To test if a text field is blank you would use IF(Trim([my text field])="",

    The Trim is just in case some one enters some spaces into a text field.

     

  • DonLarson's avatar
    DonLarson
    Qrew Commander
    2 months ago

    Elizabeth,

    Mark's solution is the fastest and easiest way to do this.

    Let me give you an alternative that will take an hour and is much more complicated, and then a reason why.   Instead of the Formula Text field, I suggest changing the architecture of your application to look like this.

    I assumed the table you are working on is called Engagement.

    By having a child table called Engagement History, you get much more robust reporting options and you know when and why something has changed.

    You will need some Pipelines to manage the logic but they are pretty straight forward.

    When an Engagement record is created, a Pipeline would create the Engagement History record setting the Status to New, and recording who it was that created the first one.

    Checking the Cyber Check box created another Engagement History record and you know exactly who it was that did that even if it gets unchecked later.

    And so on and so forth through the life cycle of an Engagement through Assigned and maybe something like Invoiced or Closed Out.  

    Senior management tends to really like this kind of info because they can see what is moving quickly, what is languishing in the system and so on.