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!