Update Tables Automation: Invalid Input

Hey

I am stuck with the automation of "update records", and this is where I am:

1. I have a table named "master project" (primary field) and in each row, consists of 7 sub-projects under the same master project. All 7 projects shared the same status on 4 variables, while each also have different status on 5 variables

2. I need to break each of the 7 sub-projects into individual row so that I can run calculation based on each row. For this purpose, I have created another table with "Projects" being my primary field. And to continue build on new projects when i have more master projects input from table 1 (above point), I have use "create a record" automation to pipe in full information of each project

3. My challenge is, I need to create an automation that, if any update is detected on (1), it should also automate my second sheet (2). But now because I have broken down 1 master project / record in table 1 into 7 rows in table 2, seems like the software is having difficulties in connecting (1) and (2), especially the way I update table 2 is through automation (but necessary because "lookup" does not work since the non-common variables are in different field for each project). Hence, whenever I choose "record ID" as the field to "record ID" in update automation, it is showing "invalid input"; although i have already created a field on table 2 to include "record ID" displayed in table 1.

What are the solutions that could help updating table 2? Any other connectors that I should use?

Thanks a million!

Page 1 / 1

Hmm, could you include screenshots of your tables and your automation setup? Could you also indicate on the screenshots the data from Table 1 and where it would go on Table 2?

Hi Adam, thanks for responding!

1. Table 1: 2 items are common across all (in yellow), in blues are sub-project specific info (only screenshot 1 of the 7 projects).

2. Convert each project into rows; automation is triggered based on number of markets under each project IF the project name is not empty (because each master project can have 1-7 sub-projects; sometimes more and sometimes less). So, says, project 2 exists and have 5 countries are chosen under "ongoing", the automation will create 5 projects for project 2, each represent 1 country; likewise for all 7 projects & markets.

Is this clear?

Thanks again!

Hi Adam, thanks for responding!

1. Table 1: 2 items are common across all (in yellow), in blues are sub-project specific info (only screenshot 1 of the 7 projects).

Is this clear?

Thanks again!

Ah, yes, thanks for the details!

Hmm...I think if I were you I would have a new "Project Name" field in Table 2, and with your example of the second project, the value of "2-Project Name" value would go into that field via the initial automation

Then, when an update is detected on Table 1, I would have an automation that would trigger and look for records in Table 2 that:
1. Have the same "Project Name" value
2. Are linked to the triggering record

I would then use a Repeating Group and use the results of the Find Record action as the list, and then have an Update Record that does the updates required, does that make sense?

Thanks Adam!

When creating the update record automation, what should be the record ID that i should be using?

I have tried "Record ID", have also create unique project ID (project 1-7 will now have diff record ID), but none of these works but keep returning "invalid input". What is the logic in filling in the field of "record ID"? Thanks!