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.
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.
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.
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:
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!!
User | Count |
---|---|
35 | |
31 | |
30 | |
20 | |
17 |