How Would You: Create a view to score a large numb...

Luke_D · 01-27-2021 09:04 PM

I have a “People” table of several hundred people, a “Skills” table of several hundred skills those people can be evaluated on, and a “Peoples Skills” table that references both of those tables to assign a score of 0 to 3 for any one person on any one skill.

How would you go about creating a user-friendly view that lets you choose a person, then rapidly scroll through the several hundred skills to assign scores (0 to 3, currently just using radio buttons) for that person on those skills? Basically this amounts to selecting a record from the “People” table, showing a list of everything in the “Skills” table, then rapidly adding records to the “Peoples Skills” table for the selected person and the Skills I want to assign a score to. I just don’t know what’s the best design approach that lets me see all the skills at once AND which ones already have scores entered for that person in the same view.

perissf

Do you mean something like this?

I am not sure how the built-in drop down behaves with several hundred records, but you can try yourself.
Simply create the associations between the 3 tables (for the third one also make a virtual column concatenating the refs to the other tables in order to have a virtual hidden key). The Level column is an enum property of the third table.

Luke_D

Ok, that’s basically what I have so far, with several hundred skills in a dropdown where you select one skill, then a score, and then save the record. But what I want is more of a view where (for the person I’ve selected) there’s a list of all the available skills visible, with boxes beside each to input a score, and the resulting records created on the fly as I work down the list of skills with that person. Kind of like a giant multi-select input but with a secondary field on each option to input a number. Is that description more clear?
I’m just not sure how to go about that in AppSheet (some kind of table view?), or if it requires a different data structure.

Oh, and your suggestion about creating a virtual column in the third table for a concatenated key is a good one that I had overlooked. Will do that!

perissf

Ok. Something like that is achievable by changing the default view “people_skills_inline” to a deck view with some actions in it for setting the level to the different values. But you need first to create “empty” records for the “People Skills” table in order to have all of them always shown:

Luke_D

Ok, now that feels like the direction I needed someone to point me in. Thank you! Now to figure out if there’s a way to batch add a large number of blank records with an action or something. Another day, another battle ha ha.

I appreciate your help!

perissf

I’d go with Copy&Paste or a VBA script in excel for adding the records for the existing people and skill records.
For the people and skills to be added by app users, you need to create a workflow rule that adds “PeopleSkill” records whenever a new People or a new Skill is added. This is definitely the trickiest task, because you have to add a set of PeopleSkill records, not a single one.
You need to learn how to add a number of rows from this link

and maybe instead of using a column for holding the counter, you can create a condition based on the number of missing records. If you have n people and m skills, and the user adds a new people, then the workflow should add m PeopleSkill records…

Luke_D

Brilliant. Looking forward to digging in to that example app. Thank you!

perissf

Here are the actions to define in order to add records to People Skills stable when a new record to People is added. No need to use counters nor control columns nor control tables. Similarly, you’ll probably need to create actions for handling the creation of new skills.

Action 1: Add a row to P_S from People

For a record of this table: People
Do this: Data: add a new row to another table using values from this row
Table to add to: People Skills
Set these columns:
- Person: [_THISROW].[ID]
- Skill: ANY(Skills[ID]-SELECT(People Skills[Skill],[Person]=[_THISROW].[ID]))
Appearance: Do not display

Action 2: Add a row to P_S from People Recursion

For a record of this table: People
Do this: Grouped: execute a sequence of actions
Actions:
- Add a row to P_S from People
Appearance: Do not display

Action 3: Add rows to P_S from People

For a record of this table: People
Do this: execute an action on a set of rows
Referenced Table: People
Referenced Rows: LIST([_THISROW])
Referenced Action: Add a row to P_S from People Recursion
Appearance: Do not display
Only if this condition is true: COUNT(Skills[ID]-SELECT(People Skills[Skill],[Person]=[_THISROW].[ID]))>0

Add the last action to Add a row to P_S from People Recursion

Actions:
- Add a row to P_S from People
- Add rows to P_S from People

Workflow: Add rows to P_S when people add

Target data: People
Update event: ADDS_ONLY
Run these tasks:
- Workflow task name: Add rows to P_S from People task
- Task category: Change data
- Data change action name: Add rows to P_S from People

MultiTech

@perissf your method is perfect! No extra data changes necessary to be communicated back and forth between the server and apps. I LOVE IT!

So much so that I made this sample app to demonstrate it. (^_^)

I made a few changes to make things a bit more efficient, as well as migrating all the executions into actions alone (no workflows) - this way all the data changes are immediate. (^_^)

https://www.appsheet.com/samples/How-to-loop-through-a-list-and-create-child-records-for-each-item-i...

perissf

Thanks!

MultiTech

No no… thank YOU (^_^)

How Would You: Create a view to score a large number of people on a large number of skills

I made a few changes to make things a bit more efficient, as well as migrating all the executions into actions alone (no workflows) - this way all the data changes are immediate. (^_^)