Skip to main content
Solved

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

  • January 30, 2022
  • 6 replies
  • 83 views
G

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?

Best answer by ScottWorld

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

{Account Status} & ""

    6 replies

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9823 replies
    • Answer
    • January 30, 2022

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

    {Account Status} & ""

    G
    • Grace822
    • Author
    • New Participant
    • 4 replies
    • January 30, 2022

    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?

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • January 30, 2022

    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"
)
    G
    • Grace822
    • Author
    • New Participant
    • 4 replies
    • January 30, 2022

    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.

    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
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • January 30, 2022

    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.


    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
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9823 replies
    • January 31, 2022

    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?


    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.


    Terms & ConditionsAccessibility statement