Forum Discussion

JoshuaTate's avatar
JoshuaTate
Qrew Cadet
8 years ago

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

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.
APIs and custom code
App builders
Formulas and functions
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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's avatar
    JoshuaTate
    Qrew Cadet
    8 years ago
    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 = ...    }); 
    });
    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      8 years ago
      lol thanks - Have a good rest, there's always tomorrow or some competition to helping me solve the case :D
  • JoshuaTate's avatar
    JoshuaTate
    Qrew Cadet
    8 years ago
    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's avatar
    DanLadner1
    Qrew Trainee
    8 years ago
    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's avatar
    JoshuaTate
    Qrew Cadet
    8 years ago
    Thanks Dan, I will keep that in mind as an option (I'm just more a fan of Java/URL)
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    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's avatar
    JoshuaTate
    Qrew Cadet
    8 years ago
    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)
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      8 years ago
      Notepad++ is fine for the size of code used with QuickBase. There may be a plugin for Notepad++ - maybe this:

      http://www.sunjw.us/jstoolnpp/

      Someone recently published a script that synced a more professional IDE to QuickBase code pages but that is probably overkill. I forget the details. People have flame wars over their preferred editor / IDE so offering an opinion is worthless.

      One thing that would help your forum posting is to always run code through jsbeautifier using two space indention and after pasting the code into the forum selected it and hit the "pre" button on the forum toolbar.
       $.get(dbidSOW, {
           act: "API_EditRecord",
           rid: F3,
           _fid_114: WRrid
         })
         .then(function(xml) {
           console.dirxml(xml);
         });
      This will help make your code more readable and avoid escaping problems introduced by the forum. It also helps my robot to parse the messages so I can file my activity and developments reports to the mothership.

    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      8 years ago
      Awesome thank you so much for your help -it's been invaluable
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      8 years ago
      Thanks - how kind of you!

      Get all your questions in today as I will be out of planet on Friday.
  • RickLindner's avatar
    RickLindner
    Qrew Trainee
    6 years ago
    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
    ------------------------------