Lookup a date on another table and work it into a formula

[James_Fisher-Ma]

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?

[Jeremy_Oglesby]

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

[W_Vann_Hall]

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.

[James_Fisher-Ma]

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()
    )
    )

[W_Vann_Hall]

James_Fisher-Martins:

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

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:

[James_Fisher-Ma]

That sounds like a fun evening in :joy:

Does make me feel better about my 7 indentations.