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.