Linked Records & IF Statements causing Circular References

[Kyle_Bento]

Hey all,

We’re running into an issue with Circular References. Can you help?

I spun up a test table so you can see what we’re trying to accomplish.

In the test, the “Percent Complete” for the Monthly Goal (1st one) should actually be {Sub-Goals Percent}, but AirTable’s Circular Reference check is not letting us do this even though our IF statement prevents one.

Here’s what the formula should read:

IF({Goal Type} = “Weekly”, {Tasks Completed}/{Tasks Committed}*100,IF({Goal Type} = “Monthly”,{Sub-Goals Percent}))

Does anyone have a good workaround?

It’s important that our Percent Complete sums into one column (for reporting). I’d like to avoid manually copying anything & using Zapier (as we’d need to scale this across dozens of bases.

Thanks in advance!

[Roxanne_Tosi]

Hi,

I Have set something similar in my base but with the use of a junction table. My base has a Process (junction) table, a Task Table, and a Projects Table. Both the Task Table and the Projects Table are linked to the Process Table and the process table name field concatenate the the tasks or projects name (among other things)

I count the number of sub-tasks ( & sub-projects) with a count field. I Then have an if formula that is shows a :heavy_check_mark: , if a task status is completed. I have a roll up field that looks at the sub-task linked record field, particularly at the :heavy_check_mark: formula and have it on arraycompact(values). Next I have a formula field that counts the number of item found in the roll up field or the number of completed items. Finally I have a formula field that calculates the % of completed items.

Now i know that this doesn’t take into account you goal type because i don’t see the point of it in this formula but more in my deadlines one!

Finally here’s a little screenshot exemple of my final view to see that data

as you can see it’s not 100% perfect yet as the “Main project” % doesn’t take into account it’s sub-projects progression but that’s doable