AddRow to table based on change in another table

[Courtney_Zorio1]

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!

[Jean_Pierre_Traets]

Dear @Courtney_Zorio1,

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

[Courtney_Zorio1]

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

[BenLee]

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.

[Courtney_Zorio1]

@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

[BenLee]

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.

[Ashlyee_Hickman]

I need to do something similar. This solution is hard to follow: I’m not sure where to place the button or how to use thisrow correctly. When I follow the code pasted for my own table I get an error. When I leave out thisrow it functions but adds inputs every row from my Table 1 as one new row in my Table 2. How do I get it isolated based on rows that have met the criteria “closed” in @Courtney_Zorio1’s case.

[Pch]

Hi there @Ashlyee_Hickman and Welcome to the Community !

Yes, Copy/Paste formulas can lead to errors (it’s generally best to re-write the formula).

Would you mind sharing a sample of your doc so we might see what’s not working ? .
(it’s harder to try and solve a problem without any info on the doc, its setup
and of course, its purpose… well, the expected results, at least )

While waiting for your reply I’ve build a little example to show how the button in this case works .
It’s a simplified version of the suggested solution above and it leaves out the automation part .

You’ll see 3 buttons in this example decomposing the 2 steps action formula above .
(Well, there’s a 4th one, on the canvas but it’s just there to easily reset the example )

The first button, just add the row to the table (which is the first step in the formula), the second one modify the row in the table where the buttons are to change the status of thisRow from Pending (in this example), to Closed-Won (Step 2) and the last one do both (Step 1 + Step 2) .

[Ashlyee_Hickman]

That was super helpful @Pch! I modified it for my purpose. Now I can’t figure out how to get automation to only push the button when status= true. Currently it pushes all the buttons in the column. I am referencing the Backlog Table.

[Pch]

Hi there @Ashlyee_Hickman !

I’m glad to know my very small example helped you a little bit .

Now that I see you doc (pretty nice setup by the way ) I’ll see what I can do to help you a little further . (as I already got a semi-satisfying working solution)

I sadly must go away for an hour or so but I’ll get back at you as soon as I can !

[Pch]

Ok, so here I am .

This took me a little of time because following your doc, I had to change the way the button Sync (here, in your doc) is disable as, if I understood correctly what you’re trying to do, you want the automation to be triggered when you manually check the corresponding Status checkbox after reading the article.

Using checkboxes can sometimes complicates things but I think I found a way to make it work .

For simplicity and because I’ve got a few things to explain () , I re-used my earlier example so you might need to adapt things for your doc .

So, the raw principles behind my sample here are:

1. You read your article (meaning your article is currently not in your table Read Articles)
2. Once finished reading, you check the corresponding Status checkbox to “send it” to the Read Articles table.

Following this, what I did in my sample (below) to disable accordingly the buttons is compared the articles in Backlog and the articles already in Read Articles, meaning that the Sync buttons will only enable if an article in Backlog is not in Read Articles (and those buttons will also be disabled if Status=false (just in case )).

The Disable if formula is this one :

[Read Articles].Title.Contains(thisRow.Title) OR thisRow.Status=false

Now, trying to be as complete as possible, I found 3 ways to sent those read articles from the Backlog to Read articles .

First, the automation, as you requested but it has its flaws as it requires more attentions (well, at least checking if it did correctly its work and there might be some delay between the triggering of the automation and the results).
I also needed to add a Properties field : ModifiedOn() to make it work (See the step 2 of the automation below)

With the new Disable if formula the steps for the automations are those :

The formula in this step is :

Backlog.Filter(Status AND [Modified on]=[Modified on].Max()).

It looks for the most recent row where the Status=true.
I needed to go there, because the trigger is row based, which means that it will trigger whether you check or uncheck a checkbox. So I tried to put a “stop” on the automation there.
(It might be superfluous though but it’s been a long time since I’ve played around with automations )

The last step is logically :

You could also add a step here to notify you when the automation was able to run .

Now, because I personally don’t like relying on automations, you could also use a Push button on the canvas . Which is something I used in this post .
It’s the Button 1 in my example . Like its name says, it will just push the appropriate buttons in Backlog and it will tell you how many rows will be added

Or strictly an AddRow button (Button 2 in my example) on the canvas following this post . It’s a little bit more complicated though .

I hope this helps !

If you need clarifications, don’t hesitate ! (It’s not always easy to explain those kind of things )

PS: Happy New Year (maybe a little bit in advance )

[Ashlyee_Hickman]

You’re a rockstar! I like the way you think. For the sake of learning, I’m trying to understand how to interpret that If statement in automation:

Backlog.Filter(Status AND [Modified on]=[Modified on].Max()).

I don’t understand the [Modified on] part.

Your help also inspired another workaround: since your brilliant modification to the button’s Disable If formula disables the button if the status = false, I could just do a time-based automation so that it just does a daily sweep for buttons to push (ideally I’m reading something daily, if not I’ll have to modify for the automation allowance).

I also appreciate your idea about just adding a button to the canvas. Thank you so much for your quick and thoughtful help @Pch!

[Pch]

You’re welcome !

Ok, so for the If part of the automation :

Backlog.Filter(Status AND [Modified on]=[Modified on].Max())

In other words, it takes the table Backlog and and looks into the fields Status to search for the true ones and among those true ones, it looks, in the field [Modified on] to find the most recent one (the row where [Modified on] is at the “maximum” therefore where [Modified on]=[Modified on].Max()) .

There’s no way in a row based automation (at least with checkboxes) to really determines the actual change and it will trigger at any changes so by saying there it “needs to be true and the most recent one” to go through, it can help with the human factor error .

It has its flaws as it means you can’t check the checkboxes in Status at full speed. I just put it there kind of as a “security mesure” which is probably superfluous with the new disable if formula .

@Ashlyee_Hickman

Your help also inspired another workaround: since your brilliant modification to the button’s Disable If formula disables the button if the status = false, I could just do a time-based automation so that it just does a daily sweep for buttons to push (ideally I’m reading something daily, if not I’ll have to modify for the automation allowance).

For sure ! If you stick with automation I think it’s a better idea than the row based one !
(IMHO )

@Ashlyee_Hickman

I also appreciate your idea about just adding a button to the canvas.

I like to give different leads to follow when I can and when it’s possible

Again, you’re more than welcome !
Always glad to help when I can .