Get data in one table and post in other 5 tables, creating a new record or updating specific fields

[Aline_v]

Hi, community! I'm sure you'll be able to give me some ideas to figure out the best way to accomplish what I'm trying to.

I have a base with 6 tables: Check-in, Market, Business, Operations, Traction and Finance.

Forms with data will arrive in Check-in table. Since is a lot of data for the customer to fill in one time, we had to split fields in 5 forms which collect the real values of customer business and 5 forms collecting projection values.

Customer will answer this every month, so we have an Evaluation Month field in every table. Also, and that's one messed up thing, they need customer be able to send data in more than one form. So this would work something like, on first month, customer knows 4 of the 8 questions, he will send a form with these 4 answers; then, when he gets the other 4, he will send another form with the other 4 answers. Please, consider we can't ask our customer to send only one form per month, despite this was the easiest way to solve the problem.

After that, we need to get data from Check-in table and post in the other tables. I thought about doing that with automations, but there are so many possibilities to combine(5 tables, 12 months, at least 7 fields in each form) that I would have to create one automation for each month and type of form; and then so many conditions to attend so it updates only empty fields, not overwriting those already filled; and one final step in case the Find records step doesn't find any record with tha same month, so it Creates a record in the respective table. 😰

My guess is the cleanest way to achieve this is a Run script module, but I'd be very glad to hear from you.
Thanks!

[TheTimeSavingCo]

Assuming you want a single record per customer per month in the "Market", "Business" etc tables, that is to say, Customer 1 will have one record in "Market" for Jan 2024, another in Feb 2024 etc etc, perhaps you could try:

1. Create a formula field in the "Check-in" table that will output the Evaluation Month, year of submission and customer unique identifier, e.g. "Jan 2024 - customer1email@gmail.com"
2. Create an automation that will trigger whenever a new form is submitted and have an "Update Record" action that will paste the value from the formula field into the linked field to the appropriate table.  E.g. if the "Market" form was submitted, paste it into the linked field to the "Market" table

This will automatically create a record in the appropriate table and, if the user submits an update, will link to the same record in the "Market" table, so you now have a single record in "Market" that's linked to a single customer for a single month

At this point I'm not entirely sure what the business logic is as you mention worrying about overwrites.  Are you worried about overwrites to the data from a single month-form type combo?  Or is the expectation that the 'Market' table only contains a single record per customer that collates all of that customer's data across the months?