Discussions

Expand all | Collapse all

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

QuickBaseCoach Dev./Training12-27-2018 18:15

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

    Posted 12-20-2018 17:55
    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!


  • 2.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 17:58
    Why not just trigger the Automation when the Parent Record changes, and then have the Automation edit those child records.


  • 3.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 18:25
    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 


  • 4.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 19:34
    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?



  • 5.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 21:18
    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?


  • 6.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 22:14
    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.









  • 7.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 23:21
    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!


  • 8.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 23:26
    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


  • 9.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-20-2018 23:35
    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


  • 10.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-21-2018 00:17
    What is the filter on the import?

    Do the child records have the correct focus Project Record ID.


  • 11.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-27-2018 17:27
    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."






  • 12.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-27-2018 17:43
    Changed the field type from Work Date to Date - it works now :) Thank you so much!


  • 13.  RE: Need to copy value in one field to another when a status field changes all within the same record

    Posted 12-27-2018 18:15
    Great, thx for letting me know.