Forum Discussion

ArshadKhwaja's avatar
ArshadKhwaja
Qrew Commander
8 years ago

Conditional Dates Derived

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. 
  
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
  • ArshadKhwaja's avatar
    ArshadKhwaja
    Qrew Commander
    8 years ago
    Many many thanks for such a detailed reply and the explanation.  Works fantastic.  Kind regards.