Single & Multiple Select from Lookup or Rollup -> Arrayjoin(values)

[Avana_Vana]

It would be really useful if there were a way to dynamically create single and multiple select options using the rollup function on another column. For example, in one of my tables, I have a list of geological formations each of which has a set of dominant lithologies. A given formation might consist of 1-4 different lithologies. In another table, I have a list of rock samples. I link each rock sample to the geological formation from which it was collected, and it would be great then to be able to choose the exact lithology of the sample from the 1-4 dominant lithologies listed in that sample’s parent formation, rather than choosing the lithology from the list of all lithologies (there are hundreds).

I can add a rollup column and format it using arrayjoin(values), which gives me a printed array of the 1-4 values of the linked record on the other table, but there is no way then to take those array values and turn them into single or multiple select options for another column.

I imagine this would be exceedingly useful for people besides me, especially with use cases where a user first chooses or links a country and then chooses a state/province, or first a state/province and then a county.

TLDR:
Let us use array values from the rollup formula to populate single/multiple select columns.

[Kevin_Smith]

Our CRM base has a similar use case:
To enter an Activity, we wish to first search/select the Company, then search/select for a Contact within the specific Company.
The Lightweight CRM template supplied by Airtable does this a different way - to enter an Activity, it has the user search/select for a Contact, and then a rollup field uses ARRAYUNIQUE(values) to pull in the Company associated with that Contact. Very neat, but our users prefer to follow the sequence Company then Contact.

[Avana_Vana]

I have realized my original idea as expressed here doesn’t actually make sense, because a multi-select column aggregates all of the values used in all its cells.

But this is a real and important use case. If you have a country and a state/province field, currently you have to lump all of the world’s provinces into one enormous list, ie US States + Canadian Provinces + Russian Oblasts etc, so that a user can select a country like “Canada” and then choose “Alberta” under state/provinces. This is unmanageable, as it creates a list 1000s of items long, and it’s prone to many errors. You need to be able to dynamically enumerate the options available in any row based on a lookup field. This is actually a new feature, not something that makes sense with the existing multi-select column. Or else, what Airtable needs to do is add a toggle option to the multi-select column type that says “Lookup options from Linked record(s)”, and then asks the user to choose the linked record column, and then the lookup field, from which to look up the options. Then when the user goes to fill in that column’s cell in a particular row, it looks at the linked field, for example ‘country’, and then displays options returned from a field in the other table.

I thought for a minute this could be done with the new “Limit linked fields to a view option”, for example you could create a bunch of views that filter a list of contacts by company, and then expect to choose only from that filtered list of contacts depending on the company, but since you apply this at the column level, it would end up being limited to the same company for every row. So that doesn’t work. You would need to dynamically choose/create views based on the company linked record value.

I think the next best thing besides the toggle option I described would be if somehow Airtable allowed users to format a rollup field as a multi-select, so that I could do a rollup on a field that returns an array of options and displays them as multi-select options.

[Avana_Vana]

That’s all well and good from Airtable, but they miss the problem that arises when a Contact can belong to multiple Companies. And in the context of a single Activity, the contact is only acting in the interests of a single company, so returning an array doesn’t work. Users need to be able to choose which of the companies in the returned array is associated with the Activity.

And this might sort of work for contacts as long as each contact only has one company, but in the case of the extremely common pattern of storing addresses with Country, State/Province, you can’t possibly have a list of all the world’s states/provinces/regions/departments/oblasts/territories/etc in a huge list 1000s long, that can then be used to lookup a country value. It’s totally unmanageable. And this is why you prefer to search companies over contacts - because there may be many, many more times the number of contacts compared to companies. And you need that hinting. Plus, people can easily have the same name across different companies, and you need to be able to tell one John Smith apart from 4 others in different companies (not much you can do about two in the same company).

I can’t believe this isn’t a core function in Airtable, given how common this pattern is in CRMs and in Order Forms and many other models.