Dependent dropdown list

[Gustavo_Eduardo]

Hello, good day to everyone.

I have created a table where I have a series of data that are planned tasks.

Such a table takes referenced data from other tables.

When I already have the information selected in this table, I save the planned work.

I have created another table that should take data from the first one (planned works), that is, it should be a dependent dropdown list that takes values ​​from that first table.

Do any of you know how to make a dependent dropdown list?

I Thanks yours answers

[Joseph_Seddik]

Hola Gustavo,

In the second table, you can create a column type EnumList, base type Ref, and point it to the first table. 

[Gustavo_Eduardo]

Hello seddik Good Day, I tell you that I tried what you suggest, however it failed. In the first table, the names are already a reference to another. I choose the names of the clients based on a third table. Perhaps the "EnumList" does not work because it is about referencing with a table that is not the owner of the data (so to speak).

[Joseph_Seddik]

Can you please show us the columns of your table? Thank you. 

[Gustavo_Eduardo]

Here I detail the Enum List type column configuration, based on Ref type, indicating the first table. In the first table I already have a series of data loaded that should show them.

[Gustavo_Eduardo]

As you can see. In the first table I load the data that is referenced to other tables. Once I have loaded all the data, this data must be available in a second table in a drop-down manner.

[Gustavo_Eduardo]

Hi! Seddik, here a image.

They are two practically twin boards. One shows the commissioned jobs and their expected values ​​(what one thinks they are going to do)
In the other table, the planned work is cited and the daily advances are entered.
To do this I must be able to choose only the works that are in the first table.

[Joseph_Seddik]

OK. 

The column of type or base type Ref provides a reference to the KEY column of the target table. Therefore, the only way to reference the table "Trabajos" from another table is to add a Ref column in that other table that contains values of the "id Trabajos", not "nombre de cliente" or any other column; because "id Trabajos" is your key column.

However, it is still possible to show los "Nombre Completo de Cliente", as I understand is your goal, by just marking this column as a Label. 

Your dropdown list will actually contain values of the Key column: "id Trabajos", but what you will see displayed instead are the corresponding values of the Label column "Nombre completo de cliente". 

If this doesn't solve your problem, please explain more what do you mean by "does not work". Thanks.

[Gustavo_Eduardo]

Good day, Seddik, I tried placing "Client Full Name" as "label", however it happens that in the progress table a drop-down list is produced with repeated names since there are many works on for each client.

Is there any way to make that this list not contain repeated values?

[Joseph_Seddik]

Buenas Gustavo,

yes there is a way, but allow me to ask you pls, is your purpose is to select clients? or trabajos? if we show only one random trabajo por cliente, would that be OK to you? 
Otherwise, if you don’t want to miss trabajos but you’d like to show as well the client name you do one of the following:

1. My preferred option: separate Trabajos and Clients in two tables. Trabajos table should have a ref column to Clients. In your form you’ll have two different columns, the user will first select the Client, then select a Trabajo from the list of Trabajos that only belong to the selected Client. 
or,

2. Add a new text column in your existing table combining Trabajo and Name with an expression like:

 [trabajoID] & [full name]
and make it a Label column. 

[Gustavo_Eduardo]

Seddik, I've tried concatenating but I find it gets very cumbersome. Option 1 that you suggest is 100% preferable.

Every day, the user is going to enter progress records. So, what is going to happen is that it is going to filter according to your choices. You will first select the client, then the works available for that client.

One option that I have tried and seems to be working would be to create a "Ref" type column with a table called "Clients (Slice)" and then I would find a way that only values ​​that are present in the drop-down list appear in the drop-down list from "Trabajos" table. Perhaps with an expression for data Valid it could work.

I have tried to put in Data Valid the following expression (Valid If):

Trabajos[Nombre completo de Cliente]

When I go to the "Progress" table, the desired behavior occurs, a list is displayed where the names that have assigned works appear, however, in the list of namesm, of the clients table, that do not have assigned works, the ID appears and I would prefer that this data not they will show up.

[Joseph_Seddik]

A slice from what table? Sorry I don't understand. 

But in any case, the effect of Valid If only takes place after the user has filled in the column, and it will just accept or reject the user's input, but will not have any impact on the displayed list. 

What you need to use is the Suggested Values field.

Based on the 1st approach of having two separate tables (not a slice),  you can use the expressions below for the Trabajo column.

• Show field: ISNOTBLANK([clientID])
• Suggested Values:
  [clientID].[related Trabajaos] 
  or:
  SELECT(Trabajos[trabajoID], [clientID] = [_THISROW].[clientID])

[Gustavo_Eduardo]

Perfecto, voy a probar con esto. Estamos más cerca!

[Gustavo_Eduardo]

Another good option that occurs to me is to make dependent drop-down lists in a form in which the fields are displayed according to a condition

For example:

1. Select the full name of the client in the form of the progress table, then,

2. appsheet look for that name in the work table,

3. if it finds it, then show the next field "jobs", otherwise the next field would not be shown.

This seems simple to me to do at one point, placing the expression in data valid: Select(Works[Works],[Customer Full Name]=[_Thisrow].[Customer Full Name]

This prevents the user from moving forward if there is no job available to add.

However, I don't know how to configure the show if. Do you know any expression?

[Joseph_Seddik]

I've never tried the dependent drop-down in my apps, and wouldn't use it since for me it is counter-intuitive from a data-modelling point of view, and because I also almost always prefer depending on behaviours that I can explicitly control rather than on how would a platform automatically infer the desired behaviour. 

But, this is only a personal preference. I'm just saying that I haven't used so as to be able to recommend it. 

[Gustavo_Eduardo]

Buen punto pruebo y te comento Seddik muchas gracias por el consejo

[Joseph_Seddik]

De nada amigo.

Joseph 🙂

[Gustavo_Eduardo]

Ok Seddik, I'm almost solving the problem.

I have managed to create a forecast table and a progress log table. Both are practically twins, the only difference is that the second one (the advance table), only allows records that are as planned. The charging process is done in a chain. Everything goes well, except for a yellow exclamation mark that appears in a field when choosing.

Do you know what could be causing this?

[Joseph_Seddik · Accepted solution]

The triangle indicates a broken reference and I see two reasons for you to get it:

1. You can only reference the key column in the target table, so unless "Descripción" is the key column in your "Trabajos" table, which I highly doubt, referencing will not work. To construct the reference, the value of "Descripción" column in "Avances" will be looked for among the the values of the key column in "Trabajos", not any other column.
2. Ref column should have a single text value, since they key column in the target table will always be a single text value. However, your app formula will return a list, even if it contains a single item, it will be a list. Therefore, when you use commands that returns a list (like SELECT) to get a single value, you should also convert it using for example ANY(). 

Also, I was going to tell you that you should not be having two tables of identical column structure because one table is a subset from the other in term of rows. Instead you should use a slice.

May be I'm wrong, but from your captures it seems that you are using a slice for that purpose. If this is the case, then you do not need to do any referencing between the table and its own slice. 

[Gustavo_Eduardo]

I must say that I have a friend in the distance. I really appreciate your time and dedication Seddik, I wish I knew a little more than you to return so much help. Sincerely very happy with everything you have done for me on this platform. Thank you very much again.

[Joseph_Seddik]

Thanks much Gustavo, your friendship fills my heart with joy --<@

I've never been able to help the many admirable people who helped me here when I started learning AppSheet, so I'm returning their favor by helping others the same they did to me. Soon you as well will be helping many many others 🙂 

I really appreciate this community for its wonderful people. I've been in many other technical communities and can truly say this one is almost one of a kind. 

Mis amigos aquí me llaman José 🙂 

[Gustavo_Eduardo]

Genial José! Presente estaré aquí para esa gente! a mi me llaman Gus

[Gustavo_Eduardo]

Great Seddik, I'll work on it. Between tomorrow and the day after tomorrow I write to you. I thank you in advance for all your dedication. We will surely find a solution!