Automatization when a form is summited and update a record from other table

[furucia2023]

Hi everyone, here is my situation: I'm working with KPIs and must record the people whose projects have been trained.  I'm using forms to collect the participant's data. So I have:

• Table 1 "People trained": the table where I have the forms and the data collected about people trained is stored.
• Table 2 "projects":  which is the one where I have the project data and want to update their KPIs when a form is summited. 

So my idea is to have in the table "projects" a count of people by the different KPIs registered in the table "people trained," for example, I'm looking to have in each project record the number of women that have been trained under the framework of that project. So, it means that every time a form is submitted, each project record needs to be updated.

I already have linked table 2 with Table 1 by the projects field using the "Linked records" type, so when a person fills out the form, they can manually select the project that belongs to the activity they are participating in. So I can do that relationship: people trained by the project. 

However, right now, I'm stuck in the automatization process of linking the Table 1 projects with Table 2 because I'm using a field in Table 2 as "Linked Records" type, but doing it that way is supposed that I should add all the records created in table 1 manually. It can't be because, in the end, it will be hundreds of submissions by more than ten projects. 

I'd appreciate a lot your help from the buttom of my hearth! 

[Arthur_Tutt]

Hey @furucia2023 ! Are you able to share screenshots of your tables? Helps me to visualize your project. 

And under the projects table, what specifically do you want updated when the form is submitted? Just a count of the total number of people trained? Their names? 

The more details you can provide the better. Thanks!! 

[furucia2023]

Hey @Arthur_Tutt! 

This is Table 1, "People trained":

This is Table 2 "projects": 

So I have a relationship of one project to many people trained.

In this case, I want to update the KPIs fields with the number of women, indigenous people, and people under 18 years old who will be trained in the project table. Throughout the form, I will collect data from participants about gender, ethnic group, and groups of age. I need to create monthly performance reports by projects based on the people trained using disaggregated data, so I'm looking to make a relationship that can be automatically updated for easy counting. 

Thank you so much for your interest 🙂 

[Arthur_Tutt]

Hey @furucia2023 ! Appreciate all that extra context, have a much better idea of what you're trying to accomplish now! The good news is it's doable, the bad news is it will take a bit to get setup. But I've broken down a simple example to show you how you can do it. 

I've made an example with 3 trainees (2 female and 1 male), and on the Project Table I calculate the count of each gender. 

Table Setup Trainees:

Table Setup Projects:

 Example How To Do A Conditional Lookup

 Example how to count the number of female trainees. Note: why is formula divided by 6? Because the word 'female' has 6 letters. When filtering for male trainees divide by 4 ( yes this step is a pain in the a**, but easiest way to count the single selects from the trainee table without setting up a whole script and automation). 

Make sense?

[Kenneth_Raghuna]

@Arthur_Tutt A conditional rollup with a COUNTA() would be more efficient/intuitive than a conditional lookup.

It brings it down to one field that immediately counts the number of records that satisfy the outlined conditions, rather than having to create a second field that calculates the number of entries by looking at the string length.

[Arthur_Tutt]

@Kenneth_Raghuna  Yess! In my mind that was the solution I was trying to build. Well said

[furucia2023]

Thanks! @Kenneth_Raghuna @Arthur_Tutt actually that's not my issue, it is instead the previous step to count the data by categories in each field (to count the data by categories I have already applied a rollup field).
My problem is that at this point I have to select each person manually in the field that makes possible the join and therefore the import of data to the projects table from the "people trained" table.
I share with you a video for more details in this link: https://drive.google.com/file/d/1Ge7EaMsf1OxfcsuisLAX-7YTqOyVnfEn/view?usp=sharing .

[Arthur_Tutt]

Hi @furucia2023 I'm confused, I thought said when the trainees fill out the form, they list the project they're working on. Once they make that connection it is take care of. Then why do you have to manually do it? 

[Kenneth_Raghuna]

I'm also confused, but if my intuition is serving me correctly, then you likely just need to add an automation that adds the appropriate Project linked record every time a record is created in your People table.

[furucia2023]

I'm sorry to make you confused. I need to create an automatization, as @Kenneth_Raghuna says, and that is where I need help because I don't know how to make it. 😅