Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

Solved
Jump to Solution
576 6
cancel
Showing results for 
Search instead for 
Did you mean: 

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!
Screen Shot 2022-01-30 at 1.51.43 PM

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?
Screen Shot 2022-01-30 at 1.52.24 PM

1 Solution

Accepted Solutions

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

{Account Status} & ""

See Solution in Thread

6 Replies 6

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

{Account Status} & ""

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?

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

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.
Screen Shot 2022-01-30 at 3.04.41 PM

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.
Screen Shot 2022-01-30 at 3.12.00 PM

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.

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.