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

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
  • (Edited)
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.
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
  • Frustrated PLEASE Help

Posted 9 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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
(Edited)
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
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 = ...
  }); 
});
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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.
(Edited)
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
lol thanks - Have a good rest, there's always tomorrow or some competition to helping me solve the case :D
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
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
Photo of Dan

Dan

  • 1,176 Points 1k badge 2x thumb
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.
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Thanks Dan, I will keep that in mind as an option (I'm just more a fan of Java/URL)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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);
   });
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
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)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
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.

(Edited)
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Awesome thank you so much for your help -it's been invaluable
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,272 Points 20k badge 2x thumb
Thanks - how kind of you!

Get all your questions in today as I will be out of planet on Friday.