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

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?

Hi, Adam! Thank you very much for your reply. I'm already trying to implement what you suggest, but I'm not sure if I followed you on that. Since the business logic was updated today, maybe the solution you gave me also needs an update.

We are tracking customer data to calculate KPIs. We have 5 Dimensions (Market, Business Model, Operations, Traction and Finance); in each dimension we have 5 stages (1,2,3,4,5) each one represented by a KPI. When customer achieves the target value for the KPI, we consider he is on that stage in that specific dimension.

Customer will send these informations every week and ideally, they will be able to send partial information. So if the Market form has 7 fields to fill and the customer only knows 5 of those answeres by the time he is sending, this should be ok. Later on that same week, he can send another Market form replying only the 2 fields he didn't know before. Data sent by customer is used to calculate the KPIs (formula fields in Check-in table). Then the KPIs values are sent to the respective table, e.g Market.

We will also have the customer sending through form the target for these KPIs and these values should also be taken to the respective table. In each table we have a field called Real/Target, which is a single select that is being filled by the automation. The idea here is to use this field to group data for the charts in Interface so it will display real and projected evolution of the dimension.

Hopefully I could describe more clearly the business logic. Thanks once again!

Hmm, sounds like you want one record per customer-week combo, so should be fine once you swap the logic from months to weeks I reckon

If you could provide a read-only invite link to a duplicated copy of your base with some example data I could set it up for you.  If you could also include an example of two form submissions and how you'd like the end result to look that would be extremely helpful

That's perfect! The copy base is in here
For this purpose I kept a partial base 'cause I understand whatever we apply in one form and table, I'll be able to replicate for the others. Also we have fields and questions in portuguese, but I believe this won't be a problem. 

Ideally this result would be in a chart that unfortunately we don't have in Airtable, so I'm still not sure which one would be better. We have to be able to follow customer going from one stage to another e he will achieve once his KPI results are equal or higher than KPI targets that he will also send to us through a form. 

Thanks once again for all your help on that! 🤗

That's great, Adam! I believe this will work.

Thanks a lot!