Forum Discussion

JoseVieira's avatar
JoseVieira
Qrew Trainee
8 years ago

Is it possible to use webhooks to trigger scripts?

I have a script that does some things that, as far as I understand, cannot be done with POST webhooks, but may be possible with GET. Let's say we have 3 tables, T1, T2 and T3. T3 is children of T1 and T2, but there is no relationship between T1 and T2. T2 has N records. When a new record is added to T1, I need N new records to be created on T3, like that:
1. Each new record will be related to each record of T2. That means, there will be N children on T3 related to each of the N records of T2
2. All those new N records of T3 will also be related to the new T1 record.

Trying to make thing clearer:
T1 gets a new record, with ID 'a'. T2 has N records, with ID's b1, b2, ... bN. The new record on T1 will trigger new records being created on T3 the following way:
Related T1, Related T2, data...
a, b1, ...
a, b2, ...
a, b3, ...
...
a, bN, ...

I have this working fine with my script. But I needed to put that script on a button and click on that button every time a new record is created on T1. I would like to run this script automatically when a new record is added. So, I thought about using Webhooks. What I have tried, without success, was to use as EndPoint URL the URL for my code page with the scripts, with all its querystring parameters, and selected the GET HTTP method. I'm not sure about which headers i should use for the GET. I believe that this is not Webhooks were created for but, If there is a way to run the script with or without webhooks, that's fine for me.

Thanks!
APIs and custom code
App builders
Ask about Quickbase functionality
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Unfortunately, you cannot use QB Webhooks to run a QB Code Page.  You will need to get your server involved or an outside service to run the needed script.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    If you are already using script there is no need to complicate the matter buy involving a webhook or an external server. Just extend your script to perform the creation of the new records automatically rather than using a button to kick start the process. I can't offer any more advice without seeing what script you already have so post a simplified version of it without unnecessary details.

    There is a big misconception about webhooks. First of all they are not really a "thing" - there is no web standard or API for webhooks. It does not exist. They are just a marketing term / buzz word for URL endpoint and set of parameters established by convention that can be used for mostly unidirectional communication between two web services. And in the context of QuickBase webhooks work best when communicating between QuickBase and external service (rather than QuickBase to QuickBase). A QuickBase to QuickBase webhook can only react to a very limited number of events related to records but a script can react to thousands of different events.

    Also, what do you mean by "there is no relationship between T1 and T2"? Which table is the parent and which is the child?
  • JoseVieira's avatar
    JoseVieira
    Qrew Trainee
    8 years ago
    Thanks for the quick repy,
    Let me try to explain with a little more details. The app is to track people skills, so we know who needs training on what. So, the tables are:

    Table 1: People
    • ID,User (unique),Role
    • 1,Joe,Role1
    • 2,Janet,Role2
    • 3,Carl,Role1
    • 4,Mary,Role3
    • 5,Carol,Role2
    • 6,Thomas,Role1
    Table 2: Skills
    • ID,Description,Role
    • 1,Knows A,Role1
    • 2,Knows B,Role1
    • 3,Knows C,Role2
    • 4,Knows D,Role1
    • 5,Knows E,Role3
    • 6,Knows F,Role1
    • 7,Knows G,Role2
    Table 3: Skills matrix (it's basically a product of tables 1 and 2)
    • ID,Person ID,Person name,Role from Person,Skill ID,Skill Description,Role from skill,Assigned,Status
    • 1,1,Joe,Role1,1,Knows A,Role1,1,0
    • 2,1,Joe,Role1,2,Knows B,Role1,1,1
    • 3,1,Joe,Role1,3,Knows C,Role2,0,0
    • 4,1,Joe,Role1,4,Knows D,Role1,1,0
    • 5,1,Joe,Role1,5,Knows E,Role3,0,0
    • 6,1,Joe,Role1,6,Knows F,Role1,1,0
    • 7,1,Joe,Role1,7,Knows G,Role2,0,0
    • 8,2,Janet,Role2,1,Knows A,Role1,0,1
    • 9,2,Janet,Role2,2,Knows B,Role1,0,0
    • 10,2,Janet,Role2,3,Knows C,Role2,1,0
    • 11,2,Janet,Role2,4,Knows D,Role1,0,0
    • 12,2,Janet,Role2,5,Knows E,Role3,0,0
    • 13,2,Janet,Role2,6,Knows F,Role1,0,0
    • 13,2,Janet,Role2,7,Knows G,Role2,1,1
    • ...
    So, each time a new person is added, a list of all Skills from the Skills table is created for that person on the Matrix. Each person has a role, and each skill is related to a role. On the matrix, when the role from a given person and the role from a given skill match, it means that that skill is assigned to that person. This is done by the 'Assigned' field, which is a calculated field. This way, if a person moves to a different role, he or she gets a new list of required skills. Also, when a role is assigned to a different person, that person will have a new list of required skills. The status field is used to track if a given person is qualified on a given skill. It can be 1 even if that skill is not currently assigned, because that person was qualified previously on a different role. So, if he/she goes back to that role, the status is still there.

    This is the script. It's a simplified version, without error handling. All I need is to execute it each time a new person is added. Actually, I'll need a similar script for new skills, but the idea is the same:
    $(document).ready(function(){   //get person id from querystring
     var person = getQueryVariable('rid');
     //clean up matrix items without related skills
     var url = 'bmzta34wf?a=API_PurgeRecords&query={6.EX.}&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
     var promise= $.get(url);
     //clean up matrix items without related people
     url = 'bmzta34wf?a=API_PurgeRecords&query={31.EX.}&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
     promise= $.get(url);
     //get all skills from Skills table
     url = 'bmzizdnpr?a=API_DoQuery&clist=a&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
     promise= $.get(url);
     $.when(promise).then(function(xml){
      //loop through skills
      $(xml).find('record').each(function(){
       //get skill id
       var skill = $(this).find("record_id_").text();
       //check for existing combination of skill and person on 'SKills List' table
       var url = 'bmzta34wf?act=API_DoQueryCount&clist=a&query={6.EX.' + skill + '}AND{31.EX.' + person + '}&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
       var promise= $.get(url);
       $.when(promise).then(function(xml){
        $(xml).find('qdbapi').each(function(){
         //check if an item with the skill x person combination already exists
         var matches = $(this).find('numMatches').text();
         //if it does not exist, create it
         if(matches == 0) {
          var url = 'bmzta34wf?act=API_AddRecord&clist=a&_fid_6=' + skill + '&_fid_31=' + person + '&apptoken=bh89wwc8sxc9ac2g83fme83nsi';
          var promise= $.get(url);
         }
        });
       });
      });
     })
    })
    //function to get querystring values from variables
    function getQueryVariable(variable) {
       var query = window.location.search.substring(1);
       var vars = query.split('&');
       for (var i=0;i<vars.length;i++) {
         var pair = vars.split('=');
         if(pair[0] == variable){return pair[1];}
       }
     return(false);
    }

    Thanks!
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    I ran your code through jsbeautifier.org with 2 space indent and placed it online via pastebin.com:

    https://pastebin.com/FbEsPDZ6

    This is the preferred way to share code as the forum often messes it up.

    There are a number of things you could do to improve your code


    (1) use $.ajaxSetup({data: {apptoken: apptoken}}); to avoid appending the apptoken to every AJAX call

    (2) I am not sure why you are using $().each() as there will only be once qdbapi element in the XML response:

          $(xml).find('qdbapi').each(function() {

    Note $(<a>).find(<b>) is equivalent to $(<b>, <a>)

    (3) your utility function getQueryVariable should probably return undefined instead of false and should probably do some URL decoding. I would look to the internet for a better version (there are dozens of these floating around)

    (4) $.when(...) is probably better used with a list of promises all of which have to resolve before the when resolves. Note this code:
          var promise = $.get(url);
          $.when(promise).then(function(xml) { ...
    is largely equivalent to:

        $.get(url).then(function(xml) { ...

    but I assume it works for your purposes. If you goal is to automatically get the code to execute after clicking the save button you could use this technique to do so:

    Save and Scooby Doo Technique
    https://community.quickbase.com/quickbase/topics/what-is-the-save-and-scooby-doo-technique
  • JoseVieira's avatar
    JoseVieira
    Qrew Trainee
    8 years ago
    Dan,

    Thanks for the tips on making my code simpler. Most of it is based on samples from the QB API documentation. BTW, I started using jquery after starting to develop QB apps. So, I'm still new on this jquery thing. It's always good to know that things can be simpler.

    Regarding the 'Save and Scooby Doo' technique, I've implemented it and it worked fine, allowing me to do exactly what I have described in the first post. Now I'm considering to use it on other apps, for which I have different solutions, most of them based on buttons that trigger sync scripts.

    Regarding the copyright note, I'm including it as text on the forms for which I'm using the technique. Is that correct?

    Thanks!