Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 12, 2023 10:23 AM
Hi,
I have been trying to create a status field that uses a formula to indicate when a project is completed
Dec 12, 2023 10:45 AM
Hey @haileyfleury!
There's a couple ways to write this formula, but the most direct, valid version of the formula you've provided in your screenshot is something like this:
IF(
{task assigned?},
'in progress',
IF(
{% Complete} = 100,
'complete'
)
)
There are two issues with the formula you provided that need to be fixed in order for the formula to be valid. The first issue is that your formula is made up of two separate IF() functions. These functions' returned values need to be either joined or nested into a single parent IF() function.
The formula I provided you is an example of a nested structure.
The second issue is that you've added a '%' character to the back of your evaluation parameter for your second IF() function. While Airtable might format a field as a percentage, the value is actually just a number (integer). Because of this, Airtable's formula runtime sees the '%' as an invalid character.
You can test this by adding a '%' character to the formula I provided. Airtable will throw an error in response.
Removing the character will resolve this issue.
An alternative structure to the formula I provided would be to join two separate IF() functions. This is more along the lines of the formula you provided, but there are potential issues with using this structure.
IF(
{task assigned?},
'in progress'
)
&
IF(
{% Complete} = 100,
'complete'
)
When using this structure, it's very likely that you will see records return the following formula value:
in progresscomplete
Since the functions are not nested, there is not a way to guarantee that both conditions cannot be met and therefore both values returned in a malformed, unintended way.
A potential solution might be to swap the first formula I provided you with this:
IF(
{% Complete} = 100,
'complete',
IF(
{task assigned?},
'in progress'
)
)
Here, we ensure that if the % Complete field value returns 100, that the only possible thing that could be returned from the formula is your intended complete value, regardless of the condition returned by the task assigned? field.