Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 09, 2020 10:09 AM
Hi,
I’m using a lookup field which aggregates a list of single selects (containing values like : “Yes”, “No”).
Within the lookup fields, I’m using the formula : ARRAYJOIN(values)
It works great. Then I have a formula field with a FIND() :
FIND("Yes", {my lookup colomn})
The result I get with this formula is way below the amount I am supposed to get. About 3 times lower.
Even stranger, if I change the lookup formula to ARRAYJOIN(values, ', ')
, I get yet a different number.
What gives?
Thanks for your help!
Solved! Go to Solution.
Apr 09, 2020 11:18 AM
Welcome to the community, @Marine_Boudeau! :grinning_face_with_big_eyes: I think you might be misunderstanding the purpose of the FIND()
function. FIND()
looks at a string and returns the position of the first occurrence of the item you’re seeking. However, it sounds like you’re trying to count the number of “Yes” replies, which is why you’re getting unexpected results. (On a side note, it sounds like you’re using a rollup field, not a lookup. Rollup fields have aggregation formulas, whereas lookup fields don’t.)
To get what you want will require a different approach. First, change your rollup aggregation formula to this (no space between the quotes):
ARRAYJOIN(values, "")
This will create a string something like “YesNoNoYesNoNoNoYes…”, with all responses mashed together.
In the formula field that processes this string, you want to strip out all “No” results and count the remaining “Yes” results. This can be done a couple of ways, but they both start the same way.
Begin your formula with a SUBSTITUTE()
that replaces each “No” with an empty string:
SUBSTITUTE({Your Rollup Field Here}, "No", "")
There are two ways you can take that output and count the remaining “Yes” bits. One is to take the length and divide it by 3 (the length of the string “Yes”). With that, the full formula is this:
LEN(SUBSTITUTE({Your Rollup Field Here}, "No", "")) / 3
A slightly lengthier option is to wrap that first SUBSTITUTE()
inside a second SUBSTITUTE()
to replace each “Yes” with a single character, then count the length of that resulting string:
LEN(SUBSTITUTE(SUBSTITUTE({Your Rollup Field Here}, "No", ""), "Yes", "X"))
A more advanced option is to do all of this processing inside the rollup formula itself, avoiding the need for another formula field. Using the shorter of the two versions above, your rollup field aggregation formula would turn into this:
LEN(SUBSTITUTE(ARRAYJOIN(values, ""), "No", "")) / 3
That will make the rollup directly return the number of “Yes” results.
Apr 09, 2020 11:18 AM
Welcome to the community, @Marine_Boudeau! :grinning_face_with_big_eyes: I think you might be misunderstanding the purpose of the FIND()
function. FIND()
looks at a string and returns the position of the first occurrence of the item you’re seeking. However, it sounds like you’re trying to count the number of “Yes” replies, which is why you’re getting unexpected results. (On a side note, it sounds like you’re using a rollup field, not a lookup. Rollup fields have aggregation formulas, whereas lookup fields don’t.)
To get what you want will require a different approach. First, change your rollup aggregation formula to this (no space between the quotes):
ARRAYJOIN(values, "")
This will create a string something like “YesNoNoYesNoNoNoYes…”, with all responses mashed together.
In the formula field that processes this string, you want to strip out all “No” results and count the remaining “Yes” results. This can be done a couple of ways, but they both start the same way.
Begin your formula with a SUBSTITUTE()
that replaces each “No” with an empty string:
SUBSTITUTE({Your Rollup Field Here}, "No", "")
There are two ways you can take that output and count the remaining “Yes” bits. One is to take the length and divide it by 3 (the length of the string “Yes”). With that, the full formula is this:
LEN(SUBSTITUTE({Your Rollup Field Here}, "No", "")) / 3
A slightly lengthier option is to wrap that first SUBSTITUTE()
inside a second SUBSTITUTE()
to replace each “Yes” with a single character, then count the length of that resulting string:
LEN(SUBSTITUTE(SUBSTITUTE({Your Rollup Field Here}, "No", ""), "Yes", "X"))
A more advanced option is to do all of this processing inside the rollup formula itself, avoiding the need for another formula field. Using the shorter of the two versions above, your rollup field aggregation formula would turn into this:
LEN(SUBSTITUTE(ARRAYJOIN(values, ""), "No", "")) / 3
That will make the rollup directly return the number of “Yes” results.
Apr 09, 2020 12:09 PM
Hi Justin. Thanks so much for this and your quick reply. I fixed the topic of my post, yes, I had meant Rollup instead of Lookup.
What I don’t understand is why I have to go substract the other words instead if just counting the values I am interested in. The reason I ask if because I don’t just have “Yes” and “No” but I also have “Partial” and “N/a”.
Would you recommend the same solution?
Thank you!
Marine
Apr 09, 2020 01:01 PM
I got it, I did that and it works perfectly. Thank you! Though interested if there’s a better way to do it!
LEN(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
{Support accessible},
"No", ""
),
"Partial", ""
),
" ", ""
),
"n/a", ""
),
"–", ""
),
"Yes", "X"
)
)
Apr 09, 2020 02:13 PM
That’s pretty much the best option for now. Airtable formulas don’t currently have a function that counts the number of occurrences of one string inside a larger string.