Circular reference error when trying to make my formula

Benoit_Lorent · ‎May 25, 2023

Hi all !

To explain my project : I want to make a task manager where tasks can be dependant to each other (for example a task will start when the previous one end, and so if I change the end date of this previous task, the start date of the task will be udpated too)

So I created my table like that :

The previous task of a task is linked from the "Previous task" field. The "Start Date (Not first step)" is a lookup of the "End date" field from the link to "Previous task".

My goal is that the "End date" field have a formula that verify if "Previous Task" is empty or not, if its empty that mean the task dont have previous task and so it calculate the end date from the "start date (first step)" that is entered manually. If "Previous Task" is not empty that mean the task have a previous task and so its start date is the end date of the previous task (and so, the end date need to be calculated from the "Start Date (Not first step)" field.

Here is the formula I would like to add, and Airtable dont let me do it :

I understand the problem is from the fact I can link a record to himself and so it would create a infinite loop, but this will never happend in reality, so how am I supposed to do that ?

Thanks in advance for help !

Benoît

Vic_Airtable · ‎May 31, 2023

Hi there,

To set the stage for the explanation below, it's important to note that formulas in Airtable are slightly different from formulas in a traditional spreadsheet program. In a spreadsheet, you can put a formula in any cell, and have it reference any other cell in the sheet (i.e. the row above). Airtable is a relational database so formulas are applied for the entire field (what Airtable calls a column) so that the same formula applies to every record in that field.

In this case, trying to build the desired workflow would involve using linked records (Linked record fields), lookup fields (Guide to formula, lookup, rollup, and other computed fields), and formula fields (Formula field reference) to almost get the desired result. The goal for this approach would be to pass the end date-time from one record on to the next (linked) record as the start date-time. Additionally, a duration field would be used to calculate the end date-time from the start date-time of that same record.

That last step, though, creates something called a circular reference -- basically an infinite formula loop which would likely crash your table. Because of this, Airtable doesn't allow these to be created.

I would recommend using a Gantt view instead, as you can accomplish something similar to what you're looking to do by using dependencies and adjusting task dates within the Gantt chart itself. The one catch here is that both the start and end date fields should be manual -- not formulas. As soon as you use formulated date fields in a Gantt chart, you can no longer click and drag projects to change their date values.