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?

[Aline_v]

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!

[TheTimeSavingCo]

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

[Aline_v]

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! 🤗

[TheTimeSavingCo · Accepted solution]

Thanks for the link!  You're mostly there!  I've duplicated and set up the automation here

Assuming this is your check in data:

The automation will paste the "ID" value into the linked field to "Market" when the "Market" form is submitted:

And in "Market" you'll create lookup fields to display the data:

  

[Aline_v]

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

Thanks a lot!