Enum List from two columns in the same table

[leonsteber]

Hello!

I have a table called PlantNames :

PlantNames_ID	PlantNames_Common Name	PlantNames_Genus	PlantNames_Family	PlantNames_Other
2b297f68	Adlai	Coix	e7bdf220
ee5e11a6	Alfalfa	Medicago	8469d115	Lucerne, Alfalfa Sprouts
109e44b8	Amaranth	Amaranthus	b35b8972
de275406	Ampalaya	Momordica	04a9c598	Bitter melon
c1c50b83	Artichoke	Cynara	5a2f3db1
2168f717	Arugula	Eruca	570a120d	Rocket, Rucoli, Rugala
07dcf2f5	Asparagus	Asparagus	8b220af1

I have a referenced table called Seeds, which has an ENUM dropdown referenced to PlantNames, the suggested values is:

orderby(PlantNames[PlantNames_ID],[PlantNames_Common Name])

This works lovely.

But what I really want is suggested values from the [PlantNames_Common Name] plus the non-blank [PlantNames_Other] column, all in alphabetical order.  The [PlantNames_Other] also has to be "split" so that, for instance, the drop down will contain all of these: Arugula, Rocket, Rucoli, Rugala with the single key value of 2168f717

I understand I could combine these lists in google sheets (a new table), but is this possible using appsheet expressions?

Cheers!

Leon

[Suvrutt_Gurjar]

Will the user select any one name from the dropdown in the "Seeds" table?

For example any one of Arugula, Rocket, Rucoli, Rugala and still the ref column  will be 2168f717 for all those 4 options?

---

[leonsteber]

Hello @Suvrutt_Gurjar 

Yes that's right;

 -  if I choose "Arugula" it gives me 2168f717

- if I choose "Rocket" it will also give me 2168f717

I did attempt making a two dimensional list with list() + list() but got a bit stuck with thinking how  it would work...

[Suvrutt_Gurjar]

Thank you.

Please try below

1. Add a virtual column called say [All_Plant_Names] in the PlantNames table with an expression something like 

LIST([PlantNames_Common Name] +[PlantNames_Other] -LIST("")

Assumes that [PlantNames_Other] is an enumlist column.

2. Add a physical column called say [All_Plant_Names] in the Seeds table just below the reference column that references the PlantNames

The valid_If  expression of this column can be something like [Ref Column in teh seeds table that references PlantNames Table].[All_Plant_Names]

3. With this , once the user selects a plant name with reference column in the Seeds table, the column [All_Plant_Names] will present all the plant name options from which the user can select the desired name.

[leonsteber]

I get back to you soon, not working yet but I see where you're heading

[Suvrutt_Gurjar]

Sure. Here is how it looks. Once the user selects the "Common or Primary"  name in the Product ID field, he gets to see and select the other name options including primary name in the next field.

[leonsteber]

Not working yet and I suspect maybe I've misled you with my description.

So the Seeds table is only referenced to the PlantNames table when the user selects from a enum ref.

1. So, say a user wants to add a new seed packet to his inventory (Seeds table), she's got a packet of "Jumbo peanuts".
2. When she fills in the form for adding the Jumbo Peanuts there is an enum ref dropdown for the common name and she will choose "Peanuts" (from PlantNames[PlantNames_Common Name]) but she also could choose "Groundnut" from the PlantNames[PlantNames_Other].
3. Then PlantNames is referenced to Seeds via this All Plant Names enum ref.

So from following your suggestions:

1. Virtual column called All Plant Names in PlantNames (hopefully this is the expression you meant?):

When I test this I do get the expected combination of two lists (see below for confirmation of "Groundnut" which is from PlantNames[PlantNames_Other]

(I did try sorting this alphabetically and splitting the "/" but I was just getting more and more lost)

2. Add physical column in Seeds:

3. I suspect this isn't working because the reference isn't there (so can't use it) until I choose it in this column....

The result of this is a drop down with no reference back to ID, so has warning symbols. However the list is good with what I expect, see "groundnut" below (just not sorted etc yet)

[Suvrutt_Gurjar]

---

@Suvrutt_Gurjar wrote:

Add a virtual column called say [All_Plant_Names] in the PlantNames table with an expression something like 

LIST([PlantNames_Common Name] +[PlantNames_Other] -LIST("")

---

My suggested expression for the [All_Plants_Name] was as above. With your expression entire names list is being composed. My suggestion was creating a " row level" list for each plant in a VC in the same row of the PlantNames table. Then the user first selects the common name in the Seeds table using the reference column. Thereafter a list of all names for that plant is created in another column through a valid_if that pulls [All_Plants_Name] through a dereference expression. The user can then select one name from the "row level" list.

Request you to revisit my suggestions sequence. 

[leonsteber]

Ok gotcha, will give this a try later when I get home. I did try your suggestion first but wrongly assumed you’d meant whole lists (apologies!)

Can I confirm though, your first LIST() was missing a closing bracket, was this meant to encompass the entire expression like:

LIST( [PlantNames_Common Name] +[PlantNames_Other] -LIST("") )

 Thanks for your time!

[Suvrutt_Gurjar]

Hi @leonsteber ,

---

@leonsteber wrote:

(apologies!)

---

No problem. Please never mind.

Oh, sorry.  Yes, my suggestion was missing a parenthesis. It should be as follows

LIST([PlantNames_Common Name]) +[PlantNames_Other] -LIST("")

We convert the text [PlantNames_Common Name] to list by wrapping it with LIST(). Or else it cannot be added to a list.

[PlantNames_Other] is already an enumlist.

LIST("") subtracts any empty elements from the combined list.

[leonsteber]

Ok, thanks once again for your patience! I appreciate it. I see I also wrongly assumed your virtual column expression, haha. My bad. 

Ok so upon testing here's an example of what I'm getting: 

Here's my enumlist of [PlantNames_Other]:

But I think my reference between the two table is screwing things up and possibly you and I are expecting this to work in different ways. When you said: 

@Suvrutt_Gurjar wrote:

Then the user first selects the common name in the Seeds table using the reference column. Thereafter a list of all names for that plant is created in another column through a valid_if that pulls [All_Plants_Name] through a dereference expression. The user can then select one name from the "row level" list.

What I am wanting is the [All Plant Names] to completely replace any other [Common Name] input.  So it would be one input drop down enum. And [All Plant Names] will be the Reference, it will be the link that ties Seeds to PlantNames.

[Excuse the detailed description that follows, but it might help]

So another example would be if I have a packet of "Popcorn" seeds. The common name (which should be replaced by ALL PLANT NAMES) for this is basically "CORN" but a user here in the Philippines might refer to it as "Mais" (from the OTHER NAMES list) or "Maize".  So what it would be would basically just be one step/one enum.  User types in [Seeds_Name] = "Popcorn" and then chooses either [All Plant Names] = "Corn" or "Mais" or "Maize" from the dropdown enum.

Now the reason for having an [All Plant Names] drop down would be because the plant family/plant genus for all types of corn are the same, regardless of the variety (sweet corn, flint corn, popcorn) and the ultimate aim is that after you plant a certain Plant family you want to plant a different plant family the next season (crop rotations) so that diseases don't develop and you can replenish your soil.

So regarding the Seeds : All Plant Names column, whenever I tried a reference expression in the ValidIf it failed (blank dropdown):

1. Valid IF = LIST( [All Plant Names] )  gave blank enum drop down

2. Valid IF = [All Plant Names].[All Plant Names]   gave blank 

3. Valid IF = [Seeds_CommonName].[All Plant Names]   gave blank 

So if I understand correctly, these are failing because there is not yet a reference between the two tables.

So the partial success was simply using

Valid If = orderby(PlantNames[PlantNames_ID],[All Plant Names])

and then on the PlantNames table moving the "Label?" to the [All Plant Names] column.

So this works and is sufficient but really I'd been trying to put those PlantNames_Other on their own line in the drop down.

[Suvrutt_Gurjar]

Thank you for more details. However, I am sorry, I am unable to understand it.

Maybe you wish to share how it should operate from an app user point of view.

I am trying to understand what user will select , if she wishes to select only "Corn" out of multiple options for that name and all other plants. She will have to first select the common name and then other names can populate? She will search for the name "Corn?" and then all names for only "Corn" should be shown as dropdown options?

How the user will proceed with the following options and zero down on "Rucoli" for example.

[leonsteber]

Thanks for hanging in there @Suvrutt_Gurjar ! 

I was going to provide a working app example but its still a work in progress and messy and so another example will need to suffice for now. If this is too lengthy, give me a week or two and I'll show the app to you.

---

@Suvrutt_Gurjar wrote:

if she wishes to select only "Corn" out of multiple options for that name and all other plants. She will have to first select the common name and then other names can populate? She will search for the name "Corn?" and then all names for only "Corn" should be shown as dropdown options?

---

So COMMON NAME will now be your new enum [All Plant Names].

She will select (enum, ref) a common name ([All Plant Names] ) for her seeds and that's all.  Nothing else.  That [All Plant Names] column will now be the reference link between SEEDS table and PLANTNAMES table.  What I should have made clear, is that PLANTNAMES table is a pre-filled table with 240 different vegetables and their planting instructions etc (user should rarely ever have to add to it, it could almost be read only).

But, because I'm living here in the Philippines and there are different Filipino names for vegetables (eg. Mais, Maize for corn, which are ALREADY pre-filled in the PlantNames[PlantNames_Other] column) I want a user to be able to scroll down (I don't think the SEARCH is an obvious option for a new user) to whatever it is they call Corn.  If they choose CORN it will have the same ID as Maize and Mais., but in the PlantNames table they all exist on the one row.  In the drop down they will each have their own tickmark in alphabetical order

Sorry about my explanations, here's another one:

My PlantNames table is connected to another table that gives instructions on how to plant the various plant families.  What I probably should have made clear, is that the PlantNames table is pretty much a pre-filled database, the user will rarely have to add any other items to it.  It has things like CORN, CARROT, BEETROOT, etc, all very general vegetable names.  It comes with about 240 different plant common names, instructions for how to plant them, the typical weight of their seeds, etc.  This is all pre-filled; user doesn't have to add to it.

For instance, each CORN seed needs to be planted at a spacing of about 15" apart. It needs a lot of space to grow.  It doesn't matter if it's popcorn sweetcorn, or flint corn.

A CARROT can be planted at 3" spacing, you can fit many more of them into a vegetable patch than corn.

So a user has a bunch of packets of seeds that they want to plant, see this for example: 

I need to add this to my seeds inventory: SEEDS table. Imagine this didn't have the planting instructions written on the packet but just the name Danvers Carrot...  To get my planting instructions for CARROTS (for all carrots, not just this specific type of carrot) I need to make a reference between SEEDS table and PlantNames table.  The user does this by making a enum selection in the [All Plant Names] column of the SEEDS form. So now Danvers Carrots has a ref link to the PlantNames table via the [All Plant Names] column being selected as CARROT

[Suvrutt_Gurjar · Accepted solution]

Hi @leonsteber ,

Thank you very much for all the details. 

I believe now I understand your requirement.

Please try below.

1. In the "Seeds" table's [Plant_Name] enum column , please have a valid_if expression something like 

SPLIT(PlantNames[All_Plant_Names],",")

Where [All Plant Names] is the virtual column in the PlantNames table that we had created earlier with the expression

LIST([PlantNames_Common Name] +[PlantNames_Other] -LIST("")

This [Plant_Name] column is the column in the Seeds table where the user will select any one  plant name and it will consist a dropdown of all names of all plants, including common names and other names.

2. Please add another physical column in the Seeds table called say [PlantNames_Ref] ( type ref with referenced table as PlantNames) with an app formula expression something like 

ANY(SELECT(PlantNames[PlantNames_ID], IN([Plant_Name], [All_Plant_Names])))

This column will refer back to the PlantNames table and will select one key value when any of the names for a particular plant is selected. For example for   any one of the names selected by user among the names Arugula, Rocket, Rucoli, Rugala , the column  [PlantNames_Ref] will be populated with the single key value of 2168f717

You can keep [PlantNames_Ref]  column hidden.

Hope this helps. Please test well.

[leonsteber]

@Suvrutt_Gurjar Thanks so much for your help and patience !!

This worked.

Just for my future reference:

---

@Suvrutt_Gurjar wrote:

1. In the "Seeds" table's [Plant_Name] enum column

---

1. Seeds[Plant_Name] is no longer a Ref, it's just an enum with text as base type.

2. Needed to clean up my Plant Names columns to remove separators like "/" and replace with "," so that a SPLIT() works.

3. Still can't work out why I can't SORT the dropdown list. I put SORT() around the expression and it tests ok (except for some reason "Adlai" goes to the end) but when I save and test it, the drop down doesn't put the [PlantNames_Other] items in the correct order. I tried an extra VC in Seeds table to sort it but no luck with any of it.  Is it something to do with it being a list inside a list?

[Suvrutt_Gurjar]

---

@leonsteber wrote:

Seeds[Plant_Name] is no longer a Ref, it's just an enum with text as base type

---

Correct.

---

@leonsteber wrote:

Needed to clean up my Plant Names columns to remove separators like "/" and replace with "," so that a SPLIT() works.

---

Nice. Yes, this is s crucial step.

---

@leonsteber wrote:

Still can't work out why I can't SORT the dropdown list.

---

Could you share the current expression with screenshot?

I could sort it properly with the expression 

SORT(SPLIT(PlantNames[All_Plant_Names],","))

Please ensure the columns types are identical for Plant Name columns in both the Seeds and PlantNames tables.  Either "name" type or "text" type for example.

Here are the screenshots from the similar sample app.

The dropdown column

The valid_if expression 

[leonsteber]

OK! Got it finally!

So my PlantName table was originally just a copy of an Excel table I had years ago. So I think the lists of alternative names I had in [PlantNames_Other] must have sometimes had a SPACE character before or after the COMMA. 

So this was screwing up the SORT()

This is how I fixed it:

The formula strips all the errant spaces from around the commas:

SPLIT(
  SUBSTITUTE(
    SUBSTITUTE(
      LIST(
        [PlantNames_Common Name])
        +
        [PlantNames_Other]
        -
        LIST(""),
    ", ",","),
  " ,",","),
",")

Then for the dropdown in the Seeds Form:

SORT(
  SPLIT(PlantNames[All Plant Names],",")
)

My brain hurts now after all that so I'll go lie down for a couple of days haha.

Thank you once again @Suvrutt_Gurjar for all your patience, it's very much appreciated! I've got several apps that will use this same "other names" process so it's a very big help.

Thank you, thank you, thank you.

Hope you have a great day!!

Cheers

Leon

[Suvrutt_Gurjar]

You are welcome. Great to know it works as intended and you will be able to use this approach in multiple apps.