Forum Discussion

GaryChristopher's avatar
Qrew Member
2 years ago

Using variables in API URL requests

Im having trouble getting the API to post the value of the field instead of the literal text name of the field.  What am I doing wrong?  Obviously I am new to this.  It creates the new record in the intended table but the data doesnt come, just the literal text.  Here is my URL formula:

"$[Sage - Date]&_fid_7=$[Sage - Revenue Credit]&_fid_6=$[Sage - Purchase Debit]&_fid_15=$[Sage - Project Number]&_fid_13=$[Sage - Vendor]&_fid_14=$[Sage - Vendor Invoice Number]&_fid_10=$[Sage - PO Number]"

Gary Christopher

4 Replies

  • Here us what I suggest. Consider this post to be Quickbase Formulas 101.

    // here we define a formula variable of type text called Add
    var text Add = 
    URLRoot() & "db/" & [_DBID_ XXXXXXXX] & "?a=API_AddRecord"
    & "&apptoken=dezw24pd9yryghbkmmmfcb75e6zv"
    & "&_fid_9="  &  [Sage - Date]
    & "&_fid_7="   &  [Sage - Revenue Credit]
    & "&_fid_6="  &  [Sage - Purchase Debit]
    & "&_fid_15=" &  URLEncode([Sage - Project Number])
    & "&_fid_13=" &  URLEncode([Sage - Vendor])
    & "&_fid_14="  & URLEncode([Sage - Vendor Invoice Number])
    & "&_fid_10=  &  URLEncode([Sage - PO Number]);

    // Each formula variable needs to end with a semicolon.

    var text RefreshPage =  URLRoot() & "db/" & Dbid() & "?a=doredirect&z=" & Rurl();

    // the & outside quotes is the same concatenation character like Excel.  But inside the quotes it says to Quickbase - hey Quickbase parameter name is comin' at ya next.

    // here we string them together to run them.  When referring to a previously defined Formula Variable, it starts with a $.

    & "&rdr=" & URLEncode($RefreshPage)

    The use of URLRoot() will ensure that your Formula URL Button still works if the name of your Realm changes or you transfer the app to another Realm.

    The use of the [_DBID ......] as the table name will ensure that your app will still work if make a copy of it for testing, for example.  Replace the XXXX with the real table name at the bottom of Advanced Settings for the table.

    Quickbase is not Excel.  You can add in comment lines by starting the line with 

    // like this  or using the // at the end of a line.

    unlike Excel, you can also liberally use vertical spacing to make the formula more readable.

    Write these type of formuals vertically in a pattern to help readability.  The truth is that basically no one documents apps, so make sure that the formulas are stupid simple to read.   

    One you call an API, if run alone it will spew back the success or failure message on the screen which is very user unfriendly, so you need to land the user on some kind of report or record.  The syntax I provided will just refresh the page you were on.

    The generic syntax for sequentially calling APIs in one button is like this:

    & "&rdr=" & URLEncode($URLTWO)
    & URLEncode("&rdr=" & URLEncode($URLTHREE))
    & URLEncode(URLEncode("&rdr=" & URLEncode($URLFOUR)))
    & URLEncode(URLEncode(URLEncode("&rdr=" & URLEncode($URLFIVE))))

    So just slice off as much of the salami as you need being sure to have the last one land the user on some kind of record or report or dashboard.

    Make use of Formula Variables to enhance readability

    There you go, master this and you can move on to Formulas 201 lessons.  Feel free to post back ... we are very friendly here.

    Mark Shnier (Your Quickbase Coach)

    • MarkShnier__You's avatar
      Icon for Qrew Legend rankQrew Legend

      I forgot to explain URLEncode.  When doing successive calls, you just have to use that crazy nested format.  Can't explain why, you just do. 

      But when putting values into a field and those values may have spaces or special characters, (For example [Sage - Vendor] might be a Vendor name I suppose), then it needs to be URLEncoded to make it a valid string which will be able to navigate the great unknown magic of the internet and get back to your table.  Ever notice, for example, that in all your time wasted surfing the net, that no URL ever had a space in it?  Well there are also other illegal characters, so the URLEncode allows those special characters to get through the internet.

      Mark Shnier (Your Quickbase Coach)
      • GaryChristopher's avatar
        Qrew Member

        Thank you so much Mark!  I wasn't expecting an answer midnight Saturday but was happy to get it.  It works, although I cant get the redirect to work for some reason.  QB is telling me I need a semicolon at the end of the variable declaration, but its there.  Anyway Ill sort that out later.  Thanks again!