Issue with Lookup values in Formula Field

Hm, could you talk a bit about what your formula was trying to do?  I’ll see if I can replicate it on my end

I had a weird one where a formula field that was referencing a rollup using ‘ARRAYUNIQUE()’ errored out around the same time.  Specifically, I was using a the formula field to reference it and converting it to single select options but it just wouldn’t recognize anything.  Was working fine for months before that too

In investigating the root cause, I found that when a Lookup field is text and used in a formula with a single‑select dropdown, the formula does not work. However, if the Lookup field is changed to a single‑select, the formula functions correctly. This behaviour was not present on Friday when everything worked fine, but today I am encountering #Error or blank formula values. I think same is Roll Up as i tried roll up formula, but it is not working.

I’m having the same problem where a rollup column was being use to populate a single-select formula column. Everything was working fine, then suddenly, with no chance on my part, things broke. All entries were set to the passthrough “default” single-select option. Debugging shows that the rollup formula is still outputting the same data. Seems like an internal implementation change/error with the “Change formula output to single select options” feature. 

I am having an issue with a formula field that is set to output single select options, it’s showing the fallback option despite meeting the conditions to output a different option.

That’s precisely the problem I’m having, too.

To partially resolve this issue from your end, if you are using a Lookup field with a text data type in a formula that references a single‑select field, please change the Lookup field’s data type to single‑select. This adjustment will automatically fix the formula field.

To partially resolve this issue from your end, if you are using a Lookup field with a text data type in a formula that references a single‑select field, please change the Lookup field’s data type to single‑select. This adjustment will automatically fix the formula field.

One of the fields used in my formula was a rollup of a single select, I changed it to a lookup and that did fix my formula field issue. However, it doesn’t feel right that we should need to change field types that had previously worked correctly just because something changed on Airtable’s end?

Yes, there has definitely been a change from Airtable’s side. It isn’t strictly necessary to modify the field type, but if users are affected, this serves as a quick fix. Fields that don’t impact users can be left as they are.

In my case, I had a single-select column being referenced in another rollup column. The result of the rollup was being used to determine a third formula column, using the option to output the value of the formula as a single-select value.

A couple weeks ago, this setup worked as expected. Then, without any change on my part, the third formula column started returning only the fallback value, regardless of the input received.

I took the advice here and changed my approach to use a lookup column instead of that third formula outputting a single-select value. In my case, I was able to do the same algorithm, but now there are fewer intermediary steps. With this change, only a single column was needed to do the work of the previous two.

The insight came in understanding that a Lookup can return multiple single-select values when referencing a set of multiple linked records. From there, I was able to use the filtering and sorting options to get the output I needed by operating directly on this list of single-select values. Feels a bit odd in implementation, a lookup returning multiple values, but once you understand this is possible, the rest comes naturally.

While this workaround was successful in my case, the fact remains that some internal change caused a regression in the expected behavior of the fomula-to-single-select functionality. Hope the teams gets that one sorted out soon.

See this sequence of screenshots for context on the Lookup single-select approach:

In my case, I had a single-select column being referenced in another rollup column. The result of the rollup was being used to determine a third formula column, using the option to output the value of the formula as a single-select value.

A couple weeks ago, this setup worked as expected. Then, without any change on my part, the third formula column started returning only the fallback value, regardless of the input received.

I took the advice here and changed my approach to use a lookup column instead of that third formula outputting a single-select value. In my case, I was able to do the same algorithm, but now there are fewer intermediary steps. With this change, only a single column was needed to do the work of the previous two.

The insight came in understanding that a Lookup can return multiple single-select values when referencing a set of multiple linked records. From there, I was able to use the filtering and sorting options to get the output I needed by operating directly on this list of single-select values. Feels a bit odd in implementation, a lookup returning multiple values, but once you understand this is possible, the rest comes naturally.

While this workaround was successful in my case, the fact remains that some internal change caused a regression in the expected behavior of the fomula-to-single-select functionality. Hope the teams gets that one sorted out soon.

See this sequence of screenshots for context on the Lookup single-select approach:

 

The only challenge you’ll encounter is when multiple distinct values exist. Restricting the limit to 1 will prevent the display of any additional values that may be present

We also saw this functionality change within the past couple days.

Our setup is a formula field that is formatted with color-coded single-select options, and we noticed that the field value disappeared for all records.

Formula before (currently broken):

{Course Type Lookup}

Where “Course Type Lookup” is a lookup field that brings in a text value from a single-item link to another table.

Formula after (workaround that fixes the problem):

{Course Type Lookup} & ""

I may be wrong but it sounds like you might need to provide the predetermined set of Single Selects in the formula field. If you’ve formatted a formula field to be a single select, it will only populate the cell if the Single Select option is available.

Hi,
Lookup formula output is Array of Text, when your field type is Text.
Sometimes Airtable formula can take it as Text, sometimes not
Use CONCATENATE({Field})  instead of just {Field} in Formula
Alternatively, you can use 

"" & {Field}

(add empty string). Those methods are equal.