Forum Discussion

ChristineKirk's avatar
ChristineKirk
Qrew Cadet
7 months ago

Formulas Question - Complex formulas using multiple factors

Hello,

I'm dipping my toe into formulas - to date, I've used form rules to accomplish what I think formulas can, in a way that feels like the long way around (and requires you to actually open a record/save it, to fire the changes - whereas I believe formulas will just always be current with no manual intervention needed - please tell me if I am wrong).

My formula is complex with 4 choices - and each choice is a calculation of a couple of factors.

TL;DR: How do I factor in 4 choices that each use an AND function (how do I write "AND" in quickbase.... this is something I'm struggling to Google) - and/or is there a way to use an OR function, to get to each choice?

Detail:

I'm looking to say, "If this AND that, OR the other, then say option 1; if not, then if this2 AND that2, then say option 2; if not, then if this3 AND that3 AND not this other thing, then say, option 3"

How do I get the functionality of AND in this context? Alternatively, I think I could get to this if I have an OR function as well

If this still doesn't make sense the way I'm asking, here's further detail:

The field ultimately is, "What formal services are available in this county (across all hospitals)?" based on two data points - the county either has hospitals with my program, are hospitals with state-certified programs, both, or neither.

  • Prep:
    • I created formula fields to count the # of hospital records with my program; # of hospitals that are state-certified; # of "hospitals who are both", and # of hospitals with neither

I believe the formula should basically be saying,

  1. If the county's # of hospitals with my program is greater than 0 AND its # of hospitals with state programs is greater than 0, OR its # of hospitals with both is >0, then "This county has Both." If not....
  2. If the county # of hospitals with my program is 0 AND its # of state programs is greater than 0, then say "This county has state certified programs". If not....
  3. If the county # of hospitals with my program is greater than 0 AND its # of state programs is 0, then "This county has my program." If not
  4. Basically if everything else is zero, then "This county doesn't have a program".


------------------------------
Christine Kirk
------------------------------

5 Replies

  • no problem, Quickbase formulas are similar to Excel but easier to write and read.  Also you can add comments for our own sanity and you only tyopically need to have one IF statement as opposed to Excel where you need to have all those nested IFs.

    IF(

    ([county's # of hospitals] >  0 and [# of hospitals with state programs] >0)  

    or

    ([# of hospitals with both]  >0, "This county has Both.", 

    [county # of hospitals with my program] =0 and [# of state programs] > 0, 

      "This county has state certified programs",

    [county # of hospitals with my program] > 0 and [# of state programs]=0, 

    "This county has my program.",

     "This county doesn't have a program".)

    .

    // by the way f you want to add a comment, then start a line with //



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • ChristineKirk's avatar
      ChristineKirk
      Qrew Cadet

      Thank you Mark! You're always so quick to reply and I appreciate you!!

      It looks like I might not have been far off the beaten path then. I'm still spitting out an error right off when I use your formula:

      Also - when you mentioned adding a comment with // -- do you mean on this thread, or in the Quickbase? Wasn't sure I followed :).



      ------------------------------
      Christine Kirk
      ------------------------------

      • ChristineKirk's avatar
        ChristineKirk
        Qrew Cadet

        Ok I think I've got it now actually. Ugh. It's just tricky, sometimes it wants a certain thing and I don't understand what it's asking for. A little practice and I'll get this one. For the life of me, I couldn't make "or" work. But I'm just going to add it as another option. "These criteria met = Choice A", "These other criteria met = Still Choice A".



        ------------------------------
        Christine Kirk
        ------------------------------