Find the nearest Starbucks using QuickBase
Last week Howard popped into an "Office Hours" webinar asking about how to find the closest customers to a given Latitude and Longitude location. Well you can do that with QuickBase. I went looking for a CSV file with a bunch of Latitude and Longitude locations and I found a file with all the Starbucks locations in the US. The essence of the application is to find a Starbucks, enter the distance you want to search and return a report and map of all those locations within that distance.This can be calculated if you have both the Home and the Destination's Latitude and Longitude. So we now have a Locations table butktrachy@quickbase.com we need to add a parent table we can use to pass down all the home's Latitude, Longitude and Distance as lookup fields. We add the "Common Parent" table with only one record and made that related to all Location records. (we changed the reference field, "Related Common Parent" to a formula numeric field and put the number 1 in as a formula. Since there was only one Common Parent record, it's Record ID# was 1. So we created an automation that fires whenever a Location record's "Within Miles" field is updated. The automation will edit the parent record and update its Latitude, Longitude and Within Miles fields. This constant one to ALL relationship between the tables allowed us to always be sharing the last requested Latitude, Longitude and Mileage. Trigger: Action: Now that all the data is on all the location records, we added a formula numeric field to calculate the distance between the two. var number HomeLat = ToNumber([Home Latitude]); var number HomeLong = ToNumber([Home Longitude]); var number Lat = ToNumber([Destination Latitude]); var number Long = ToNumber([Destination Longitude]); var number Distance = Round(((69*($HomeLat - $Lat))^2 + (53*($HomeLong - $Long))^2)^0.5); If($HomeLat = 0 or $Lat= 0 , 0 , $Distance) Next we needed a way to flag only those locations that were within the "Within Miles" range. We created a formula checkbox field that would be checked if the Location record was within the range. So now we have the matching criteria to create the report link fields to display the results. The result: We cover topics like this in our daily, "Office Hours"webinars. Held M-F at 1:00 PM Eastern time. Feel free to drop in anytime. ------------------------------ Kirk Trachy , Senior Solutions Consultant QuickBase | 603-674-5454 M | ktrachy@quickbase.com ------------------------------
Magic Buttons to Collapse/Expand Sections & Scroll to Top
This post describes how to build a set of three buttons that aid navigation of long forms. When a form has many sections and fields, one solution is to use Tabs. However, if there are several sections within a Tab, users may still have to do a lot of scrolling and clicking. The idea proposed here is to create a set of three buttons that float, always visible, at the bottom of the form: [Back to Top] [Collapse] [Expand] [Back to Top] simply scrolls to a standard location near the top of any page (#navTop). While Quick Base has a handy icon to scroll to the top, it's placed near the top. Normally you want to do this when you are near the bottom, so it's handy to have a button located there. [Collapse] and [Expand] use fairly simple JavaScript to collapse or expand all sections. The versions described here are compatible with Tabs in the following sense. They only collapse or expand within the current Tab. With these buttons, a user who knows the structure and contents of a long form can collapse them all, then scan the section-headers to open just the one section they need. Or, they can set a preference by collapsing all, then clicking on their favorite sections to expand them, and leaving the form that way each time they exit. Of course, there's always the option to open up all the sections, if the user really likes to scroll. In short, the three magic buttons accommodate the preferences of just about any user. Step 1. Create a formula-text field called "Bottom Nav" for the three buttons. The field's checkboxes must have "Allow some HTML ..." checked and should have the "Reportable" and "Searchable" boxes unchecked. For the formula, insert this: var text buttonStyle=" class='Vibrant' style='padding:3px; white-space:nowrap; vertical-align:middle; line-height:21px;'"; var text imageStyle=" style='vertical-align:middle; background-color:white;'"; "<span style='font-size:120%; font-weight:bold; position:fixed; bottom:20px; left:44px; z-index:1000;'>" & "<a href='#navTop'" & $buttonStyle & ">" & "<img class='TblIcon20 Upload' " & $imageStyle & ">" & " Back to Top</a>" & "<a href=\"javascript:" & [Collapse Sections] & "void(0);\"" & $buttonStyle &">" & "<img class='TblIcon20 Animation' " & $imageStyle & ">" & " Collapse</a>" & "<a href=\"javascript:" & [Expand Sections] & "void(0);\"" & $buttonStyle &">" & "<img class='TblIcon20 OSI_Model' " & $imageStyle & ">" & " Expand</a>" & "</span>" Some explanation ... The buttonStyle and imageStyle variables set the size and style of the buttons to be reasonably compatible with Quick Base standards. The <span> is set to be positioned at the bottom, with a z-index that floats it above all other elements of the page, left-aligned so as to obscure data-entry fields and thus prevent mis-clicks. As shown here, all three buttons use one of the standard images that Quick Base supplies as table-icons. You may prefer to use other images, or skip the images and just use plain text for the buttons. Finally, the [Collapse] and [Expand] buttons require some JavaScript. That's the next step. Step 2. Create two global variables, in the Variables section of Settings for your app. "Collapse Sections" is the name you should give to a text variable that contains the following: $('.sectionTitle').each(function(){ var $titleWrap = $(this).parent(); var $expandedTitle = $titleWrap.hasClass('expanded') && (this.innerHTML.length > 0); var $tabWrap = $titleWrap.parent().parent(); var $hiddenTab = $tabWrap.hasClass('ui-tabs-hide'); if($expandedTitle && !$hiddenTab){ this.click(); } }); $('#backToTop').click(); In words, this script finds every expanded section within the current tab, clicks each of those section-headers to make the section collapse, then scrolls up. "Expand Sections" is the name you should give to a second text variable that contains the following: $('.sectionTitle').each(function(){ var $titleWrap = $(this).parent(); var $collapsedTitle = $titleWrap.hasClass('collapsed') && (this.innerHTML.length > 0); var $tabWrap = $titleWrap.parent().parent(); var $hiddenTab = $tabWrap.hasClass('ui-tabs-hide'); if($collapsedTitle && !$hiddenTab){ this.click(); } }); $('#backToTop').click(); This script finds every collapsed section within the current tab, clicks each of those section-headers to make the section expand, then scrolls up. Step 3. Place the "Bottom Nav" field on your form. It should be the last field on the form. In addition, it should be immediately preceded by a section-header that has no name. Why a nameless header? Because it will be exempted from the collapsing and expanding actions of the JavaScript. We don't want the buttons to hide (collapse) or expose (expand) themselves! This last point reveals an important caveat for the whole method described here. It works best on forms in which all sections, except the last one containing the buttons, have a name. That's because of the way Quick Base handles nameless sections; essentially, they are intended to stay expanded (within a Tab, if applicable).
How to display a thumbnail image on a table report
One of my favorite tips learned at EMPOWER2020 was from a session Chris Hutchens delivered titled Building for Mobile. He shared how to get a thumbnail image of a photo to display in a table report. While the session was geared towards mobile users, it's applicable to anyone that wants to show an image in a table report. I created a quick video to show demonstrate that little gem:https://youtu.be/M7p71Yo770c If you'd like to try this in one of your apps, simply watch the video and copy this code snippet to paste into a formula - rich text field: If(Length([Photo])=0,"","<img width=\"150\" src=\""&URLRoot()&"up/"&Dbid()&"/a/r"&[Record ID#]&"/e31/v0\"/>") Enjoy! ------------------------------ Freddie Sabbs Senior Solutions Consultant Quick Base fsabbs@quickbase.com Cambridge MA ------------------------------
Forms, Tables, HTML Add some spunk to your forms. Add formatting to your forms.
If you want to add a bit of separation to your form I stumbled onto this. Maybe it's published but this works. See attached video So you have "TABS" and "Text" for form formatting. Well, let's say you want to delineate an area in a form by putting a box around it. Editing Form: 1: (first "text" element.) <table style="width:100%;border:1px solid black"> 2: (last "text" element.) </table> You can encompass many fields on your form by putting the </table> further down. ------------------------------ Douglas Folk ------------------------------
I have a button on a tabbed form that edits the form and reloads the page. Is it possible to reload the page back to the same tab?
I have a button on a tabbed form that edits the form and reloads the page. When the page reloads, I'd like to be taken back to the same tab, rather than tab 1. Is that possible?
Working Copy to Clipboard Button
I've created a button to copy the content of a field to the clipboard that I thought would be useful to others. It copies the content of the field and then shows an alert that confirms what was copied. I've tested it with Chrome and Firefox which works, Edge did not. I used a Vibrant button, which allows me to put it into a Rich Text field with other buttons, instead of a Formula URL field. It will work with the URL field as well if you remove the button code. Hope this helps. Dana var text CB= URLEncode([Some Field]); var text Alert= URLEncode([Some Field] & " Was Copied to the Clipboard"); "<a class='Vibrant Success' style=\"border:0px solid green; background-color:green; text-align: center;\"href=" & "javascript:{" & "navigator.clipboard.writeText('" & $CB & "').then(function(){" & "},function(){" & "});" & "alert('" & $Alert & "');" & "};" & ">Copy to the Clipboard</a>"
A way to use Formula Queries to Assign Regions
I thought I would share a method I found to use Formula Queries to create an easily editable list of regional assignments for my users, without have to create any table-to-table relationships. I have two tables involved in this: Sites and FSC Assignments. Sites are a list of work sites and I needed a way to be able to give them regional assignments, but also to be able to edit these assignment easily. What I did was to create the table called 'FSC Assignments' and it holds only 1 record which is a list of all of the states and territories we work in and a list of my users regional assignments. It looks like this (I made a simple form to hold the fields neatly): Then, back in my Sites table, I have 2 fields using Formula Queries to grab the Region from the STATE/TERRITORY section and the user from the FSC REGION ASSIGNMENT in the single record of the FSC Assignmentstable. [Assigned Region] is just a Formula Text field and the code looks like this: If( [State]="Alabama",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),65)))), [State]="Alaska",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),66)))), [State]="Alberta",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),67)))), [State]="Arizona",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),68)))), [State]="Arkansas",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),69)))), [State]="British Columbia",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),70)))), [State]="California",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),71)))), [State]="Colorado",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),72)))), [State]="Connecticut",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),73)))), [State]="Delaware",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),14)))), [State]="District of Columbia",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),15)))), [State]="Florida",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),16)))), [State]="Georgia",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),17)))), [State]="Hawaii",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),18)))), [State]="Idaho",(ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),19)))), etc... The other is [REGIONAL FSC] and holds the user for a given region and the code looks like this: If( [Assigned Region]="Region 1", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),7)))), [Assigned Region]="Region 2", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),8)))), [Assigned Region]="Region 3", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),9)))), [Assigned Region]="Region 4", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),10)))), [Assigned Region]="Region 5", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),11)))), [Assigned Region]="Region 6", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),12)))), [Assigned Region]="Region 7", (ToText((GetFieldValues(GetRecord(1, [_DBID_FSC_ASSIGNMENTS]),13)))) ) The result is this: The region and regional FSC are picked from that single record in the FSC Assignments table. Should we need to change any of those, a user can simply edit the record and the changes will apply without any need to do anything else: I did not need to make any table-to-table relationships, though getting the code right took a little bit of tweaking. I don't know that this is the best way to do this, but it definitely works. I'm curious if anyone has any feedback on this method. Maybe it can be improved. ------------------------------ Jeff Peterson ------------------------------
