Insert rows based on data in other table

I’m working on a pro-bono project for a volunteer group at a local church. The group does a lot of good in the community, helping people with needed home repairs, etc.

Using RadSystems v8.1.0 Laravel/Bootstrap.

A major part of the vision for the web app is for the coordinator of volunteers to be able to ask a group of potential volunteer workers if they’ll participate in an upcoming project. So first let me describe these three tables in the database:

111 rows
vol_id INT 11 PK
last_name varchar 20
first_name varchar 20
more fields, but you get the idea

28 rows
skill_id INT 11 PK
skill varchar 50
(a 'skill' is like plumbing or carpentry or sewing)

3108 rows (111 * 28 = 3108)
id INT 11 PK
skill_id INT 11 FK skill_categories.skill_id CASCADE
vol_id INT 11 FK volunteers.vol_id CASCADE
ranking TINYINT default 0

The ranking field in the skill_assessments table gets an integer from zero (meaning unqualified or no interest in volunteering for this kind of work) up to a ‘4’ for a licensed/professional at this skill.

I’m okay doing a multiple JOIN to find who is good at a particular skill. But I need help in the case of adding a new volunteer or a new skill. In the case of adding a volunteer, you’d need to look in the skill_categories table and loop through it getting all the skill_id’s, the insert that many rows in the skill_assessments table with the skill_id, each volunteer_id, and a zero for ranking. Once that’s done I can present a FORM for the new volunteer to fill-in and submit, updating those rows.

Once that’s working I could use it as a template for the similar situation of adding a new skill in the skill_categories table.

I know this is a lot to ask but it’s really a difficult question to put into words.

Use the Page EventBefore Add to do your checks and even query other tables, that should help you achieve it.