Quick Base Formula URL for multiple actions

zac

In Quick Base, you can create buttons using Formula URL fields to trigger multiple actions – however, it’s complicated. A reminder for myself in case I ever need to set something like this up again – this formula adds a record to a child table, updates a field on the current table and immediately re-displays the form in “view” mode using a series of URL encoded ‘chunks’ that are all strung together at the end:

var text AddChildRecord =
URLRoot() & "db/" [_DBID_XXX]
& "?a=API_AddRecord&_fid_nn="& URLEncode([Record ID#])
& "&_fid_nn="& URLEncode("Add Text Here")
& "&_fid_nn="& URLEncode(User())
& "&_fid_nn="& URLEncode(Today())
& "&apptoken=9jabnbdh53n9gd4kwhxa7s8gn1";

Because we’re using the API_AddRecord function to add a record to a child table, you have to specify the DBID. To find the DBID alias, go into the Settings for the child table, then select Advanced Settings and scroll to the bottom of the page. Record ID will need to be updated to reflect what records are called in that table (unsure whether this needs to be passed through the URLEncode function or not – but it works).

The &_fid_nn items are form fields (nn is the field ID); to find these, go to Settings for the child table, then select Fields under table structure. Hover your mouse over the field name to see the field ID.

The last line is only required if app tokens are enabled for the Quick Base app.

var text EditRecord =
URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_nn=" & URLEncode("Yes")
& "&apptoken=9jabnbdh53n9gd4kwhxa7s8gn1";

Editing a record in the existing table using the API_EditRecord function is much easier. Use the Dbid() function to specify the current table, specify the changes you want and add an apptoken if needed.

var text Display =
URLRoot() & "db/" & Dbid()
& "?a=dr&rid=" & [Record ID#];

This displays the record again using the same form. Here’s a breakdown of what each bit means:

URLRoot()Go to https://mycompany.quickbase.com/
& “db/”Add the “db/” you see in all URLs
& Dbid()Add the Table ID you are currently on
& “?a=dr&rid=”This says “Action = Display Record and Record ID = …”
& [Record ID#]Specify the Record ID to display
$AddChildRecord
& "&rdr=" & URLEncode($EditRecord)
& URLEncode("&rdr=" & URLEncode($Display))

String everything together – note the nested URLEncode functions and the lack of a semicolon at the end.

Perl CGI.pm and Unicode

zac

Because I always forget how to handle Unicode input/output in Perl .. a few notes to help jog my memory for next time:

Remember to use both CGI.pm and Encode.pm:

use CGI qw(:standard);
use Encode qw(decode encode);

Handle webform input with Unicode characters:

my $input = decode('utf8', param('input'));

Prepare scalar variables to cope with Unicode characters:

my $output = encode('utf8', $input);

Declare the correct Content-Type when creating HTML:

print header(-type => 'text/html', -charset => 'utf-8');
print start_html(-title => 'Page with Unicode', -encoding => 'utf-8');

Now you can print your Unicode variables without error:

print div({-id => 'output'}, $output);

Configuration notes for a Ubiquiti UniFi setup

zac
This post is somewhat outdated; I’d recommend checking out my new guide to configuring the UniFi Controller inside a Docker container with a Let’s Encrypt SSL certificate instead of this one!

 

Ubiquiti make some shiny WiFi gear that falls in to the “Enterprise Lite” space – not quite as robust as Cisco Meraki or Aerohive gear, but also nowhere near as expensive. Here are some notes I took while setting up my home WiFi with two UniFi UAP-AC-PROs and the UniFi Controller running in the cloud:

UniFi Controller

Get the Controller up and running before anything else. The easiest way to do this would probably have been to get a UniFi Cloud Key to run the Controller locally, then manage it using a Ubiquiti account – but seeing as I was too cheap to buy a Cloud Key, and I already have a server that I’m using for other things – it made sense for me to run the Controller on that server. I mostly followed the instructions for a Debian based UniFi Controller, with the following gotchas:

  • The firewall instructions open more ports than are absolutely necessary, and
  • The firewall instructions assume you’re crazy and want to configure iptables by hand, rather than using something like ufw

Another thing to keep in mind: the ports that the UniFi Controller use will cause problems if you’re also running Tomcat on the same server (or plan to do so in the future).

I decided to change the ports for the UniFi Controller – in the following configuration file, add the following two lines (don’t uncomment the lines towards the top of the file – as I understand it, you can’t have whitespace or comments after configuration parameters):

/var/lib/unifi/system.properties

unifi.http.port=9080
unifi.https.port=9443

Restart the Controller:

sudo systemctl restart unifi

Firewall

As I’m running the UniFi Controller out on them Internets and have no intention of accessing the Controller via the web on port 9443, the collection of ports I need to open are:

  • TCP/9080 – the port that the APs use to communicate with the Controller
  • TCP/8880 – used by the guest portal (could be dropped?)
  • TCP/8843 – also used by the guest portal (could also be dropped?)

I’m not including the following ports, for the following reasons:

  • UDP/3478 – used for STUN by the UniFi VoIP software
  • TCP/8081 – I can’t see a good reason to leave a “shutdown” port open
  • TCP/9443 – I’m running a reverse proxy for the UI (see below), so it’s not needed
  • UDP/100001 – only needed when the Controller is in the same subnet as the APs

Because I don’t hate myself, I didn’t attempt to configure iptables by hand. Here’s my ufw application file:

/etc/ufw/applications.d/unifi

[UniFi]
title=UniFi Controller
description=Ubiquiti UniFi Controller
ports=8843,8880,9080/tcp

Once the above file has been created, enabling the firewall rules couldn’t be simpler (you still need to ensure ufw is otherwise correctly configured first):

sudo ufw allow unifi

Apache reverse proxy

The UniFi Controller comes secured with a self-signed SSL certificate, and while it’s possible to change the Controller UI port to 443 and install a regular SSL certificate – it’s also a complete pain in the ass (and/or impossible if you’re already running a webserver). Instead, I set up a reverse proxy in Apache to handle the redirect and make it much, much easier to manage the SSL stuff. Here’s my Apache config file for the relevant virtualhost:

/etc/apache2/sites-available/unifi.controller.fqdn.conf

<IfModule mod_ssl.c>
<VirtualHost *:443>
ServerName unifi.controller.fqdn
ServerAdmin hostmaster@example.com
 
CustomLog /path/to/access.log combined
ErrorLog /path/to/error.log
 
SSLProxyEngine On
SSLProxyCheckPeerCN off
SSLProxyCheckPeerName off
ProxyPreserveHost On
ProxyPass / https://unifi.controller.fqdn:9443/
 
SSLCertificateFile /path/to/fullchain.pem
SSLCertificateKeyFile /path/to/privkey.pem
Include /etc/letsencrypt/options-ssl-apache.conf
</VirtualHost>
</IfModule>

Couple notes:

  • No need for a DocumentRoot – I’ve set this site up purely as a redirect, but you may want to do things differently
  • All of the SSLProxy.. lines are required – this is to avoid problems with the self-signed certificate on the Controller
  • The Include /etc/letsencrypt.. line – I’m using a Let’s Encrypt certificate, so this line is standard (your SSL configuration lines will probably be different if you’re using a different CA)

Once the above is up and running, you should be able to access the UniFi Controller at https://unifi.controller.fqdn/. Run through the setup wizard, but I’d recommend setting up another site (separate to “Default”) for the first site if you plan to have several sites managed by the Controller.

Setting up the APs

Try though I might, I couldn’t get the various versions of the UniFi Discovery Tool to work at all. I gave up after an hour – save yourself the grief and configure the inform URL by SSH’ing in to each AP after it picks up a DHCP lease (default user/pass is ubnt/ubnt) with the following command:

set-inform http://unifi.controller.fqdn:9080/inform

Wait a few seconds, then log in to the Controller and complete the “adoption” process. After your new AP is adopted and assigned to a site, go back to the SSH console and re-run the same command:

set-inform http://unifi.controller.fqdn:9080/inform

This saves the inform URL in case the AP reboots, etc.

Other Config Notes

Beyond the standard WiFi configuration, I also have the following items set for each AP:

  • Minimum RSSI set to -75 dBm (-80 dBm is a good starting point for experimentation)
  • Band Steering set to Prefer 5G
  • Airtime Fairness set to On

And the following set in the site configuration:

  • Enable status LED unchecked

If you ever need to ssh back in to the APs, the default username and password will not work. The new username and password are shown in the Controller – go to Settings and select the site the APs are connected to – the username and password are listed at the bottom of the page.