Conditional Dates Derived

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a project and tasks table is related. I am creating a number of tasks from projects as part of the master detail arrangement.  Works well. In order for me to use a start date on the task, I am using  a formula which basically looks at a task and then applies a lag time. Again works well.  An example is below. 

If(
Contains ([Task], "Ref Cases: Request quotation"), WeekdayAdd([In Store Start Date],14),
Contains ([Task],"Ref Cases: Issue PO Upload"),  WeekdayAdd([In Store Start Date],12),
Contains ([Task],"Ref Cases: Manufacture begins"),  WeekdayAdd([In Store Start Date],9)
)

There are about ten such tasks.  However these tasks sometimes may need to have a different base date (eg  completion date) in case of certain projects. I can add a 'dev type' condition which would make the formula very long.  I was hoping if I could test the dev type right in the beginning  and then apply the right formula. Are VAR of some use?

So my condition will be a if it is a new development,    
Contains ([Task], "Ref Cases: Request quotation"), WeekdayAdd([Completion Date],14), 

In all other case: 
Contains ([Task], "Ref Cases: Request quotation"), WeekdayAdd([In Store Start Date],14), 

Will appreciate some help. 
  
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
You are on the exactly right track.  Use formula variables for readability.

var date CompletionDateBasis =
If(
Contains ([Task], "Ref Cases: Request quotation"), WeekdayAdd([Completion Date],14), 
Contains ([Task],"Ref Cases: Issue PO Upload"),  WeekdayAdd([Completion Date],12), 
Contains ([Task],"Ref Cases: Manufacture begins"),  WeekdayAdd([Completion Date],9)
);


var date InStoreStartDateBasis =
If(
Contains ([Task], "Ref Cases: Request quotation"), WeekdayAdd([In Store Start Date],14), 
Contains ([Task],"Ref Cases: Issue PO Upload"),  WeekdayAdd([In Store Start Date],12), 
Contains ([Task],"Ref Cases: Manufacture begins"),  WeekdayAdd([In Store Start Date],9)
);

IF([Dev Type]="New", $CompletionDateBasis, $InStoreStartDateBasis )

After defining a formula variable with a type and a name (names only may contain A-Z in either upper or lower case, but no spaces or numbers or special characters), and ending in a semi colon, you late refer to them with a $ so Quick Base knows that you are referring to the formula variable which you defined above.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Many many thanks for such a detailed reply and the explanation.  Works fantastic.  Kind regards.