Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: How to convert a lookup column to a multiple-select column

1949 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Emir_Boukraa
4 - Data Explorer
4 - Data Explorer

Hi everyone !

I’m using the lookup tool but i want to know if it’s possible (maybe with a formula) to convert what’s inside the lookup column into a multiple-select column ?

Thank you !

5 Replies 5

Change the field type from Lookup to Multiple Select. Airtable will maintain the existing data, and just change the way that data is selected via the new field type.

But will it continue to lookup data ?

No. You asked how “to convert what’s inside the lookup column into a multiple-select column,” which sounded to me like you wanted to permanently switch the field type from one to the other.

A field can only be a single type. It can either be data that you create/choose (single/long text, number, currency, single/multiple select, etc) or it can be pulled/calculated based on data elsewhere in your base (lookup, rollup, formula, etc). It can’t do both things at once.

Could you describe in more detail what you’re trying to achieve, and the current setup of your base and tables?

I came to this post looking for what I believe Emir (OP) wanted to do, which is:

Have the lookup field treated as a Multiple select field for downstream usage, in particular, I want to use the result of the lookup field as rows in a pivot table, but since the lookup result is treated as text by the pivot table, I end up having one row for each possible combination of lookup results.

Let’s say I have an menu-items and an ingredients table, I’m gonna lookup the ingredients from the ingredients table in the menu-items table via a look up, so I can summarise how many times each ingredient appears using a pivot table, but as it is, I get a row in the pivot table for “tomatoes, onion” vs two rows, one for tomatoes and one for onions. If the lookup result was treated by the pivot table as a multiple select I’d get the result I want, is there a way to get the pivot table to treat the result of the lookup as if it was a multiple select?

Welcome to the community, @Arturo_Sanchez_Corre! :grinning_face_with_big_eyes: I’m not aware of a way to do what you want. The way that any Airtable block interprets the data coming from a field is determined by whoever wrote that block. The data type can’t be overridden anywhere.

To put a more fine point on it, what you want isn’t necessarily for a lookup to act like a multiple select. What you want is the same type of data that a multiple select delivers, which is an array. In some cases, a lookup will deliver an array; other times, including this one, it delivers a string.

That aside, you might consider using a rollup field instead, with an aggregation formula that just reads “values”, meaning that the data is left as an array, and that array might be delivered intact to the pivot table block.

There’s an ever-so-slight difference in the way that lookup and rollup fields collect data and present them elsewhere. I’m doing a deep dive into the two to compare them, and will be sharing my results once I’m done. In the meantime, try changing your source field from lookup to rollup and see if it makes a difference. It’s worth a shot, at least. While I might not always get the results I want from a test, testing always teaches me something that I can use down the road.