Hello, I am trying to create an app to control trainings in my company.
This will be the structure.
Employee Table: This table will contain the following columns: Employee ID, Employee Name, Employee Function…
Training Table: Training ID, Training Name, Training Description and some other columns.
Employee-Training Table: Employee ID, Training ID, Attendance Status, and Evaluation of the training. This will be a many-to-many relationship between the Employee table and the Training table, as an employee can attend multiple trainings, and a training can have multiple attendees.
Evaluation Table: Employee ID, Training ID, Evaluation
I however don´t know how to include data in the many-to-many table from inside the Trainings Table.
I will have this training for laying bricks (example). I want to select to it all my company employees who are brick layers.
When I select them, they should populate the many-to-many table, all of the records with the Brick Laying training referenced.
Hello, that would be easy to accomplish with the ActionButton (custom widget). There is some help about that in this forum.
See this post, for example:
While this ALMOST go as far as I need, and it’s a very similar example, the number of records that can be added is always the same: Total Number of Students - Number of Students already selected.
So for an exam with 0 students in the Exam Grades Table, the buttom will read 5. If I manually add two students, the button will read three.
Ideally, you would be able to select the students on the right table and the number on the action button would be that (select 3 students on the rightside Students table, the action button reads “add 3 records”, and it adds those 3 selected students.
That sounds like an interesting function, but I think it is not posible yet.
Another solution, would be to filter the employees by funtion, but you have to change the Structure a litlle and ActionButton code for that. If you keep reading the posts after the one I sent you, there will be a solution for that and the code. (it just got updated)
Actually, I was able to do just that. Just added a SELECTED column to the Students table… and changed the Action Button Code
actions = []
existing_grades_students = set(Exam_Grades.lookupRecords(Exam=$id).Student)
for s in Students.all:
if s not in existing_grades_students and s.selected:
actions.append(["AddRecord", "Exam_Grades", None, {
"Exam": $id,
"Student": s.id,
"Grade": None
}])
return {
"button": "Add {} records".format(len(actions)),
"description": 'Add "{}" records for {} students'.format($Exam, len(actions)),
"actions": actions
}
The only part I am still missing for this to work like I want, is to CLEAR the select buttons after I click to add the students (employees in my app)
After all, I want to add two bricklayers out of 10. I select those two and add.
Then I select another Exam (Training). Like Excel. I want the secretary to have it. I filted the employees by their jobs and select the 2 secretaries. HOWEVER, the two bricklayer dudes were never deselected So they end up included in the Excel training.
I tried adding this line to the StudentsExams Action Button example. But it didn´t work. How does Grist controls updates to records?
for s in Students.all:
s.update(selected=False)
Here is a new code… also is not working (in fact, the button stopped working)
actions = []
existing_grades_students = set(Exam_Grades.lookupRecords(Exam=$id).Student)
count = 0
for s in Students.all:
if s not in existing_grades_students and s.selected:
actions.append(["AddRecord", "Exam_Grades", None, {
"Exam": $id,
"Student": s.id,
"Grade": None
}])
actions.append(["UpdateRecord", s, {"selected":False}])
count +=1
if actions:
return {
"button": "Add {} records".format(count),
"description": 'Add "{}" records for {} students'.format($Exam, count),
"actions": actions
}
else:
return {"button":"No records to add"}
Yeah, my whole problem is in how to update the existing records to unselect them. UpdateRecord, Update… nothing works and more than that, they make the whole button not work.
actions = []
existing_grades_students = set(Exam_Grades.lookupRecords(Exam=$id).Student)
count = 0
for s in Students.all:
if s not in existing_grades_students and s.selected:
actions.append(["AddRecord", "Exam_Grades", None, {
"Exam": $id,
"Student": s.id,
"Grade": None
}])
actions.append(["update", s, {"selected":False}])
count +=1
if actions:
return {
"button": "Add {} records".format(count),
"description": 'Add "{}" records for {} students'.format($Exam, count),
"actions": actions
}
else:
return {"button":"No records to add"}
If your document is already on docs.getgrist.com and doesn’t contain sensitive data, you may add Public Access (by clicking on the share button); otherwise, you could duplicate it (share button also), checking the option to include structure without data, then share the link to the copy.
Would this version work for you? Here is the updated formula:
# This is what creates the ActionButton logic.
# It's OK to hide this column in this view.
actions = []
existing_grades_students = set(Exam_Grades.lookupRecords(Exam=$id).Student)
count = 0
for s in Students.all:
if s not in existing_grades_students and s.selected:
actions.append(["AddRecord", "Exam_Grades", None, {
"Exam": $id,
"Student": s.id,
"Grade": None
}])
actions.append(["UpdateRecord", "Students", s.id, {"selected":False}])
count += 1
if actions:
return {
"button": "Add {} records".format(count),
"description": 'Add "{}" records for {} students'.format($Exam, count),
"actions": actions
}
else:
return {
"button": "No records to add",
"description": "No records to add",
"actions": actions
}
If you don’t mind, there is something that would make this better.
Notice that I had to create a SELECT column, and I must select the students I want to add, and then the code deselects all of them.
But let’s say I have 100 students that I need to select. I will have to manually select each of them.
Apparently, I can´t change the status of all checkboxes of the selected rows, automatically.
Is there a way for grist to check the selected rows by mouse clicking at the rows themselves (which allows CTRL and SHIFT click) instead of rows where I clicked the checkbox?
I must confess something. I suck at programming. It was Chat GPT that made that code for me. But as you guys can see, I still needed a real programmer to completely fix the code.
