Skip to main content
Question

Create a Parent from Child using fields from Child then pass New Parent Record ID to Related Parent ID under Child to link them

  • February 8, 2018
  • 0 replies
  • 90 views
Hello QB Community,
I have a Child record called SOW that i would like to use data from to generate a Parent record called Work Package then take the generated Parent Record ID and pass it into the field Related Parent ID in the SOW Table. 

Initially i was trying the below method but found i cannot pass the returned XML [RID] from the newly created Parent record. 


var text AddRecord = URLRoot() & "db/" & [_DBID_TASKS] & "?a=API_AddRecord&apptoken=xxxxxxxxxxxxxxxxxxx&_fid_48=" & [Related Project] & "&_fid_132=" & [Related Site] & "&_fid_7=" & [Description] & "&_fid_147=" & [Project - Related customer] & "&_fid_77=" & Today() & "&_fid_137=" & [Visit 1 Date] & "&_fid_251=" & [Visit 1 Time] & "&_fid_442=" & [Visit 1 Labour Qty] & "&_fid_164=Installation" & "&_fid_138=" & [Visit 2 Date] & "&_fid_252=" & [Visit 2 Time] & "&_fid_443=" & [Visit 2 Labour Qty] & "&_fid_165=Installation" & "&_fid_139=" & [Visit 3 Date] & "&_fid_253=" & [Visit 3 Time] & "&_fid_444=" & [Visit 3 Labour Qty] & "&_fid_166=Installation" & "&_fid_140=" & [Visit 4 Date] & "&_fid_254=" & [Visit 4 Time] & "&_fid_445=" & [Visit 4 Labour Qty] & "&_fid_167=Installation" & "&_fid_141=" & [Visit 5 Date] & "&_fid_255=" & [Visit 5 Time] & "&_fid_446=" & [Visit 5 Labour Qty] & "&_fid_168=Installation" & "&_fid_142=" & [Visit 6 Date] & "&_fid_256=" & [Visit 6 Time] & "&_fid_447=" & [Visit 6 Labour Qty] & "&_fid_169=Installation" & "&_fid_143=" & [Visit 7 Date] & "&_fid_257=" & [Visit 7 Time] & "&_fid_448=" & [Visit 7 Labour Qty] & "&_fid_170=Installation" & "&_fid_144=" & [Visit 8 Date] & "&_fid_258=" & [Visit 8 Time] & "&_fid_449=" & [Visit 8 Labour Qty] & "&_fid_171=Installation" & "&_fid_145=" & [Visit 9 Date] & "&_fid_259=" & [Visit 9 Time] & "&_fid_450=" & [Visit 9 Labour Qty] & "&_fid_172=Installation" & "&_fid_146=" & [Visit 10 Date] & "&_fid_260=" & [Visit 10 Time] & "&_fid_451=" & [Visit 10 Labour Qty] & "&_fid_173=Installation";

$AddRecord

I then moved to using a Script technique, using the variables for [script] & [/script] with a code page for .js.

Below i input to a URL Button (i didn't add all the fields just the key required ones as a test)

"javascript:" &  
"var F3='" & [Record ID#] & "';" &
"var F72='" & [Related Project] & "';" &
"var F77='" & [Related Site] & "';" &
"var F120='" & [Description] & "';" &
"var F84='" & [Project - Related customer] & "';" &
"var F100='" & [Date of Issue] & "';" &
"$.getScript('" & URLRoot() & "db/bm3kptpzr?act=dbpage&pagename=AddWR.js');void(0);"

Below i input to a code page named "AddWR.js":

var dbidSOW = "dbidSOW";
var dbidWR = "dbidWR"
var apptoken = "Myapptoken";

$.ajaxSetup({data: {apptoken: apptoken}});

var promise = $.get(dbidWR, {
  act: "API_AddRecord",
  _fid_48: F72,
  _fid_132: F77,
  _fid_7: F120,
  _fid_147: F84,
  _fid_77: F100
});

$.when(promise).then(function(xml) {
  var WRrid = $("rid", xml).text();
  console.log(WRrid);
});


$.get(dbidSOW, {  
  act. "API_EditRecord",
  rid: F3,
  _fid_114: WRrid
});

the code isn't running, i try to limit what i am doing but am not getting anywhere. Any help would be appreciated.
    This topic has been closed for replies.

    • anomDiebolt
    • Registered
    • February 8, 2018
    The problem is with the $.get(dbidSOW) call executing before $.get(dbidWR) returns its response.

    To use a metaphor, you can think of a promise as a bakery claim check for the purchase of some bread at the bakery counter. If you grab a claim check you will eventually get to purchase your bread.  But you can go on to do other work such as gab on the phone or discipline your child while waiting.



    When you create a promise by making an AJAX call you are arranging to receive an XML response at some time in the future when it arrives. But the code keeps executing the next statements. So you wind up launching a second AJAX request without knowing the newly created [Record ID#] (ie WRid).

    You can patch up the code like this (I added some console statements to debug):
    var dbidSOW = "dbidSOW";
    var dbidWR = "dbidWR"
    var apptoken = "Myapptoken";
    $.ajaxSetup({data: {apptoken: apptoken}});
    $.get(dbidWR, {
      act: "API_AddRecord",
      _fid_48: F72,
      _fid_132: F77,
      _fid_7: F120,
      _fid_147: F84,
      _fid_77: F100
    }).then(function(xml) {
      console.dirxml(xml);
      var WRrid = $("rid", xml).text();
      console.log(WRrid);
      $.get(dbidSOW, {  
        act. "API_EditRecord",
        rid: F3,
        _fid_114: WRrid
      }).then(function(xml) {
        console.dirxml(xml);
        //document.location.href = ...
      });
    });

    This isn't the best way to structure the code because your are nesting AJAX calls rather chaining them with then(). But there is a bug the version of jQuery that QuickBase uses so this is acceptable for now.

    Great article explaining these concepts:



    The Promise of a Burger Party
    https://kosamari.com/notes/the-promise-of-a-burger-party
    • JoshuaTate
    • Author
    • Registered
    • February 8, 2018
    Thank you Makes total sense, i will certainly check out the burger party. Unfortunately - For what ever reason the script isn't triggering - i click the URL button and wait, a few seconds later in console i get Evergage: Error decoding cookie segment: 100% but nothing more. Hopefully you can see what i am doing wrong...

    -----------
    1. To confirm, button has:
    "javascript:" &  
    "var F3='" & [Record ID#] & "';" &
    "var F72='" & [Related Project] & "';" &
    "var F77='" & [Related Site] & "';" &
    "var F120='" & [Description] & "';" &
    "var F84='" & [Project - Related customer] & "';" &
    "var F100='" & [Date of Issue] & "';" &
    "$.getScript('" & URLRoot() & "db/bm3kptpzr?act=dbpage&pagename=AddWR.js');void(0);"
    -----------

    2. The href for the button shows per below in inspect:

    javascript:var F3='2';var F72='7';var F77='3637';var F120='2';var F84='1';var F100='';$.getScript('https://rosjohnston.quickbase.com/db/bm3kptpzr?act=dbpage&pagename=AddWR.js');void(0);

    -----------

    3. AddWR.js has the below code:

    var dbidSOW = "****";  var dbidWR = "****"  var apptoken = "****";    $.ajaxSetup({data: {apptoken: apptoken}});    $.get(dbidWR, {    act: "API_AddRecord",    _fid_48: F72,    _fid_132: F77,    _fid_7: F120,    _fid_147: F84,    _fid_77: F100  }).then(function(xml) {    console.dirxml(xml);    var WRrid = $("rid", xml).text();    console.log(WRrid);    $.get(dbidSOW, {        act. "API_EditRecord",      rid: F3,      _fid_114: WRrid    }).then(function(xml) {      console.dirxml(xml);      //document.location.href = ...    }); 
    });
    • anomDiebolt
    • Registered
    • February 8, 2018
    Evergage is a library QuickBase uses that is essential for the monitoring of the product. Unfortunately it is chatty and keeps spitting info out to the console. Those messages have nothing to do with your code but they may be obscuring your own console logging.

    https://community.quickbase.com/quickbase/topics/removing-spurious-messages-from-the-console

    On other matters I am done for the day.
    • JoshuaTate
    • Author
    • Registered
    • February 8, 2018
    Ok so - I couldn't get the below code to work, for whatever reason it just froze the script:

      $.get(dbidSOW, {  
        act. "API_EditRecord",
        rid: F3,
        _fid_114: WRrid
      })
       .then(function(xml) {
        console.dirxml(xml);
       });

    With that said I have a workaround for now but by all means, it is not a long-term solution as I hate mixing code types but I have used a URL API to get the edit record complete - i wish i could work out why it won't work in Java above....

    .then(function(xml) {
      console.dirxml(xml);
      var WRrid = $("rid", xml).text();
      console.log(WRrid);
      document.location.href="https://rosjohnston.quickbase.com/db/bna3zpabw?a=API_EditRecord&rid="+F3+"&_fid_11...
    }); 

    Cheers

    Josh
    • DanLadner1
    • Registered
    • February 8, 2018
    For what it's worth, I've done similar stuff w/ webhooks. So a webhook on the child table would create the parent record w/ the needed info--include the child record's RID in a "source RID" field or something similar--then, a webhook on the parent table would pick up on the fact that the "source RID" has changed, and modify the child record based on that, linking it to the newly-created parent.
    • JoshuaTate
    • Author
    • Registered
    • February 8, 2018
    Thanks Dan, I will keep that in mind as an option (I'm just more a fan of Java/URL)
    • anomDiebolt
    • Registered
    • February 8, 2018
    There is no colon after "act" (you have a period)
     $.get(dbidSOW, {  
        act. "API_EditRecord",
        rid: F3,
        _fid_114: WRrid
      })
       .then(function(xml) {
        console.dirxml(xml);
       });
    • JoshuaTate
    • Author
    • Registered
    • February 9, 2018
    Thank you so much - sometimes it takes an extra pair of eyes to see a mistake. Do you know of any compiler type program that can pick up syntax errors like this? I currently use Notepad++ set with Javascript for formatting but doesn't have syntax (that i am aware of)
    • RickLindner
    • Registered
    • October 15, 2019
    Joshua,
    I realize this is after the fact, but I was stumbling with the same issue and think I figured out a solution that may be simpler for users (like me) who don't use java, AJAX, etc.

    Similar to you, I wanted to add a parent from a child form using a URL button, THEN update the child with the related parent ID.  In this case, we have accounts (parent) and orders (child).  Usually an account exists before an order however, we are now placing orders that establish new accounts.  The result is that we track order info before we know the account info, but want to add the account from the order form as soon as we have the required info.  I ended up using a URL button combined with an automation.  Here's what I did: 

    1. I added two fields to the parent table (Accounts):
      1. a numeric to capture the child's record ID, ex. [added from child Record ID#], and
      2. a checkbox that will trigger an automation, ex. [added from child?]
    2. then I created the URL button to add the parent record, and added the statements that update the two new fields,
      1. insert the child Record ID into the new field [added from child Record ID#], and
      2. set the checkbox [added from child?] to TRUE
    3. then I created an automation to do the heavy lifting
      1. trigger the automation when a new parent record is added, and
        1. the checkbox [added from child?] = TRUE, and
        2. [added from child Record ID#] isn't blank (prob not necessary, but I added for good measure)
      2. when the automation fires, update the record in the child table where the child Record ID# = the trigger field [added from child Record ID#] , and
      3. set the [related parent] value on the child record to the trigger value [record ID#].
    Seems to work just fine and it's quick enough for my purposes. 

    As an aside, when we place an order and start to gather the account info, we are capturing that info on the order record.  I setup text fields  for this purpose (i.e., draft Account ID).  When the account record is created and related, the URL button inserts the "draft" values into the correct parent fields, THEN goes back to the child record and removes the draft values (cleans up).  Once a parent is related, I use a form rule to hide the draft fields and the URL button. 

    Just thought I'd share. 

    Rick

    ------------------------------
    Rick Lindner
    ------------------------------
    Terms & ConditionsAccessibility statement