Update Tables Automation: Invalid Input

[SharonC]

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!

[TheTimeSavingCo]

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?

[SharonC]

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!

[TheTimeSavingCo]

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?

[SharonC]

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!

[TheTimeSavingCo · Accepted solution]

It would be the "Current Item"'s record ID!

 

[SharonC]

Works like a charm!

Thanks Adam!