Creating list from values in same row (and in different table)

[Murielle]

Good morning,

I have a table (Plants established) with the following columns:
-name of farmer (Farmer)
-quantity of trees B (Tree B)
-quantity of trees A (Tree A)
…

I have another table (Follow-up) with the following columns:
-name of farmer (Farmer)
-Species tree A
-Tree A alive (Y/N)
-Species tree B (Y/N)
-Tree B alive (Y/N)
…

Not all the farmers have the same plants on their farm. I would thus like to create a form (based on the table Follow-up) where each “Species tree A” will give me only the species that the farmer established (Tree A >=0) (dependent dropdown).

I thought of creating a Enum variable and to create the following list (as suggested variables):
List(if(Plants established[Tree A]>=1,“Tree A”,""), if(Plants established[Tree B]>=1,“Tree B”,"")……)

-But this gives me the following error:
Cannot compare List with Number in (Plants established[Tree A] >= 1)
-It also cannot think of a way to link this to each single producer…

Could anyone think of a solution? Happy to clarify any doubt.
Thank you so much in advance.

[WillowMobileSys]

Murielle:

if(Plants established[Tree B]>=1,“Tree B”,"")

The if(s) needs to include a COUNT() function like this:

if(COUNT(Plants established[Tree B]) >=1 ,“Tree B”,"")

[Murielle]

WillowMobileSystems:

if(COUNT(Plants established[Tree B]) >=1 ,“Tree B”,"")

Hi John!
Thank you for your help.
I tried your suggestion but I receive the same error “Cannot compare List with Number in (Plantas established[Tree A] >= 1)”
Also, I am not sure whether this would do a selection by producer, would it?

Thank you!

[WillowMobileSys]

WillowMobileSystems:

The if(s) needs to include a COUNT()

You’ll notice that I had said “If(s)” - plural. You need to make the same change to any of the expressions that are using that similar pattern.

To explain further … this part of each expression, Plants established[Tree B], returns a list of all the values from the column [Tree B]. AppSheet does not automatically return the number of items in the list for you. So, by wrapping it with the COUNT function like so COUNT(Plants established[Tree B]), the expression will be given that count of items to compare with the >= 1 part.

[Murielle]

Good morning John,

Thank you for your explanation.
Unfortunately I did try the If(s) with a COUNT() in every expression, but I still keep receiving the same error code…

Also, I do not need the number of items, but the name of the item in case the producer established more than one plant. Would this make more sense?

[Steve]

Your problem description is confusing. Please describe your problem in plain language without using any AppSheet terms, and without describing how you think you might approach it. Just describe the problem.

[SkrOYC]

I’m really not understanding what are you trying to do since we have no insight of your tables schema.
I mean that we need to know wich columns are ref, and that kind of stuff.
Keep your mind open since your schema could be completely wrong for the kind of thing that you want to do. (Could)
Try to explain what’s the real life scenario and then we could help you to take that to the app

[Murielle]

Thank you for your answers.
In one table, I have the names of all the producers and the quantity of plants established on their farm (columns would thus be: producer name, #plants A, #plants B, #plants C…).
In another table, I want the technician to give me the species of the plants they find, and whether the plants are alive (columns would thus be: producer name, species plant 1, is plant 1 alive, species plant 2, is plant 2 alive, species plant 3, is plant 3 alive…).
However, not all the producers have received the same plants. I want the form to give me options of species (as a dropdown menu), but only for the species the producer has actually established. The idea would thus be to link the producer name in both tables, and to only show the plants that were established (for example, if #plants A > 1, then the species A will appear in the dropdown menu if that producer is selected).
Is this clearer? Thank you for the patience.

[SkrOYC]

Is there going to be a fixed ammount of columns (plants) for the lifetime of the app? Because this might not be the best database design.
Try to think of columns as characteristics of a thing and not the thing itself

[Murielle]

Yes there will be a fixed amount of columns/plants.
The reason for this particular set-up is that the first table is a form where producers can enter how many plants of each type they established.
Do you think that makes the dropdown impossible to create?

[SkrOYC]

Murielle:

In one table, I have the names of all the producers and the quantity of plants established on their farm (columns would thus be: producer name, #plants A, #plants B, #plants C…).

Like this?

Key	Producer	Plants A	Plants B	Plants C
bla1	John	12	12	40
bla 2	Steve	32	23	10

Murielle:

In another table, I want the technician to give me the species of the plants they find, and whether the plants are alive (columns would thus be: producer name, species plant 1, is plant 1 alive, species plant 2, is plant 2 alive, species plant 3, is plant 3 alive…).

So a technician should be able to select a certain Producer and then make an inspection, right? Which column type is [Species Plants X] columns?

Murielle:

However, not all the producers have received the same plants.

The same kind of plants or the same quantity? We defined that the columns where going to be fixed so it should be quantity (>=0)

Murielle:

I want the form to give me options of species (as a dropdown menu)

You need an species table since to this point we have been talking about Plants. Confirm the column type of [Species Plant X] (I guess it will end up being EnumList base type Ref)

Murielle:

but only for the species the producer has actually established.

Do you mean the species related to the Plants the producer has established?

Murielle:

The idea would thus be to link the producer name in both tables, and to only show the plants that were established (for example, if #plants A > 1, then the species A will appear in the dropdown menu if that producer is selected).

This is the easier part. Just center on the first one for the moment

[Murielle]

Yes your first table is exactly what I meant.

Yes, the technician should be able to select a producer and then make an inspection.
[Species Plants X] should be Enum I believe.

Not all the same producers have received the same plants (some only species A and C, some only species B), nor the same quantity.

OK, I can create a species table.

Yes, I mean the species related to the Plants the producer has established.

Thank you very much for your time.

[SkrOYC]

Murielle:

Not all the same producers have received the same plants (some only species A and C, some only species B), nor the same quantity.

So all received the same plants but not the same quantity.
Think about this on a way that receiving 0 plants equals to the fact of receive that plant, just 0 quantity.
This makes sense from the database perspective.
To not receive a plant could mean that the field is blank but we want to use numbers (>0) on those columns.
I’m right?

[Murielle]

You have a good point! You are right indeed.

[Murielle]

So would you have any suggestions based on the above?