Discussions

Expand all | Collapse all

Need help with automating year end

Mark Comish08-22-2018 20:34

QuickBaseCoach Dev./Training08-23-2018 17:18

QuickBaseCoach Dev./Training08-24-2018 03:10

Mark Comish08-24-2018 18:43

  • 1.  Need help with automating year end

    Posted 08-21-2018 19:46
    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.


  • 2.  RE: Need help with automating year end

    Posted 08-21-2018 20:10
    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.



  • 3.  RE: Need help with automating year end

    Posted 08-22-2018 19:45
    I can't find any info on how to make a saved import?  Is this what I need to learn first?


  • 4.  RE: Need help with automating year end

    Posted 08-22-2018 20:26
    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.


  • 5.  RE: Need help with automating year end

    Posted 08-22-2018 20:34
    These are my choices:


  • 6.  RE: Need help with automating year end

    Posted 08-22-2018 20:41
    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.


  • 7.  RE: Need help with automating year end

    Posted 08-22-2018 21:49
    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!


  • 8.  RE: Need help with automating year end

    Posted 08-23-2018 02:28
    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.


  • 9.  RE: Need help with automating year end

    Posted 08-23-2018 17:18
    correction:

    [Current Year] with a formula value of

    Year(Today())


  • 10.  RE: Need help with automating year end

    Posted 08-23-2018 22:05
    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.  


  • 11.  RE: Need help with automating year end

    Posted 08-23-2018 22:25
    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.


  • 12.  RE: Need help with automating year end

    Posted 08-23-2018 22:59
    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?


  • 13.  RE: Need help with automating year end

    Posted 08-24-2018 03:10
    They will need to be re-typed


  • 14.  RE: Need help with automating year end

    Posted 08-24-2018 14:41
    One other small issue.  I have fields like [Jun Due] which are date fields.  On the first part of the import I need it to change the date by 1 year.  So if [Jan Due] is 01/15/2018 on the import I need to have it change 1 year in advance or 01/15/2019.  If the field is empty then it would not enter any date.  I am guessing I may have to create a "Formula date" field?  If so what would the formula be?  Thank you so much!!!


  • 15.  RE: Need help with automating year end

    Posted 08-24-2018 14:45
    If you want to make a field which is one year in the future from some date field, to use in your saved table to table import, the formula would be

    AdjustYear([Jan Due],1)

    Here is a list of all the functions with example usage.  It's a good one to "bookmark" in your favorites.

    https://login.quickbase.com/db/6ewwzuuj?a=td">https://login.quickbase.com/db/6ewwzuuj?a=td">https://login.quickbase.com/db/6ewwzuuj?a=td


  • 16.  RE: Need help with automating year end

    Posted 08-24-2018 16:43
    Thank you!  Ok I have it all working.  I am ready for your help on creating the button to make this happen,  Is it possible to only make the button viable to 1 user (me) or to a person with a certain role?


  • 17.  RE: Need help with automating year end

    Posted 08-24-2018 16:53
    OK, good timing as I'm about to leave for vacation in few hours.

    So we will need to put this button "somewhere".  If you like you can build a table called Year End Rollover and just add one record on the table and one field on the table.

    The button needs to run both imports and then refresh the record you are on.

    I suggest that you disable the need for application tokens in the Advanced Properties of the app as it will allow the button code to be simpler. 

    var text CreateNewOpportunities = URLRoot() & "db/" & [_DBID_table name of the opportunities table as found in advanced properties] & "?act=API_RunImport&ID=xx;

    var text CloseOutOldOpportunities = URLRoot() & "db/" & [_DBID_table name of the opportunities table as found in advanced properties] & "?act=API_RunImport&ID=xx;

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

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


    You will need to replace the xx with the ID of each import.  They start numbering at 10, so they are probably 10 and 11, unless you deleted any as you were testing.

    As you view a saved import, it will show the ID in the URL.

    (note that I just did an edit to my original code)





  • 18.  RE: Need help with automating year end

    Posted 08-24-2018 17:33
    I've never created a button, is there instructions on how to do this?


  • 19.  RE: Need help with automating year end

    Posted 08-24-2018 18:40
    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


  • 20.  RE: Need help with automating year end

    Posted 08-24-2018 18:43
    Thanks again and enjoy that vacation!!!


  • 21.  RE: Need help with automating year end

    Posted 08-24-2018 19:53
    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))


  • 22.  RE: Need help with automating year end

    Posted 08-24-2018 20:19
    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))