Dependent dropdown menu with a list that updates itself

[OldNo1]

Hello,

Is it possible to automatically update the selection from a dropdown menu? I will describe the target state.

I have two tables for a safety training session. One table with the training topics:

KEY / TOPIC / CONTENT

And one table with the trainees:

KEY / EMPLOYEE ID / TOPIC

I already have a drop-down menu in the table with the people to be trained with the topics from the table with the training topics. How can I ensure that the selection in the drop-down menu for each employee is limited to what has not yet been taught?

Many thanks in advance.
OldNo1

[Aurelien]

Hi @OldNo1

Here is the documentation with an example:

List expressions - AppSheet Help

You may want to get inspiration from it:

[OldNo1]

Okay, I’ve been trying for a while now, but without success. I’m unable to list the values in the drop-down menu based on the three components. (I’m new to Appsheet, coming from Google Sheets).

Ultimately, the drop-down menu should display the values according to the following criteria:

Table 1: KEY / TOPIC / CONTENT

Table 2: KEY / EMPLOYEE ID / TOPIC

First, the employee ID should be selected from Table 1 in the input. Then a comparison should be made with the training topics that have already taken place for this employee from Table 2 and all available training topics from Table 1. The selection in the drop-down menu should therefore be limited to the training topics that this selected employee has not yet received.

The selection should be dynamically adjusted for each employee through an evaluation, depending on which training the respective employee has already received. Once the employee has received all trainings, nothing should be displayed in the selection.

Is this even possible with a formula, or do you have to work with helper tables?

[Steve]

Dependent dropdowns are a simple tool for simple data. I don’t use them because they break too easily. To my recollection, the Valid if expressions for each involved column must be a table-column reference (not any other expression) and the columns must be physically adjacent to each other, etc. You can implement your own dependent dropdown with a more sophisticated Valid if expression. To help you with this, start with giving us the Valid if expressions the involved columns have, and explain specifically how you want the dependencies to behave. Do NOT try to explain using expressions or AppSheet jargon. Do explain using plain language, like you’re explaining it to your grandmother. This is a HUGE pet peeve of mine.

[OldNo1]

Sorry, I didn’t mean to offend anyone. I’m coming from Google Sheets and am still clueless about Appsheet. In gSheets, I would have somehow cobbled something together using helper tables. In Appsheet, that requires more expertise.

Steve, I’m not sure if you’re serious about describing it as if I were explaining it to my grandmother, but I’ll try to explain the situation better. Please forgive me if it’s not clear enough. Thank you in advance!

I have two tables.

TABLE_1:
KEY / TOPIC / CONTENT

TABLE_2:
KEY / EMPLOYEE ID / TOPIC

The columns involved are:
TABLE_1[TOPIC]
TABLE_2[EMPLOYEE_ID]
TABLE_2[TOPIC]

Description of what I want to achieve:
I want TABLE_1[TOPIC] to be linked to TABLE_2[EMPLOYEE_ID] and TABLE_2[TOPIC]. The link should be created in such a way that I select the employee ID from TABLE_2[EMPLOYEE_ID] in an input mask and all topics from TABLE_1[TOPIC] that the employee from TABLE_2[EMPLOYEE_ID] has not yet received are displayed in the TABLE_2[TOPIC] field. This means that once I have completed and saved a training session from TABLE_1[TOPIC], it should no longer appear in the TABLE_2[TOPIC] selection the next time. Once all training sessions for the employee in question have been completed from TABLE_1[TOPIC], TABLE_2[TOPIC] remains empty.

Current content of valid_IF of the columns involved:
TABLE_1[TOPIC] = empty
(this column contains all topics that are available for selection, regardless of whether any employee ID has already received training. The entire TABLE_1 is independent of TABLE_2.)

TABLE_2[EMPLOYEE_ID] = linked to another table (TABLE_3[EMPLOYEE]) as a drop-down menu to select employees.

TABLE_2[TOPIC] = linked to TABLE_1[TOPIC] to select the topic.
==> However, the goal here would be to only display the topics that the respective employee has not yet been trained on.

I apologize if this is not possible without restructuring the database. I am currently quite at a loss and would be grateful for any help in achieving this. I hope I have been able to explain it better this time and will be happy to clarify if anything is still unclear. I can also provide screenshots if required.

Many thanks,
OldNo1

[OldNo1]

I have read that you can subtract two lists from each other.

LISTS can be subtracted. So just do “SELECT(first table) - SELECT(second table)”.

Source: Dependant Dropdown - Hiding Values

Now I need to achieve the following in Valid_IF from TABLE_2[TOPIC]:
Display a list from TABLE_1[TOPIC] minus already completed instructions TABLE_2[TOPIC] from TABLE_2[EMPLOYEE_ID].

I tried to achieve this in Valid_IF from TABLE_2[TOPIC] with the following formula (without success):

SELECT(TABLE_1[TOPIC],TRUE,TRUE) -
  IN(TABLE_2[EMPLOYEE_ID],
  LIST((SELECT(TABLE_2[THEMA], TRUE, TRUE))))

However, this seems to me to be completely the wrong approach, or am I on the right track?

[Steve]

@OldNo1

Steve, I’m not sure if you’re serious about describing it as if I were explaining it to my grandmother

I absolutely was. Not sure how I could’ve been clearer. I’m done here.

[OldNo1]

Okay, I’m a little confused because my English is very poor and I have to use a translation tool. I think something has been lost in translation here. I didn’t mean to offend you, so I’m sorry.

Thank you anyway and Best regards

[OldNo1]

I found a solution that works for me and wanted to share it here in case anyone else has a similar use case. It’s a different approach than gSheets, especially if you’re new to appsheet.

SELECT(TABLE_1[TOPIC],TRUE,TRUE) -

SELECT(TABLE_2[TOPIC],[EMPLOYEE_ID]=[_THISROW]. [EMPLOYEE_ID],TRUE)

I didn’t mean to offend anyone, but I still don’t really understand what went wrong in the posts above. How the questions should be asked. Maybe it’s actually my translation tool.

Anyway. Best regards