Forum Discussion

WilliamEvans1's avatar
WilliamEvans1
Qrew Cadet
8 years ago

Javascript to Copy a Record n number of times and increment a numeric field?

Hi,

We have an "Items" table in Quickbase where each item has a "Month Number (1 to 12)" assigned (we don't use the "date" field, we use "month numbers").

I want the ability to add in an identical item, to the same table, up to 12 times (Month's 1 to 12) - for an item that occurs monthly.

Currently we do this by creating the first item, saving it, and then going to the menu "More>Copy This Item", changing the month number, rinse and repeat 11 times. 
(We can do it slightly quicker through Grid Edit but still slow and laborious when you have hundreds of items to enter and change the month number each time).

Ideal Solution:

Ideally, i'd like a javascript button that I can put on the item form 
  1. Copy's the original item n number of times (i.e. javascript prompt that asks for the number - e.g. 5, and then it copies 5 times) 

  2. Increments the "month number" by +1 for each copy (our month's go 1 to 12 so incrementing works).

I'd be happy with just 1. if incrementing is too difficult.... and if that's too difficult then i'd live with just a button that copied a fixed number (11) times.

NB: There's no parent/child relationship at this level.

Any pointers? I can find plenty of references to copying an item, but not copying it more than once...

Thanks!
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    That there is a low-tech solution available here that does not require JavaScript. Can you ask answer this question? It's a situation that if you are copying a record that is in month number 3, that the button needs to finish off the rest of the months up until 12? Is the goal to always increment to get to 12?
    • WilliamEvans1's avatar
      WilliamEvans1
      Qrew Cadet
      8 years ago
      button needs to finish off the rest of the months up until 12? Is the goal to always increment to get to 12? --> _Correct, although some flexibility would be good i think 99% of the time people will be entering items that are monthly till the end of the year so best building just for that case.... 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    I have not done a full test of working code but here is a non javascript approach.

    You can do successive URL calls like this, where each of the formula variables is a URL formula, such as API_AddRecord.

    Here is an example of a formula I used in an app once.
    var text YYOtherMonths = Right(ToText(Year(Today())),2);


    var text AddJAN = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "01-01-" & $YYOtherMonths;
    var text AddFEB = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "02-01-" & $YYOtherMonths;
    var text AddMAR = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "03-01-" & $YYOtherMonths;
    var text AddAPR= URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "04-01-" & $YYOtherMonths;
    var text AddMAY = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "05-01-" & $YYOtherMonths;
    var text AddJUN = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "06-01-" & $YYOtherMonths;
    var text AddJUL = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "07-01-" & $YYOtherMonths;
    var text AddAUG = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "08-01-" & $YYOtherMonths;
    var text AddSEP = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "09-01-" & $YYOtherMonths;
    var text AddOCT = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "10-01-" & $YYOtherMonths;
    var text AddNOV = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "11-01-" & $YYOtherMonths;
    var text AddDEC = URLRoot() & "db/" & [_DBID_TABLE_1] & "?act=API_AddRecord&_fid_189=" & [Project ID#]& "&_fid_197=" & "12-01-" & $YYOtherMonths;

    var text DisplayProjectRecordinEditMode = URLRoot() & "db/" & [_dbid_projects] & "?a=er&dfid=19&rid=" & [Project ID#];



    $AddJAN 
    & "&rdr=" & URLEncode($AddFEB)
    & URLEncode("&rdr=" & URLEncode($AddMAR))
    & URLEncode(URLEncode("&rdr=" & URLEncode($AddAPR)))
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddMAY))))
    & URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddJUN)))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddJUL))))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddAUG)))))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddSEP))))))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddOCT)))))))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddNOV))))))))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddDEC)))))))))))
    & URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($DisplayProjectRecordinEditMode))))))))))))


    So one approach is to just use a version of the the button above to make 11 duplicates, each different by the month.  That would work when you have the record made for month 1.

    Then you could also make 11 versions of the buttons and use form rules to show / hide the appropriate buttons. For example if the month was "3", then you could show the button which would make months 4,5,6,7,8,9,10 11 and 12.
    • WilliamEvans1's avatar
      WilliamEvans1
      Qrew Cadet
      8 years ago
      Interesting idea thanks! i'll try it out and post back in a day :)
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      The only risk factor that I know about this is that with the successive URLEncoding, the resultant URL gets very long.  Chrome and FF allow very long URLs, but I believe that IE is more limited to about 2,000 characters.

      So once you get it working in say Chrome, then try the other browsers.  You ca also use a formula field to determine the length of the formula, and google to see the IE limit.
      https://www.google.ca/search?q=internet+exporer+maximum+url+length&oq=internet+exporer+maximum+u...\

      It seems be a limit of 2,083 characters.  So you may hit that limit if your individual URLs are very long.  If that happens and you need it to work in IE and you want to stay low tech non javascript, then you would need to break it up into two buttons.

      Mark
    • WilliamEvans1's avatar
      WilliamEvans1
      Qrew Cadet
      8 years ago
      All of my users use the latest Chrome so I think i'll be ok with the URL limit....

      I am trying to get the code working with just ONE copy + Increment action first (this is my first time coding a url formula, but i'm trying to learn)

      So with using the API_AddRecords - to duplicate my record will i need to map each and every source field to the target field - which since i'm creating a duplicate is just each field mapped upon itself? (thus hardcoding it in this formula - bit of a pain if the items table has new fields added at some point?) .... so the url becomes really really long because i have ~60 editable fields to copy thus your concern about the length?

      - or should i instead be using "GenCopyRecord" to copy the item, and then just edit the "Month Number" field ? or is it too hard to lock onto the correct field that way? 

      Am I on the wrong track (i'm still reading about the API/URL formulas)

      Thanks!
  • RyanSolutions's avatar
    RyanSolutions
    Qrew Member
    8 years ago
    Unfortunately, to get around the timeout error JavaScript becomes necessary. I've put together a quick snippet of how the script may look using xhttp. Add a code page titled "MyScript.js" and copy your JavaScript into there. This code was quickly put together and has not been tested. You have been warned :-) 

    Its important you pay attention to the variables you are passing from Quick Base into the script itself. Quick Base variables are passed through the button (see below). The script receives a few variables (including the start month and part of the add record API call). Some useful resources on JavaScript can be found at https://www.w3schools.com.




    // QuickBase Button

    var text dbid = "Table ID goes here"; 
    var text apptoken = "App token goes here";

    var text addItem = ToText(URLRoot() & "db/" & $dbid & "?a=API_AddRecord&apptoken=" & $apptoken); // **NOTE: This is an incomplete statement.
    var text itemMonth = ToText([Item Month]);

    "<a class='Tall Vibrant Snowy' 
    href=\"javascript:var addItem='" & $addItem & "';var itemMonth='" & $itemMonth & "';$.getScript(gReqAppDBID + '?a=dbpage&pagename=MyScript.js');void(0);\">Recurring TTI</a>"






    // Sample JavaScript

    var numMonths = prompt("How many items do you want to add?", 1);
    var monthStart = parseInt(itemMonth, 10);
    var nextMonth = monthStart + 1;
    var xhttp;

    try{

    if (window.XMLHttpRequest) {
       xhttp = new XMLHttpRequest();
    } 
    else {
       // code for IE6, IE5
       xhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }

    if(numMonths > 0 && numMonths < 13){
    for(var i = 0; i <= numMonths && $nextMonth < 13; i++){
    myAPI = addItem + "&_fid_1=" + nextMonth;

    xhttp.open("GET", myAPI, false);
    xhttp.send();

    nextMonth += 1;
    }
    }

    location.reload();
    }
    catch(e){
    console.log("Unexpected Error: " + e);
    }
    • WilliamEvans1's avatar
      WilliamEvans1
      Qrew Cadet
      7 years ago
      It's a year later, but thanks so much for this, I managed to get it to work

      Here's my modifications to your code in case this helps someone one day

      CODE PAGE: DuplicateItemMonthly.js

      var numMonths = prompt("For how many months do you want to duplicate this item?", 1);
      var monthStart = parseInt(itemMonth, 10);
      var nextMonth = monthStart + 1;
      var xhttp;
      try{
      if (window.XMLHttpRequest) {
         xhttp = new XMLHttpRequest();
      } 
      else {
         // code for IE6, IE5
         xhttp = new ActiveXObject("Microsoft.XMLHTTP");
      }
      if(numMonths > 0 && numMonths < 13){
      for(var i = 0; i <= numMonths && nextMonth < 13; i++){
      myAPI = addItem + commonfields + "&_fid_43=" + nextMonth;
      xhttp.open("GET", myAPI, false);
      xhttp.send();
      nextMonth += 1;
      }
      }
      location.reload();
      }
      catch(e){
      console.log("Unexpected Error: " + e);
      }

      BUTTON CODE: 

      var text commonfields = ToText( 
      "&_fid_8=" & [Budget Status Flag]
      & "&_fid_22=" & URLEncode([Comments])
      & "&_fid_25=" & [Account Code]
      & "&_fid_28=" & [Related Company]
      & "&_fid_244=" & [Item Status]
      & "&_fid_72=" & [General Cost Currency]
      & "&_fid_73=" & URLEncode([General Cost Description])
      & "&_fid_122=" & [People Cost Number of Days Required]
      & "&_fid_156=" & URLEncode([Item Vendor])
      & "&_fid_160=" & [Spend Country]
      & "&_fid_9=" & [Recoverable?]
      & "&_fid_165=" & [General Cost Quantity]
      & "&_fid_164=" & [Predefined Daily Cost Quantity]
      & "&_fid_163=" & [Predefined Cost Quantity Completed]
      & "&_fid_114=" & [Related People Cost]
      & "&_fid_81=" & [Predefined Daily Cost Days]
      & "&_fid_71=" & [General Unit Cost]
      & "&_fid_69=" & [Related Predefined Daily Cost]
      & "&_fid_68=" & [Related Predefined Cost]
      & "&_fid_37=" & [Related Fx]
      & "&_fid_32=" & [Item Type]
      & "&_fid_47=" & [Related Sub-Project]
      & "&_fid_241=" & URLEncode([Original Item Project Category])
      & "&_fid_305=" & [Item has been Deferred]
      & "&_fid_302=" & [Select Month For Split]
      & "&_fid_299=" & [People Cost # Days to DEFER]
      & "&_fid_296=" & [Predefined Daily Cost Qty to DEFER]
      & "&_fid_295=" & [Predefined Daily Cost # of Days to DEFER]
      & "&_fid_294=" & [Predefined Daily Cost Days / Qty]
      & "&_fid_292=" & [Predefined Cost Quantity to DEFER]
      & "&_fid_281=" & [General New Split Qty to DEFER]
      & "&_fid_280=" & [General New Split Unit Cost to DEFER]
      & "&_fid_279=" & [General Split Cost / Qty?]
      & "&_fid_259=" & [Item Completion Status]);
      var text apptoken = "MY APP ID";
      var text addItem = ToText(URLRoot() & "db/" & "MY TABLE ID" & "?a=API_AddRecord&apptoken=" & $apptoken);
      var text itemMonth = ToText([Month Number]);
      "javascript:var addItem='" & $addItem & "';var itemMonth='" & $itemMonth & "';var commonfields='" & $commonfields & "';$.getScript(gReqAppDBID + '?a=dbpage&pagename=DuplicateItemMonthly.js');void(0);"
  • WilliamEvans1's avatar
    WilliamEvans1
    Qrew Cadet
    7 years ago
    I have one more quick question :) - 

    With the Javascript code below, If i set "How many months to copy to?" = 12... then  it only works when Month = 1 (January) as it stops when it hits december (12) ...

    Can you please let me know how to modify it so that if the initial month is another number (like 7 for July) and the Prompt is 12, then it should create months for 8, 9, 10, 11, 12, 1 , 2, 3, 4 , 5, 6 ... i.e. when it gets past 12 it starts at 1 again until the starting month value?

    ==============================================

    // This is used by the button that duplicates an item many times
    // Prompt for number of times to copy
    var numMonths = prompt(" How many months to copy? enter 12 for whole year", 12);
    //this is the starting month from Quickbase - i.e. 7 for July
    var monthStart = parseInt(itemMonth, 10);
    var nextMonth = monthStart + 1;
    var xhttp;
    try{
    if (window.XMLHttpRequest) {
       xhttp = new XMLHttpRequest();
    } 
    else {
       // code for IE6, IE5
       xhttp = new ActiveXObject("Microsoft.XMLHTTP");
    }
    if(numMonths > 0 && numMonths < 13){
    for(var i = 0; i <= numMonths - 1 && nextMonth < 13; i++){
    myAPI = addItem + commonfields + "&_fid_43=" + nextMonth;
    xhttp.open("GET", myAPI, false);
    xhttp.send();
    nextMonth += 1;
    }
    }
    location.reload();
    }
    catch(e){
    console.log("Unexpected Error: " + e);
    }


    EDIT: I posted this as a seperate question, which is better worded, so it get's seen: https://community.quickbase.com/quickbase/topics/javascript-code-for-loop-calculation-help?rfm=1&.