how do you bring values from a lookup field into a single select field

[stephanie_crock]

use case, crop planning.

Tables:
1. crop reference (list of crops, planting times, etc.) this reduces having to put crop info (spacing)
2. inventory (i can link to a crop and add the "variety" info, quantities etc.) this worked GREAT!
3. planting schedule (date of planting, variety planted.) I just need to pull ONE variety by choosing from the varieties listed in the inventory. i want this to be fed by available inventory which i'll update regularly.

so in the planting schedule, i want to enter date planted, then choose which one variety i planted using a single select that is populated with the values of the varieties. the tables are successfully linked and i have the lookup values, i just want to choose ONE variety per planting date. 

i think i might be linking to the wrong table? or formula needed? see attached...I can't find where "crop copy" is?

[TheTimeSavingCo]

Hmm, so you want the single select field to have available for selection one option for each record you have in "Inventory", is that right?  If so, probably simplest to do via a script extension

You mentioned your tables were already successfully linked?  If you've already got a link between "Planting Schedule" and "Inventory", it seems like a possible workflow would involve using that linked field instead.  It would give you the ability to pick one variety per planting date like you mentioned, but I assume there's some business need that prevents this workflow?

[pressGO_design]

Crop Copy means that you duplicated the Crop field at one point and then forgot to rename it. Happens to me all the time.

What you're struggling with is making me wonder whether you're daisy-chaining your data correctly to achieve what you want. I'm imagining that you have in your crops table

Corn
Beans
Squash

with all their info next to it. In your inventory table, you have

Variety | Crop (linked to crops table)
Pinto | Bean
Kidney | Bean
Sweet Yellow | Corn
Dry White | Corn* 

In your planting table, what is sounds like you want to have happen is a dependent list situation - you choose the date, then Corn, then are presented with a choice between Sweet Yellow and Dry White instead of having to wade your way through the Beans and the Squash and all the other things. That's going to be difficult to do without scripting. But there is a cheat-y way to do it, and that's to use a formula field for the name of your variety that puts the crop name into it. This turns your inventory table into

Formula that concatenates Variety and Crop | Variety | Crop (this is linked to Crops)
Kidney Bean | Kidney | Bean 
Sweet Yellow Corn | Sweet Yellow | Corn 
Dry White Corn | Dry White | Corn*

And then when you type in Corn, you're presented with only the Corn varieties. 

*have I said that I know nothing about growing crops? because I know nothing about growing crops.

[stephanie_crock]

ok. we're getting somewhere.

crop ref=crops (corn and beans for example)
inventory links to crop ref and associates the crop with the variety. each record in inventory is a variety, so i have

• yellow bicolor>corn
• dent>corn
• blue lake>bean

in the planting schedule table (aka greenhouse) each record will be a date and variety. it is a great suggestion to choose a variety which will automatically lookup the crop and i have this working. but for me, i would rather say "today i'm going to plant corn, what varieties am i going to plant?" instead of "today i'm going to plant yellow bicolor"

in other words, i think of my crop plan in crops first then drill down to the varieties, and would love to be able to select from the available varieties. even though i have only 35 different crops, for each crop i have at least 3-4 varieties and the list gets real long real fast!

[stephanie_crock]

cool. so in that example you showed, the formula is in what table?

[pressGO_design]

It’s the Name field of the inventory table. 

[stephanie_crock]

ok. i feel so close!

crop table>crop name (bean, corn)
variety table>link to crop name, variety name, formula that concatenates crop and variety name
greenhouse table>link to variety table with concatenate crop as lookup field

but when i am in greenhouse table, i go to variety (linked field) and search, but when i enter "bean" in the search bar up top, i get no results and i can see the bean right there. 

assuming this is a mistake in my linking structure?

[pressGO_design]

That formula that concatenates crop and variety name needs to be the first column of the variety table. 

Link to read-only base with overly descriptive field names.

[stephanie_crock]

I really appreciate the help so far, but i'm not a programmer but I still feel like I'm not getting the results i want so i changed my approach slightly. (I did get the concatenate to work, however)

1. crop table (crop name, field dates, general info)
2. varieties (linked to crop name to associate the crop with it's varieties) also contains inventory numbers.
3. greenhouse (currently a list of crops and their planting dates

from the greenhouse list, i want a place to pick which variety i ended up planting from my available inventory (in the variety table). i was able to get the available varieties to show, but can't figure out to select just one? i can also create a linked field that i can see ALL the varieties, but I just want the ones associated with the particular crop. (so i don't pick romaine tomatoes)

it seems to me when i try to grab a linked record, I should be able to type in "tomato" to find all of the tomatoes, but i think my linking is wrong and it gets confusing when airtable automatically adds fields to source tables.

[pressGO_design]

I think you’re getting stuck on linking the Crop table to both the Varieties and the Greenhouse tables. Crop links to Varieties and Varieties links to Greenhouse.

I’m  not seeing the concatenate formula in the screenshots you sent, and that’s the thing that is going to allow you to type Tomato in your Greenhouse table and be able to choose from the Tomato varieties that you have on hand. So you need to duplicate the first column of your Varieties table, rename the first column VarietyID and put the concatenate formula in that column. Otherwise it’s not going to work.