how do I create an append query?

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered

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

Photo of Al

Al

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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.
Photo of Barry

Barry

  • 40 Points
Could you help me find your app URL Formulas for Dummies?  I seem to be even more of a dummy than expected : )   

Thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
no problem.
Go to the My Apps Page at the top left.
Then top right Free apps for you.
Search on dummies

 
Photo of Barry

Barry

  • 40 Points
I apologize for never having thanked you for this... thank you SO much!

Cheers.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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.