Expand all | Collapse all

URL formula to edit every record in a table

  • 1.  URL formula to edit every record in a table

    Posted 05-22-2017 15:25
    I need to send a project update for each project weekly to external email contacts.  I was looking for a way to manually edit all of the records to trigger the notifications.  I've gotten a URL formula to trigger a notification, but it only works with one record at a time.  Any tips on getting a url formula to edit all records in sequence?  Script? 

    I think Juiced Tech is able provide this ability, but working with an external vendor may not be possible right now.

  • 2.  RE: URL formula to edit every record in a table

    Posted 05-22-2017 15:47
    I only know enough script to be dangerous to myself.  I got this from Keira Bromberg who is also a QuickBase Solution Provider.

    First make a dashboard button or a URL Formula text field with html enabled.

    Then you make a code page called UpdateLinks (well it can be any name with no spaces ending in .html)

    In my experience once you get it working it will update records one by one, thus triggering emails, at a rate of about 3 per second.  So if there were 100 records to update that would take 33 seconds before the "Done" pop up comes up.

    You could set the Notifications to trigger based on a date/time field being updated to whatever is in the formula date time field of now().  ie copy that now() field into a field via the script and trigger the emails off that target date/time field being changed.

    Triple check the field ID's being updated  and the qid of of the report that has the records to be updated so your script does not run amok!

    Dashboard Button
    <a href="javascript:void(window.open('bd5p83fpk?a=dbpage&pagename=UpdateLinks.html&qid=189','qdbScript','top=240,left=320,width=400,height=150,location=no,menubar=no,toolbar=no'))">Update Freight Audit Links to Freight Lanes</a>

    page name should have no spaces

    bd5p83fpk is the table with the report
    qid 189 is the report with the records to be updated

    The Code page is here called UpdateLinks.html

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><HTML>
    <link rel="stylesheet" type="text/css" href="/css/style.css"/><TITLE>QuickBase Script Engine</TITLE>
    // There references call in the script engine, ie 9kaw8phg
    <script type="text/javascript" src="/db/9kaw8phg?a=dbpage&pagename=QuickBaseClient.js"></script>
    <script type="text/javascript" src="/db/9kaw8phg?a=dbpage&pagename=qdbScript.js"></script>
    <script lang="javascript">function perPage(){

    //QDB Script Wizard inserts JavaScript below here!
    // This is the guts of the script.  Here we are identifying the record ID field as being fid 3 (its always fid 3)
    // and then copying the value of fid 748 into fid 742 and then the fid 310 into fid 749
    //  The table being updated is bd5p83fpk
    //QDB Script Wizard inserts JavaScript above here!
    <BODY style=margin:25 onload="qdbScript();" class=stdBody>
    <!--QDB Script Wizard inserts HTML below here!-->
    <!�Insert the message to the user below here!-->
    <b>Updating xxxxxx records </b>
    <!--QDB Script Wizard inserts HTML above here!-->
    <DIV id=beforeScript>
    <!--QDB Script Wizard inserts before script HTML below here!-->
    <!--QDB Script Wizard inserts before script HTML above here!-->
    <DIV id=duringScript style=display:none>
    <!--QDB Script Wizard inserts during script HTML below here!-->
    <!�Another message to the user goes here-->
    <b>Now updating xxxxxxxx records ...</b>
    <!--QDB Script Wizard inserts during script HTML above here!-->
    <DIV id=afterScript style=display:none>
    <!--QDB Script Wizard inserts after script HTML below here!-->
    <input type=button value="Done! � All Notifications have been sent.  You may now close this box" onclick=window.close()>
    <!--QDB Script Wizard inserts after script HTML above here!-->
    <DIV id="errorDiv" style=display:none></DIV>

  • 3.  RE: URL formula to edit every record in a table

    Posted 05-22-2017 17:52
    Thanks so much for providing this Mark!  I am triggering the notifications now, but unfortunately the notifications include the field ids instead of the actual field content.  For example, the notification email says:
    If you have questions, please contact the Project Manager, [107].
    Field 107 is the field that contains the PM's name.  

    If it makes any difference, I had to remove the html to get the formula URL to work (although I think that would only affect the button itself, not the notification)

    Any thoughts?

  • 4.  RE: URL formula to edit every record in a table

    Posted 05-22-2017 18:05
    That sounds like a problem with the Notification setup and not the script.  

    Does your Notification work if you just trigger it by a single manual edit?  Does your Notification body include the field name?  

    Also, is the type of Notification set to Open and not Recipient?  It if is Type Recipient up at the top, you will ned to manually rebuild it (not copy it) and create one that is type Open.

  • 5.  RE: URL formula to edit every record in a table

    Posted 05-22-2017 18:39
    It's set to open and the notification looks great when I manually trigger.

    I'm realizing that even though the script runs and tells me it is "Done!", it isn't actually editing records.  I think I may just have been triggering the notifications when I was adding fields.

    In your example above, after triggering the script should field 742 contain the value in 748
    and likewise, should 749 contain the value in field 310.  Should field 742=field 749?

  • 6.  RE: URL formula to edit every record in a table

    Posted 05-22-2017 20:43
    I've had similar experiences with the 'mass update' sending odd or incorrect notification.  Even if you fill out the "multiple" records option.

    Sadly, until QB fixes the email bug, you will either need to click each individual record, or have a script that queries the list of Records and makes the changes.