Distinct Value Dropdowns

[anonymous8]

Hey experts,

I am new to appsheet and at the Moment i am not enterly sure if it is a problem witch is easy to solve and the way I thing about the problem is wrong.

What I want to do:
I have a list of people in a separate table.
In my data table I make daysheets where I see how many people and witch one have been there. So I have 15 rows with with data called Employee 1, Employee 2 … .
It is absolutely no Problem to create these dropdowns and that is working really fine. But I think it is really hard for the user to scroll through all the teammembers all the time.
So I want that the dropdown distinct. If you select one team member in the dropdown before it shouldnt appear in the next dropdown.

My Idea:
I have the team table and beside each team member entry with the column name “presence”, witch is a bool yes/no.
The validif of the Employee1 - 15 column is “=SELECT(Team[Name],[Presence] = True)”
At the beginning everone has the value “true”.
At the next step I wanted to change the value of the presence row of the selected employee. The problem is I have absolutly no idea how. I tryed it in the formula column and in the initial value column. Both dosn´t work. Even I am not sure if the formula is right. I tryed it with:
=if(LOOKUP([_THIS], “team”, “name”, “presence”) = true, presence=false,presence=true).

The last problem would be that if you save the entry (or after all the dropdowns) the value of the complete column have to be changed to “true” again, because if you start to fill in the next dataset all employees should be selectable again.

Now my questions:
Is this possibly to do it like this or is the beginning already wrong?
Can someone help with a suitable solution?

Thank you so much. I´d try to solve the probleme since 3 days and I dont get there.

[WillowMobileSys]

Hi! Welcome to the AppSheet Community!

I’m struggling to give a good answer because I’m not clear how you have the data setup and what your goal is for the app.

I understand that you have Employees you want to assign to a team.

Do you want to just simply show a list of Names assigned to the Team?

Or do you want to be able to manage Team members in some way entering additional info along the way?

Quick and Easy

If you just want a simple list of names associated with the Team, you can use an EnumList type column. It will allow you choose multiple Employees at the same time.

Manage Team member data

If you are wanting an app that manages team members in some way (e.g. a Project Team member where you might want to keep track of progress and hours spent), then you’ll likely want to setup a Parent/Child relationship with a Team table and a Team Members table (choosing from Employees). We can help you get this setup (its not hard), just let us know if this is more what you need.

In the meantime, you could checkout this article that describes setting up Parent/Child structure between tables. The section “Expressing Ownership Between Tables” talk about this a bit.

[anonymous8]

Hello John,

thanks for the quick reply.
Here is the app https://www.appsheet.com/newshortcut/d2c0f05f-349f-4207-afe3-f96fcd55a571.

The goal is to see the team members who worked at a specific day on a specific project, not to monitor the times because I do this with an other programm.
The names should be enouth. But in our buisness the workers change daily or weekly so the enumlist has to be a reference from another table where the user can add or delete Names.
Is this possible?

Thanks for your answer.

[WillowMobileSys]

The “quick and easy” EnumList, is probably not the way you want to go since you have a more professional app.

I recommend, in addition to the Team table and Employees table, creating a Team Members table.

In this table you will want to have at a minimum, the columns:

1. Team ID - Key to the team table
2. Team Member ID - Key for this table. I would use the UNIQUEID() function
3. Employee - A reference to the employee table for member selection.

Load this table into AppSheet. Edit the Team ID column make sure it is a Ref column and “turn on” the “Is part of” property.

This will “link” the two tables together in a Parent/Child relationship. You will see that AppSheet also automatically adds some Virtual Columns for you to access in the app - “Related Team Members”.

Now when you create or edit a team, you will see either an ADD button to add team members OR a list of selected team members with an Add button below.

Example Add buttons to add new children - Installed Products/Materials Used

Example with children that can be edited or add more - I am showing mine in tables

Once you have this structure setup, you’ll be able to easily expand it as you need to surface more information without making major changes. But more importantly, you can easily update the Team Member list and when new Employees are add or removed, the Team Member selection list is automatically updated. You’ll likely use this in many places.

Oh and back to your original question of “Distinct Value Dropdowns”

To prevent already selected employees from showing in the Team member drop down list you would update the Valid_if of the Employee column in the Team Member table. Use an expression like:

SELECT(Employees[ID], 
       NOT(
           IN([Employee], 
                SELECT(Team Members[Employee], 
                        AND([Team ID] = [_THISROW].[Team ID],
                            [Employee] <> [_THISROW].[Employee]
                        )
                )
           )
       )
)

I hope this is all clear and helpful!!