Discussions

Expand all | Collapse all

how do I create an append query?

  • 1.  how do I create an append query?

     
    Posted 10-09-2016 02:33

    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



  • 2.  RE: how do I create an append query?

    Posted 10-09-2016 12:27
    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.


  • 3.  RE: how do I create an append query?

    Posted 02-23-2018 17:21
    Could you help me find your app URL Formulas for Dummies?  I seem to be even more of a dummy than expected : )   

    Thank you.


  • 4.  RE: how do I create an append query?

    Posted 02-23-2018 17:25
    no problem.
    Go to the My Apps Page at the top left.
    Then top right Free apps for you.
    Search on dummies

     


  • 5.  RE: how do I create an append query?

    Posted 08-22-2018 02:09
    I apologize for never having thanked you for this... thank you SO much!

    Cheers.


  • 6.  RE: how do I create an append query?

    Posted 10-09-2016 12:37
    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.