How to choose or extract value from a list of values?

[Grace822]

Hi, I have been trying for days and looked through previous posts but haven’t been able to find an answer.

I created a linked table that shows how many engagements (essentially projects) per client account.

NOTE: all of these records are completely made up and using as examples!

Client accounts may have both active and inactive engagements, because there might be more than one engagement (project) assigned to an account. But my objective here is to show the account’s status as ‘Active’ if there is at least one active engagement.

I got to this point, but how can I default to ‘Active’ if the field contains both active and inactive?

[ScottWorld · Accepted solution]

In your formula, where it says {Account Status}, change that to:

{Account Status} & ""

[Grace822]

That worked! Still getting the hang of formulas and I want to understand the logic here, i.e. what I was doing wrong. Would you mind explaining?

[kuovonne]

Do the records in your Engagements have a field that determines if the are active or not? If so, I recommend using a conditional rollup where you rollup only active engagements.

IF(
   COUNTALL(values) > 0,
    "active",
    "inactive"
)

[Grace822]

Yes, they do. In my Engagements table, each shows where they are in the pipeline. Then based on that, they are either a Lead, Closed, [on] Hold, or Archived. Finally, based on those, the engagement is either Active or Inactive status.

In my Accounts table, I did a rollup of engagements for each account. When I applied the conditional rollup formula, linking to the Engagements table and the Status field, it yielded ‘active’ on all.

[kuovonne]

The formula that I provided is not the condition. You need to set the condition in the configuration of the rollup, so only active engagements are rolled up.

[ScottWorld]

Grace822:

That worked! Still getting the hang of formulas and I want to understand the logic here… Would you mind explaining?

Sure!

The logic here is that the “FIND” function only searches through text strings, but a Rollup is actually an array of values.

So, by adding the extra quotation mark at the end, Airtable ends up converting the array into a text string.