Need help with automating year end
I would like a way to automate a process we will do at year's end each year. We have over 100 Opportunities. [Opportunity] field is a Formula Text field made from taking the [Company Name]&" - "&[Se...
Forum Discussion
I suggest making a button to manually push to roll over the Opportunities.
The button would run a saved table to table import to import the table into itself, with the only field being updated would be the Status. There would be a filter to only copy records where the Status was open (I'm not sure of what your statuses are).
Then the button would continue and next copy the table into itself to create new records.
You will probably want to make a Copy of your app to practise making a saved import and then get those imports working when run manually.
Then I can help you build a button which runs those two saved Imports.
In theory, those could also be done with a scheduled Automaton, but it would be difficult to test. I think that pushing a manual button once a year is not too onerous.
The button would run a saved table to table import to import the table into itself, with the only field being updated would be the Status. There would be a filter to only copy records where the Status was open (I'm not sure of what your statuses are).
Then the button would continue and next copy the table into itself to create new records.
You will probably want to make a Copy of your app to practise making a saved import and then get those imports working when run manually.
Then I can help you build a button which runs those two saved Imports.
In theory, those could also be done with a scheduled Automaton, but it would be difficult to test. I think that pushing a manual button once a year is not too onerous.
- I can't find any info on how to make a saved import? Is this what I need to learn first?
- When you go to Import / Export off the Main Home Page or from any Table Home page, you can choose Import and then there is a path to create a saved import.
- These are my choices:
- It is the third choice. A import into a table from another table.
Be sure you are first playing in a test copy of your app. - Ok, so I think I understand the saved import. Not sure how to do the following...
Make the original Opportunities become [status] of "Completed Year"
Make the new Opportunities change the [year] field to the current year
I have fields that are checked or have data that I wouldn't want moved to the new Opportunities such as a checkbox for March Invoiced since it would not be yet true in the new Opportunity
and of course how to create the button that does all these things
BTW All this is appreciated! - There will be two different saved table to table imports.
Let's talk about the one which will duplicate the qualifying Opportunities and set the year to be the current year. So you will be using the option in the setup to COPY records and initially, because the field names on your source and destinations tables match (because they are the same table!), it will suggest to copy all fields. But you will edit that "Mapping" to map nothing into fields that you want empty.
If you have a field like Year which needs to be updated to the current year, then you will need to create a field called, for example [Current Year] with a formula value of
Today()
assuming that it is a numeric field or
ToText(Today())
if it is a text field.
Similarly, you would create a field for say [Status used to roll forward Opportunities] with a formula of
"Rolled Forward"
or whatever you want to populate that field with.
Then for the other saved table to table import you will be selecting the option to Merge records. (you can start off with a COPY of the 1st saved table to table import if you like and edit it from there).
You will merge on the [Record ID#] field. so in this setup you are not creating records, but copying the table into itself and merging on Record ID#
It will map each field into itself (which is OK, but when i do this, I actually take the time to set each field to not copy, so that for the field or two that I do want to update, it will "stand out" on the field list.
So again, if there is a status field that needs to be updated, you will need to create a formula field with the value that you need in order to have a source field for that field.
This latter mass update to close out the old opportunities, could also be done via and Action, but in this case because we need to duplicate the records and need to create a button to do that, I think that it is a clearer method to do both updates using the same technique.
Once you have have these working in your test app, I can show you how to make a button to run both imports. - correction:
[Current Year] with a formula value of
Year(Today()) - So on the first import (new records import) I have it set to only match records with a [Stage] of "Won" and [Status] of Active, as these are the ones I want to roll over. I would assume the [Year] will change to 2019 if I do this import on Jan 1 2019, due to the formula field? On the second import "Merge" how can I setup the Matching to only change the status of the initial records before the copied ones (first import) were created? The only difference would be the year would be 2018 unless it will also change when the merge import is ran due to the formula? On the [Status] field it is a text - multiple choice so I am not sure I can use a formula field.
- yes, you can create a formula field for [last year] with a formula of
Year(Today()) - 1
and use that to differentiate which get closed out.
For updating the Status field, you will need to create a formula text field called perhaps [Closed Year Words] with a formula of
"Closed Year"
and map that field into the Status field. - I am getting ready to test the imports on a copied app. If they work will I have to recreate in my actual app or can they be copied?
