Need to copy value in one field to another when a status field changes all within the same record

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I have a parent table (Projects) and a child table (Ops). Ultimately, what I want to happen is when the "Status" of a record in the parent table changes from "Not Started" to "In-Progress", then the date in the field "Actual Start" in the child table is copied to "Planned Start" also in the child table - I only want this to happen one time, when the status of the record in the parent table changes from "Not Started" to "In-Progress".

I have tried using Actions and Automation, and can't seem to get either of them to do what I want. I believe the Automation is not working because it fires ""When a user..." modifies a record in the Ops table and "Project Status" changes to "In-Progress", but a user is not actually changing it - it's a lookup field getting its value from its parent record.

Any help would be greatly appreciated!
Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
Why not just trigger the Automation when the Parent Record changes, and then have the Automation edit those child records.
Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb
I tried this, but the automation will only allow me to copy values from the Projects table to the Ops table - I need to copy a value from Ops to another field also in Ops 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
Sorry but based on your original question and this subsequent clarification I'm quite confused as to what you want to do.  Can you restate it clearly?

Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb
Of course! I appreciate the help - 

I have a parent table called Projects that has a child table called Ops

-The Projects table has a field called Status, this field can either have the value "Not Started" or "In-Progress"
-The Ops table has a field called Actual Start and another field called Planned Start
-When I change the Status field in the Projects table from "Not Started" to "In-Progress", I want the value in the Actual Start field in the Ops Table to populate the Planned Start field also in the Ops Table, for each Op related to said Project.




The Automation that I am trying looks like this:

Trigger: When a user modifies a record in the Projects table <and> Status changes <and> Status is equal to the value "In-Progress"

Action: Modify records in the Ops table where Related Project ID# is equal to the value Project ID#, populate the field Planned Start in Ops > Value from another record > (I only see fields from the Projects table to select here, I need the field being copied from to be the Actual Start field from the Ops table




I tried to work around this by creating a Project Status Lookup field in the Ops table, then changed the Automation to:

Trigger: When a user modifies a record in the Ops table <and> Module Status changes <and> Module Status is equal to the value "In-Progress"

Action: Modify records in the Ops table where OP ID# is equal to the value OP ID#, populate the field Planned Start in Ops > Value from another record > Actual Start in Ops

This seems to me like it should work, but the action doesn't even run - I'm assuming this is because a user (me) isn't actually modifying any records in Ops, it's just the lookup field Module Status in Ops changing to match the "In-Progress" value in the Status field in Projects

It seems like this would be easy to do - am I missing something really simple?
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
One way to get this to work, you will need to run a saved table to table import to import the child record into themselves and merge on Record ID# so in fact no new records would be created.  The import would map the one date field into the other.

The trick however, is to limit that table to table import to just the child record of the Focus Project.

You can make a table with exactly one record in it and exactly 1 numeric field called [Record ID# of Focus Project].  It will be record ID# of 1.  Then make a relationship between that table and your children Ops records based on a field called [Link to Focus Project (=1)] and make that a formula numeric field that will calculate to 1.  So the formula is 1.  Then look up the [Record ID# of Focus Project] down to the child ops records.

Then build a saved table to table import from ops into ops and limit the import to just where [Related Project] = [Record ID# of Focus Project].

Map the [Actual Start date into the planned start date]

Then build your automation to have two steps.

1. Write the Record ID#of the project into that focus table.
2. Run the import.

Feel free to contact me directly at QuickBaseCoach.com or we can continue to muddle though this way too.







Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb
Not quite working but I feel like I'm at least making some progress now, thanks!

I have most of that working, it appears up to the part where it copies the value from [Actual Start] to [Planned Start]. The Automation triggers, the [Record ID# of Focus Project] gets set to the correct Record ID#, the import runs but doesn't actually copy any data it appears? Thanks again for your help so far!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
when You run the import manually does it have a green pop up saying that there were records copied.

Do the child records have the correct Focus arrived ID or is that blank.

Did you set the formula field to used in the relationship calculate to 1
Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb
When run manually, I do not see a pop-up - just the "Last Run Time" update

The [Record ID# of Focus Project] field has the correct ID#, and is updating correctly 

the [Link to Focus Project (=1)] has a formula in it of 1, and is showing a value of 1 for all ops

I could give you access to my app if that makes this easier
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
What is the filter on the import?

Do the child records have the correct focus Project Record ID.
Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb
Import Style:
-Merge in Ops, [Op ID#] = [Op ID#]

Import ops that meet the following criteria - Show ops where:
-all of these conditions are true
-[Related Project ID#] is equal to the value in the field [Related Project ID# of Focus Project]


Yes, the child records have the correct [Related Project ID#]

If I manually click the Import button, I get a green box pop-up that says "A total of 139 Ops were read in.139 existing Ops were left unchanged."




Photo of James Burnett

James Burnett

  • 200 Points 100 badge 2x thumb
Changed the field type from Work Date to Date - it works now :) Thank you so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 63,768 Points 50k badge 2x thumb
Great, thx for letting me know.