Skip to main content

Hi all


I’m trying to create a task table for a Training Company base.

Certain tasks will always be a few days before the deadline.


The Deadline date is in Course Info table.

The Print Course task will is in Tasks.

Print Course will always be 3 days before the deadline.


So how do I make a formula that looks at the Course Info deadline then minuses 3 days from that in Print Course?

Do you make a new “Print Course” task for every “Course” you make and link them?

Given the following assumptions



  1. Your [Course Info] table has a {Course} field identifying the course and a {Deadline} field identifying the deadline date.


  2. [Course Info] is linked to the [Tasks] table using a many-to-one link.

  3. Within [Tasks], there is a {Task Type} field that for some records is set to 'Print Course'.


you need to define a {Print Course Date} field in [Tasks] with the following configuration



  1. A rollup field

  2. using the {Link to Course Info} field

  3. to roll up the {Course Info::Deadline} field

  4. with the following aggregation formula


IF(

    {Task Type}='Print Course',

    WORKDAY(values,-3),

    BLANK()

    )

That should return — on 'Print Course' task records only — the date three workdays prior to {Course Info::Deadline}. (A workday is defined as a non-weekend, non-holiday day. The syntax above only skips weekend days; to step past holidays as well, you’ll need to add as a third variable a comma-separated list of ISO-formatted holiday dates, as explained here.


If those assumptions are more than a little off, explain how, and we’ll try this again.

Great!

I managed to get that to work.


I’ve use the Sales Pipeline tutorial as guide for the recurring tasks.

In that they use a dropdown list for the tasks.

I have these:


I nested the IF statement to look like this:

Do you know of an easier way to do that? There are 7 options.


IF(    

    {Task}='Print',

    WORKDAY(values,-3),

    IF(

    {Task}='Produce',

    WORKDAY(values,-10),

    BLANK()

    )

    )

Great!

I managed to get that to work.


I’ve use the Sales Pipeline tutorial as guide for the recurring tasks.

In that they use a dropdown list for the tasks.

I have these:


I nested the IF statement to look like this:

Do you know of an easier way to do that? There are 7 options.


IF(    

    {Task}='Print',

    WORKDAY(values,-3),

    IF(

    {Task}='Produce',

    WORKDAY(values,-10),

    BLANK()

    )

    )


I’m afraid that’s probably your best bet. (For complicated formulas, I compose them offline in an editor, using indentation to make it easier to write and debug. Once I’m done, I copy-and-paste the formula, with indentation, into the formula configuration field. Airtable will accept the indentation; even better, Airtable will preserve it, allowing indented formulas to be copy-and-pasted from Airtable.)


If it’s any consolation, I recently had an IF() statement with 468 options. :winking_face:


I’m afraid that’s probably your best bet. (For complicated formulas, I compose them offline in an editor, using indentation to make it easier to write and debug. Once I’m done, I copy-and-paste the formula, with indentation, into the formula configuration field. Airtable will accept the indentation; even better, Airtable will preserve it, allowing indented formulas to be copy-and-pasted from Airtable.)


If it’s any consolation, I recently had an IF() statement with 468 options. :winking_face:


That sounds like a fun evening in 😂


Does make me feel better about my 7 indentations.

Reply


Terms & Conditions