I have a Project Details table with the following columns:
Project ID
Data Filled (initially blank)
Requirements:
1. The Data Filled column should start as blank.
2. When the Status column in Project Details is set to "Assigned to user", the Data Filled should change to "No".
3. There are two related tables, W1 and W2, which are linked to Project ID. Both tables have columns that should be filled for the project to be considered "complete".
4. If all required columns in W1 are filled (no blank columns in any row), the virtual column in W1 should show "Yes".
5. Similarly, if all required columns in W2 are filled (no blank columns in any row), the virtual column in W2 should show "Yes".
6. When both W1 and W2 columns show "Yes", the Data Filled column in Project Details should automatically change to "Yes".
What I've Tried:
In W1 and W2, I created 2 columns in each table to check if all rows for the related Project ID are filled, using formulas to return "Yes" or "No" based on the column values.
In Project Details, I created a virtual column that captures "Yes" or "No" based on both tables.
I then created an action button that, when clicked, reflects the value of the virtual column in the Data
Filled column.
However, I want this process to be fully automated, without the need for a manual button click. Iโve tried using App sheet Automation, but I couldnโt trigger the update using the virtual column value, as automation doesn't seem to trigger changes in virtual columns.
Solution Sought:
How can I automate the update of the Data Filled column in Project Details based on the values in W1 and W2 without requiring manual intervention or button clicks?
What steps can I take to trigger the automation whenever the status of a project changes or when data in W1 or W2 is updated?
LinkedIn
Twitter
