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

  • 1
  • 1
  • Question
  • Updated 6 months ago
  • Answered
  • (Edited)
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!
Photo of William

William

  • 226 Points 100 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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?
Photo of William

William

  • 226 Points 100 badge 2x thumb
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.... 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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
Photo of William

William

  • 226 Points 100 badge 2x thumb
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!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Yes, if you have 60 fields to copy they need to need to be mapped individually, and yes if you add a field in the future it will not get mapped.

You can make a button to do that GenCopyRecord to save clicking to the more menu and do them one by one.

It's sort of a "choose your poison" type choice.



Photo of William

William

  • 226 Points 100 badge 2x thumb
Hi Again,

I was able to implement the record copy and increment using API_AddRecord successfully for ONE month (one iteration) ... BUT as soon as i put in all the iterations, the URL became too long for QuickBase's html server :( 

I have the error

Bad Request - Request Too Long
HTTP Error 400. The size of the request headers is too long.



Here's my formula below (with api key redacted) - is there anyway to simplify this so that it will fit?




START CODE
==========================================
var text AddAug = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "8"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddSep = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "9"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddOct = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "10"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddNov = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "11"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddDec = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "12"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddJan = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "1"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddFeb = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "2"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddMar = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "3"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddApr = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "4"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddMay = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "5"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
var text AddJun = URLRoot() & "db/" & [_DBID_NEWITEMS] & "?act=API_AddRecord&_fid_8=" & [Budget Status Flag]
& "&_fid_22=" & [Comments]
& "&_fid_25=" & [Account Code]
& "&_fid_28=" & [Related Company]
& "&_fid_244=" & [Item Status]
& "&_fid_72=" & [General Cost Currency]
& "&_fid_73=" & [General Cost Description]
& "&_fid_122=" & [Number of Days Required]
& "&_fid_156=" & [Item Vendor]
& "&_fid_160=" & [Spend Country]
& "&_fid_161=" & [Related Project Manager]
& "&_fid_9=" & [Recoverable?]
& "&_fid_165=" & [General Cost Quantity]
& "&_fid_164=" & [Predefined Daily Cost Quantity]
& "&_fid_163=" & [Predefined Cost Quantity]
& "&_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=" & [Original Item Project Category]
& "&_fid_43=" & "6"
& "&apptoken=thisismyapptoken_doineedtorepeatiteachtime??";
$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($AddAUG))))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddSEP)))))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddOCT))))))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddNOV)))))))))
& URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($AddDEC))))))))))
===============================================
END CODE (These Forums really need a "Code" snippet button)



Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
I suggest that you make a button which when clicked will add the next record and display the newly created record.  The formula can "calculate" the month number for the next month.

Suffix the API string with &disprec=1 and it will land you on the record.

So then workflow will be to start with any month and then click and it will make the next month and land you on the newly created record, and then click again until you get to 12.
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);
}
Photo of William

William

  • 226 Points 100 badge 2x thumb
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);"
(Edited)
Photo of William

William

  • 226 Points 100 badge 2x thumb
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&...
(Edited)