Forum Discussion

AlAl's avatar
AlAl
Qrew Member
9 years ago

how do I create an append query?

I need to create an append query.

I have a "leads" table that outside agents populate with a potential sales lead. Once my agent accepts the lead he changes the status of that lead. I need a query, triggered by the status change to append the lead to my "clients" table.

Of course I'll also need a delete query to delete the record from the "leads" table.

In addition I'll need to be able to create some update queries too for other tasks in my database.

I can't find any help on this at all. Gee I miss SQL

APIs and custom code
reports and charts
roles and permissions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    In QuickBase, there are two native methods to do this, ie without using non native script.

    If you look for HELP on keyword API, you will find a decent set of APIs.  

    These can be triggered by using a formula URL field (button) for the user to click.  I have posted an app in he Exchange called URL Formulas for Dummies, which gives some example, and in particular how you string multiple API calls together in a sequence.

    For example, in your case you would want to API_AddRecord, and then call and API_DeleteRecord, and then you will need to display a record or a report or a Dashboard or else the screen will spew some XML success message to your user, which is not user friendly.

    Another option is using Webhooks, which were just recently released.


    My suggestion in your case is to use the URL formula buttons.  I will post an example for you when I get a chance, but meanwhile read the tips in that app I posted to the Exchange.
    • BarryMacMaster's avatar
      BarryMacMaster
      Qrew Member
      8 years ago
      Could you help me find your app URL Formulas for Dummies?  I seem to be even more of a dummy than expected : )   

      Thank you.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      no problem.
      Go to the My Apps Page at the top left.
      Then top right Free apps for you.
      Search on dummies

       
    • BarryMacMaster's avatar
      BarryMacMaster
      Qrew Member
      7 years ago
      I apologize for never having thanked you for this... thank you SO much!

      Cheers.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Here is an example of sequential actions in a URL call.

    var text URLONE = URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & ToText([Record ID#])
    & "&_fid_337=" & ToText(Today())
    & "&_fid_18=" & URLEncode("Booked as Help Ticket")
    & "&_fid_10=" & ToText(Today()); // Set Date Expected to Today()


    var text URLTWO = URLRoot() & "db/" & [_DBID_INVOICES] & "?a=API_GenAddRecordForm&_fid_165=" & URLEncode ([Record ID#])
    & "&_fid_171=" & ToText([Record ID# of Help Ticket])
    & "&_fid_176=0" // lock Related Help Ticket, Related Quote
    & "&_fid_101=" & ToText(Today()) // Set Date Finance to invoice as Today()
    & "&_fid_12=1" // Finance to invoice checkbox
    ;
    If([Customer PO Number]<>"",
    $URLONE
    & "&rdr=" & URLEncode($URLTWO)
    )


    Typically, you create a formula variable to hold each API call, and then you can string them together.  This one just has two being called, but you can actually string quite a few together before you reach a limit which seems to only be limited by the maximum leangth if a URL, like about 2,000 characters.

    Post back if you have more questions or contact me directly for one on one assistance for a QuickStart into URL Formulas or help with Webhooks.