Need help with automating year end

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
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]&" - "&[Service]&" - "&[Year]).  On Jan 1 of every new year I would like an automation or how ever it needs to be done to copy all Opportunities with an "Active" [Status] and change the [Year] to the new current year.  Then I would want it to change the status of the ones it copied to [Status] "Closed Year".  Any advice would be appreciated.
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
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.

Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
I've never created a button, is there instructions on how to do this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
sorry,  I make assumptions ....

Just make a formula URL field and then choose a pretty color for the button.  I hate grey.  Blue is nice for this purpose here is the hex code to paste in

058df5
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
Thanks again and enjoy that vacation!!!
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
Getting this error:

Formula syntax error

A variable declaration must end with a semi-colon.


var text CreateNewOpportunities = URLRoot() & "db/" & [_DBID_OPPORTUNITIES] & "?act=API_RunImport&ID=10;

var text CloseOutOldOpportunities = URLRoot() & "  db/" & [_DBID_OPPORTUNITIES] & "?act=API_RunImport&ID=11;

var text Redisplay = URLRoot & "db/" & dbid() & "?a=dr&rid=" & [Record ID#];

$CreateNewOpportunities  
& "&rdr=" & URLEncode($CloseOutOldOpportunities)

& URLEncode("&rdr=" & URLEncode($Redisplay))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
it needed a closing quote on the first two formula variables.


var text CreateNewOpportunities = URLRoot() & "db/" & [_DBID_OPPORTUNITIES] & "?act=API_RunImport&ID=10";

var text CloseOutOldOpportunities = URLRoot() & "db/" & [_DBID_OPPORTUNITIES] & "?act=API_RunImport&ID=11";

var text Redisplay = URLRoot & "db/" & dbid() & "?a=dr&rid=" & [Record ID#];

$CreateNewOpportunities  
& "&rdr=" & URLEncode($CloseOutOldOpportunities)

& URLEncode("&rdr=" & URLEncode($Redisplay))