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


#1

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?


#2

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


#3

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.


#4

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:
image

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

#5

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. :wink:


#6

That sounds like a fun evening in :joy:

Does make me feel better about my 7 indentations.