AddRow to table based on change in another table

I have two tables.

  • Table 1 is a pipeline (a collection of opportunities my company is working on trying to close).
    • There is a column for the status of the opportunity
    • There is a column for the deal size.
  • Table 2 is a list of all transactions and forecasted transactions that occur in the business.
    • There is a column for the actual/expected date of the transaction
    • There is an adjacent column for the month-year in which the expected date takes place (i.e if its March 13 2020, this column will Mar’20)
    • There is a column for the value of the transaction in $

What I want is that anytime the status of an opportunity in Table 1 was to change to “Closed - Won”, a new row would be created in Table 2 and will populate the fields appropriately. I know how to use the AddRow function to get the correct fields to populate, but I don’t know how to properly set this up so that it happens based on the trigger I explained and to ensure that it is in fact looking at only the Row in which the status is changing. Also, these need to remain two seperate tables (one cannot just be the view of another).

I also want to know if I can somehow “lock” the row in Table 1 from having the status column changed one it is marked as closed.

Thanks!

Dear @Courtney_Zorio1,

Please would you mind to update your doc sharing settings as follows:

@Jean_Pierre_Traets, sorry about that, fixed. https://coda.io/d/Test_dLcSImYAOAk

Instead of using an If() statement in the button action, I would use the “Disable if” field. This will disable the button from being pressed, even from an automation or a “Push all” button from the canvas.

For your formula, something like this should work for the button action…

RunActions(
  [Table 2].AddRow(
    [Table 2].Name,thisRow.Name,
    [Table 2].[Date of transaction],thisRow.[Expected Pay Date],
    [Table 2].[Amount $],thisRow.[Deal Size]
  ), 
  ModifyRows(thisRow,
    thisRow.Status,"CLOSED"
  )
)

Then for the disable field, you can use something like…

Status.Contains("CLOSED")

The final step is to setup an automation to run when a row changes. Pro and Team plans have more automations available, so if you’re on the Free plan, I would look to run this once a day, but on the Pro or Team plans you can run this when the row changes and set it to run when the status column changes. Then you can add an extra filter to only run if the status contains “won” or whatever you need.

@BenLee Thank you for the help! I’m sorry but I realized that I made a mistake and I altered my document after the post (I added the button to test the easier “manual” solution. (My first time sharing examples)

My original request was to see how to do this without a button. So the sales rep would be dragging the opportunity record along in a card view to the “Closed-won” status, and I want Table 2 to update automatically, and prevent the row in Table 1 from being modified again.

I’ve updated the document to what it was supposed to be and I won’t mess with it again, I promise :slight_smile:

So the same strategy will work if you include the functionality in the Automation. I prefer to use buttons because they provide an easier way to see what is working and what isn’t when creating a setup like this. Also, don’t think of the button as an “only human interaction” thing, it can be pushed by the automation and the button column itself can be hidden from view.

The only tough one here is that there is no way to lock the row from being edited again. You can hide it from other views or add conditional formatting to either cross out text or show a gray background, but you can’t lock a row in this way.