Dynamic Checkbox Lists?

AppSheet AppSheet Q&A
1

Hi all. I am trying to create an internal training management app.

The idea is that you have an employee table, each employee belongs to a department and has a job title.

Like… John Silver is from engineering department and is an Engineer.
Jake Gold is from engineering department and is a Senior Engineer
Mike Copper is from HR and is HR Manager.

Etc.

Then I will create training items (ie: Quality Inspection Training). And I want to assign Departments, Job Roles and People to that Training Item, so they will HAVE to do that training.

So, I can select an employee. And only those selected will have to do the training.

Or I can select some job titles, and anyone with those job titles will have to do the training.

Or I can select Departments, and everyone in those departments will have to do the training.

My ideal solution would be to have 3 dynamic lists (since items can change) with checkboxes.)

But I can only think of how to do this with enumlist. And an Enumlist would create a text with items separated by commas… not sure how I would be able to extract the info about which departments, job titles or people were selected, if the selection became a text separated by commas.

Another way would be to have a child table. But then instead of quick selecting from a list, I would have to add items, and then select a single item from a list and save.

2

I would create a virtual column on your employee table that selects the related trainings that the employee has to do. The formula for the virtual column would look something like this:

SELECT(Trainings[Key Column],
    OR(
        [Employee Requirement] = [_THISROW].[Employee Name],
        [Department Requirement] = [_THISROW].[Department],
        [Job Title Requirement] = [_THISROW].[Job Title]
    )
)

I’m assuming your employees table has the employee name, department, and job title. And that your trainings table has the Requirement columns. Those columns could use a valid_if or suggested values to be dropdowns of possible employee names/departments/job titles. See this article: https://help.appsheet.com/ux/dropdowns-in-forms/dropdown-from-valid_if

3

Ok, but the Requirement columns can have several items.

Like… Requirements for X item may be Financial Department, HR Department, General Manager job, Internet job plus Bob and Ann.

Your formula seems to work to single items, not lists. Like if I would create a training for a single employee, a single department or job title.

I get the message “Cannot compare List with Text in ([ReqNomFunc] = [Key].[Nome])”

I guess that instead of selecting from a Valid If Dropdown, I would need to create a child table for selected items (department, people, etc).

Which is like the dillema I posed on the OP

4

@Rogerio_Penna If you want to check if a single value is in a list, you can use IN([Some column], [Some enumlist column])

5

tony:

IN([Some column], [Some enumlist column])

should i use “IN” inside the Select??

SELECT(Trainings[Key Column],
OR(
[Employee Requirement] = [_THISROW].[Employee Name],
[Department Requirement] = [_THISROW].[Department],
[Job Title Requirement] = [_THISROW].[Job Title]
)
)

6

If the requirements are EnumLists, then instead of doing [Department Requirement] = [_THISROW].[Department] you should do IN([_THISROW].[Department], [Department Requirement]) inside your filter condition.

7

Thanks Tony, I did and it worked.

However, I am not sure how I go on from here.

So I can see which trainings are marked as that employee needing. Ok.

However, I must think of a way of creating training sessions and assigning those people who have the requirement of such training and still havent done it, as well as when a course is “done” by that employee, it should move to the employee list of completed courses/trainings.

Which is why I am not sure “lists” are the best way to deal with that.

Maybe a many-to-many table?

Have 3 child tables (Department, Job, Employee) linking to the Training Item.

So when looking at the training item, I can see 3 tables and assing departments, jobs and employees for each table.

MAYBE it would even be possible, if I assign a department, to automatically fill the 2nd table with every job that belongs to that department, and the 3rd table with every employee belonging to those jobs?

Now, it would be important to be able to go into the employee records and see what training requirements are pending, and then select it from INSIDE the employee record to create a new training session.

When creating new training session, I should be able to select training items and then include in the session only people individually, or by job or by department, required for all the training items in that training session.

Man… this is escalating quickly…

8

If I want to add multiple items to a single column of a row, only options are either having another table that is PART OF the first table, or create a list?

because I do am not sure I want to have the lists of departments, jobs and employees being a part of the training item.

Yes, a training item may have multiple employees, but an employee may have multiple training items.

This should be a many-to-many relationship, right?