Discussions

Expand all | Collapse all

Conditional Dates Derived

  • 1.  Conditional Dates Derived

    Posted 09-04-2017 23:26
    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. 
      


  • 2.  RE: Conditional Dates Derived

    Posted 09-05-2017 00:19
    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.


  • 3.  RE: Conditional Dates Derived

    Posted 09-05-2017 00:32
    Many many thanks for such a detailed reply and the explanation.  Works fantastic.  Kind regards.