Forum Discussion

NancyScholl's avatar
NancyScholl
Qrew Member
3 years ago

Part, but not all, of updated formula working

I have a checkbox field containing a formula.  We needed to make some changes, and not quite getting the syntax correct.  Would appreciate some help/input, please.

Here is the original formula - this worked:

If([Request Status]="Draft" and [# of WBS codes]>0 and [Related Position Number]>0 and [WBS Not Required?]=false,true,
If([Request Status]="Draft" and [# of WBS codes]=0 and [Related Position Number]>0 and [WBS Not Required?]=true,true,false))

We were given additional requirements, and tried to add on to this field with the following lines.  This updated version works when there is a WBS code, but doesn't work when there isn't one. (For BOTH Customer & No Customer access):

If
(If(([Request Status]="Draft" and [# of WBS codes]>0 and [Customer Access IS Required]=false and [WBS Not Required?]=false),
true,
If(([Request Status]="Draft" and [# of WBS codes]=0 and [Customer Access IS Required]=false and [WBS Not Required?]=true)
,true)),false)
or
(If(([Request Status]="Draft" and [# of WBS codes]>0 and [Related Position Number]>0 and [WBS Not Required?]=false),
true,
 If(([Request Status]="Draft" and [# of WBS codes]=0 and [Related Position Number]>0 and [WBS Not Required?]=true),
true),
false))


I tried mapping it out like this - maybe this will help:
Draft and WBS field - when conditions are met, this box needs to be checked (selected):
Request Status = Draft Entire line Draft & WBS field desired results
AND EITHER of these two: WBS Not Required? False and # of WBS Codes > 0 and Related Position Number > 0 TRUE Checked
WBS Not Required? True and # of WBS Codes = 0 and Related Position Number > 0 TRUE Checked
OR
EITHER of these two: WBS Not Required? False and # of WBS Codes > 0 and Customer Access IS Required = false TRUE Checked
WBS Not Required? True and # of WBS Codes = 0 and Customer Access IS Required = false TRUE Checked
Otherwise FALSE Unchecked


------------------------------
Nancy Scholl
------------------------------
Formulas and functions

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    Unlike excel you normally do not need to nest your IFs  so this might work or at least be easier to debug,

    also, you do not need to specify false, because an IF will continue to test the conditions until if finds the first one that is true.

    see if this work and feel free to post back with the errors if there are syntax errors.


    IF(
    [Request Status]="Draft" and [# of WBS codes]>0 and [Customer Access IS Required]=false and [WBS Not Required?]=false,
    true,

    (([Request Status]="Draft" and [# of WBS codes]=0 and [Customer Access IS Required]=false and [WBS Not Required?]=true)
    or
    ([Request Status]="Draft" and [# of WBS codes]>0 and [Related Position Number]>0 and [WBS Not Required?]=false), true)),

    [Request Status]="Draft" and [# of WBS codes]=0 and [Related Position Number]>0 and [WBS Not Required?]=true,
    true))

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • NancyScholl's avatar
      NancyScholl
      Qrew Member
      3 years ago
      Thank you, thank you!  We've been working on this for over a week.  I had to make a few syntax fixes, but it now works in all four cases.  Appreciate the background on formulas too.  Here's the finished result:

      If(
      [Request Status]="Draft" and [# of WBS codes]>0 and [Customer Access IS Required]=false and [WBS Not Required?]=false,
      true,

      (([Request Status]="Draft" and [# of WBS codes]=0 and [Customer Access IS Required]=false and [WBS Not Required?]=true)
      or
      ([Request Status]="Draft" and [# of WBS codes]>0 and [Related Position Number]>0 and [WBS Not Required?]=false)), true,

      ([Request Status]="Draft" and [# of WBS codes]=0 and [Related Position Number]>0 and [WBS Not Required?]=true),
      true)


      Thank you again!
      Nancy

      ------------------------------
      Nancy Scholl
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        Nice lookin' formula.

        Much easier to read now and hence easier to debug.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------