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 ------------------------------
How do I make a random color button?
We had a user request this thinking since there is no random command it could not be done. We tried a few methods but found a native method that works best and is really fun. First create a rich text formula field that will generate the random color - [Randomish Button Color] Note: We used Now() and built in fields to generate the Base 16 and at the bottom we have only listed 20 permutations of the over 700 available. var text hh = Case(ToText(Mod(Hour(ToTimeOfDay(Now())), 16)), "0", "0", "1", "1", "2", "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "9", "9", "10", "a", "11", "b", "12", "c", "13", "d", "14", "e", "15", "f", ""); var text mm = Case(ToText(Mod(Minute(ToTimeOfDay(Now())), 16)), "0", "0", "1", "1", "2", "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "9", "9", "10", "a", "11", "b", "12", "c", "13", "d", "14", "e", "15", "f", ""); var text ss = Case(ToText(Mod(Second(ToTimeOfDay(Now())), 16)), "0", "0", "1", "1", "2", "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "9", "9", "10", "a", "11", "b", "12", "c", "13", "d", "14", "e", "15", "f", ""); var text ms = Case(ToText(Mod(MSecond(ToTimeOfDay(Now())), 16)), "0", "0", "1", "1", "2", "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "9", "9", "10", "a", "11", "b", "12", "c", "13", "d", "14", "e", "15", "f", ""); var text dd = Case(ToText(Mod(Day(ToDate([Date Modified])), 16)), "0", "0", "1", "1", "2", "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "9", "9", "10", "a", "11", "b", "12", "c", "13", "d", "14", "e", "15", "f", ""); var text zc = Case(ToText(Mod(Day(ToDate([Date Created])),16)), "0", "0", "1", "1", "2", "2", "3", "3", "4", "4", "5", "5", "6", "6", "7", "7", "8", "8", "9", "9", "10", "a", "11", "b", "12", "c", "13", "d", "14", "e", "15", "f", ""); // Permutations Case(Mod([Record ID#],20), 0, "#"&$mm&$dd&$hh&$ss&$ms&$zc, 1, "#"&$ms&$hh&$zc&$dd&$mm&$ss, 2, "#"&$ss&$mm&$ms&$dd&$hh&$zc, 3, "#"&$zc&$mm&$hh&$ss&$ms&$ms, 4, "#"&$zc&$mm&$ss&$hh&$dd&$ms, 5, "#"&$ss&$dd&$mm&$ms&$hh&$zc, 6, "#"&$hh&$zc&$mm&$ms&$dd&$ss, 7, "#"&$ss&$ms&$hh&$mm&$dd&$zc, 8, "#"&$hh&$ms&$ss&$mm&$zc&$dd, 9, "#"&$dd&$mm&$hh&$zc&$ss&$ms, 10, "#"&$dd&$ms&$mm&$ss&$zc&$hh, 11, "#"&$zc&$mm&$ss&$ms&$hh&$dd, 12, "#"&$mm&$hh&$ss&$dd&$ms&$zc, 13, "#"&$ms&$ss&$dd&$hh&$mm&$zc, 14, "#"&$mm&$hh&$ms&$dd&$ss&$zc, 15, "#"&$zc&$ss&$dd&$hh&$ms&$mm, 16, "#"&$zc&$hh&$ms&$dd&$mm&$ss, 17, "#"&$ss&$hh&$dd&$mm&$zc&$ms, 18, "#"&$mm&$hh&$dd&$ms&$zc&$ss, 19, "#"&$ss&$ms&$zc&$hh&$dd&$mm, "") Then create a formula checkbox field that will determine if the text should be black or white. [Threshold] - var number threshold = 105; var text R = Right(Left([Randomish Button Color],3),2); var text G = Right(Left([Randomish Button Color],5),2); var text B = Right([Randomish Button Color],2); var text RL = Case(Left($R,1), "f", "15", "e", "14", "d", "13", "c", "12", "b", "11", "a", "10", "9","9", "8","8", "7","7", "6","6", "5","5", "4","4", "3","3", "2","2", "1","1", "0"); var text RR = Case(Right($R,1), "f", "15", "e", "14", "d", "13", "c", "12", "b", "11", "a", "10", "9","9", "8","8", "7","7", "6","6", "5","5", "4","4", "3","3", "2","2", "1","1", "0"); var text GL = Case(Left($G,1), "f", "15", "e", "14", "d", "13", "c", "12", "b", "11", "a", "10", "9","9", "8","8", "7","7", "6","6", "5","5", "4","4", "3","3", "2","2", "1","1", "0"); var text GR = Case(Right($G,1), "f", "15", "e", "14", "d", "13", "c", "12", "b", "11", "a", "10", "9","9", "8","8", "7","7", "6","6", "5","5", "4","4", "3","3", "2","2", "1","1", "0"); var text BL = Case(Left($B,1), "f", "15", "e", "14", "d", "13", "c", "12", "b", "11", "a", "10", "9","9", "8","8", "7","7", "6","6", "5","5", "4","4", "3","3", "2","2", "1","1", "0"); var text BR = Case(Right($B,1), "f", "15", "e", "14", "d", "13", "c", "12", "b", "11", "a", "10", "9","9", "8","8", "7","7", "6","6", "5","5", "4","4", "3","3", "2","2", "1","1", "0"); var number Rvalue = (ToNumber($RL) * 16 + ToNumber($RR))*0.299; var number Gvalue = (ToNumber($GL) * 16 + ToNumber($GR))*0.587; var number Bvalue = (ToNumber($BL) * 16 + ToNumber($BR))*0.144; If(255-($Rvalue+$Gvalue+$Bvalue)<$Threshold, true, false) Now you have the 2 pieces and need to create the rich text formula button - // Begin button style var text bgcolor = [Randomish Button Color]; var text txtcolor = If([Threshold]=false,"white","black"); var text style = "style=\"text-decoration: none; box-shadow: 3px 3px 1px #888888; background:" & $bgcolor & "; border-radius: 3px; padding: 5px 8px; color: " & $txtcolor & "; display: inline-block; font-weight: normal;font: bold 700 24px/1 \"Calibri\", sans-serif; text-align: center; text-shadow:none;"; // End button style "<a " & $style & " href=\"javascript:" & "$.get('" & $URL & "', function(){" & "location.reload();" & "});" & "void(0);\">Crazy Button Name</a>" Note: $URL is the variable you can create to perform the button actions and the button is designed to return to any location that the button is used.
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).Buttons with color, function and flexability
I wanted a button that could be colored to any color, change data, disappear if criteria was met and return to the same location it was used thus allowing end users to have the button wherever they wanted and in multiple locations. Also I wanted to have name of the button be dynamic and the name change based on data in a the record. So pretty much I wanted most everything. Being a very low code person I used the forum and Quick Base support. I found that nobody was asking for all of that. In a matter of a week I have pieced together the code thanks to many contributors in the forum (if you answered anything on any of the items I wanted you are on the list). Here is the code and below some explanation, I hope this can help someone looking as much as I wanted. This is used in a text-formula field with html enabled. It is a check-in button (we also have a version that changes to a check-out after check-in is clicked...basically multi-function) // Begin button style var text bgcolor = "#A5E294"; var text txtcolor = "black"; var text style = "style=\"text-decoration: none;\n\n box-shadow: 3px 3px 1px #888888; \n\n background:" & $bgcolor & ";\n\n border-radius: 3px;\n\n padding: 5px 8px;\n\n color: " & $txtcolor & ";\n\n display: inline-block;\n\n font: bold 700 24px/1 \"Calibri\", sans-serif;\n\n text-align: center;\n\n text-shadow:none;"; // End button style var text URL = URLRoot() & "db/" & Dbid() & "?a=API_EditRecord&rid=" & [Record ID#] & "&_fid_13="&URLEncode(Now()) & "&_fid_25=" & URLEncode("Tech Onsite"); If(ToText([Lead Tech Day 1 Check-In])="", "<a " & $style & " href=" & "javascript:" & "$.get('" & $URL & "',function(){" & "location.reload(true);" & "});" & "void(0);" & "> Check-In "&[Technician Name]&"</a>", "") #A5E294 ? HTML color code for button color Black ? color wording for button text. This can be replaced with HTML color code & "&_fid_13="&URLEncode(Now()) & "&_fid_25=" & URLEncode("Tech Onsite"); This is the section of code defining the changes to make. Make sure that the last change end with a semi-colon! If(ToText([Lead Tech Day 1 Check-In])="", The if statement that creates the condition to show the button Check-In "&[Technician Name]&" The name of the button, note the syntax to use if wanting to have a dynamic name with data from a field in the record
Create a Summary Report from a Multi-select Dropdown Field
Suppose I have a Multi-select field with options (A, B, C) and 100 records where the field is used. Is it possible to create a report that counts each time an option is selected in each record? For example: A - 5 B - 8 C - 2 Above would indicate that the option A has been selected 5 times out of all of my records, B has been selected 8 times, and C has been selected 2 times. When I create a summary report for each I just get the count of each combination. Like A - 5 A;B - 3 A;B;C - 2Custom buttons for tabbed forms: Save & Next Tab, Save & Same Tab
Here's a method to do variants of "Save & keep working" within a tabbed form. First, I'll explain how to create a "Save & next tab" button. Then, with slight modification, I'll show "Save & same tab". [1] On the form properties, do not check "Show Save & close and Cancel buttons at the end of this form" and do check "Show save & keep working choice" [2] Create a rich-text-formula field called, say, "Custom Save and Next Tab". Temporarily, you can fill it the text "Save & next tab". Eventually, after completing the steps below, it will contain a custom Page and several custom Variables, like this: [customButtonScript1] & "saveAndNextTab.js" & [customButtonScript2] & [customButtonStyleSKW] & "Save & next tab" & [customButtonEnd] [3] In the settings for you app, click on Variables and add one called "customButtonScript1". You can use a different name, if you wish, but be sure to use that name in the first like of the rich-text-formula in step [2]. Whatever you call it, the variable should contains the following. The quotes are important. However, remove the spaces in h t t p s, use your company's setting for "yourdomain" and your app's id instead of "yyy999zzz" <a href='javascript:($.getScript("h t t p s://yourdomain.quickbase.com/db/yyy999zzz?a=dbpage&pagename= [4] Next, in the settings for you app, add a Page called "saveAndNextTab.js" (or another name you prefer, provided you use it in the second line of the rich-text-formula field"). The page should contains exactly this: var nextTabLink=$("li.ui-tabs-selected").next("[style!='display:none']").children("a"); $("#saveAndKeepWorkingMenuOption").click(); var noErrorDialog=$('#qbDialog').length==0; if( noErrorDialog ){ $(nextTabLink).click(); location.assign('#navTop'); }; In words, this script is (a) finding the currently selected tab, then the next that is not hidden, and it's name; then (b) clicking the standard "Save & keep working" button, emulating a user; and (c) checking for a pop-up error message, e.g., about failing to supply a required field; and finally (d) clicking the next non-hidden tab-name and scrolling up to where the tab-row will be visible. [5] Back on your app's settings, add another Variable to close the script. Call it "customButtonScript2" if you are sticking to the naming in step [2]. It must contain exactly this: "));void(0); [6] Next you need to style the custom button. I prefer doing so as follow, to make it resemble the standard save buttons. Add another Variable in you app's settings called "customButtonStyleSKW" which contains this: ' class='Vibrant Tall' style='font-size:14px; color:white; background:#6BBD57; white-space:nowrap;'> [7] Finally, in add one more Variable in your app's settings, called "customButtonEnd", containing simply: </a> Of course, you could just use "</a>" as the final line of your rich-text-formula field, but I prefer the additional Variable for readability ... and just in case I ever want to make some sort of global change for all my custom button's endings. [8] Oh, and to get this button on your forms, add it at the end of every tab. (On the last tab, it will stay there, after not finding any next tab.) Naturally, you will want to use an empty "alternate label" for that row of the form, and set it for add and edit modes only. To modify this method for "Save & same tab", the main change is in the first line of the script in step [4]. Use this: var sameTabLink=$("li.ui-tabs-selected").children("a"); Also, later in the script, use: $(sameTabLink).click();
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 ------------------------------Can you configure the Copy Master/Detail Records button?
How much flexibility do you have with the Copy Master/Detail Records button? I would like to be able to configure a couple things: Exclude fields from being copied Edit the values in certain copied fields Is this possible? I suppose I might be able to make a custom copy button using the API, but if possible this would be a much better option."Soft Delete" | Protecting your data from accidental deletion
A customer asked me today to help him recover records that were accidentally deleted. Well, hopefully you know that in an emergency, you can open a support case and our care team can help but he wanted to PREVENT the event from happening in the first place. My immediate thought was that this was another use case that would benefit from having a restore feature but there is a better and simpler solution using what one of our Senior Product Manager, Harrison Hersch calls "Soft Delete". Say you have a community of users that are notoriously deleting records and you need to restore them. For simplicity we'll call them Sales people, but we have all done this. How about we craft the Sales role to not allow record deletions? This solves part of the problem, but the other part is that the Sales people really would like to delete some records. Well, how about adding a checkbox we can call, "Soft Delete" and we adjust the Sales Role so they can only view records where the "Soft Delete" checkbox is unchecked. This now means that the Sales people can check the checkbox and the records will disappear from view. Whatever record they check will appear to be deleted but in fact it has only disappeared from their Sales role's view. We can then create a report that only contains records with the checkbox check and Administrators can now review, restore or permanently delete. A whole bunch of things can be added to this process to make it slick and easy to manage. We can add a button that checks that checkbox We can create a subscription report to remind the Administrator to review, restore or delete the records permanently We can create a schedule automation to delete the flagged records We can add a table to the app to handle Sales people's escalation requests to restore accidentally deleted records. The list can go on... Steps: Add the "Soft Delete" checkbox to your form or report. Create a Sales role (or whatever community of users you want protection) Flag the role so Deletions are disallowed Customize the View so you can only see records where the "Soft Delete" is not checked Optionally add a Formula URL button that will make checking the checkbox easier. You can press the button whether you are in display mode or edit mode. If you don't have a button, you will have to put the record in edit mode to check the checkbox. The code for the Formula URL button can look something like this: var text url = If([Soft Delete] = false, URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken=biwvb32baypdx8cqfjj9pk2xiim&rid=" & [Record ID#] & "&_fid_26=1", URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken=biwvb32baypdx8cqfjj9pk2xiim&rid=" & [Record ID#] & "&_fid_26=0"); "javascript:" & "$.get('" & $url & "',function(){" & "location.reload();" & "});" & "void(0);" Update the apptoken specific for your application and change the fid_26 to reflect the field ID number of your checkbox field. NOTE: QuickBase does not support the use of JavaScript in buttons. An alternative button below may be used instead but you will have to manually decide where you want to redirect. In the example below this will leave you on a report (qid=1). If([Checkbox] = false, URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken=c7y3n83dz8fuc2n8jum5dzq4iky&rid=" & [Record ID #] & "&_fid_101=1" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=q&qid=1"), URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken=c7y3n83dz8fuc2n8jum5dzq4iky&rid=" & [Record ID #] & "&_fid_101=0" & "&rdr=" & URLEncode(URLRoot() & "db/" & Dbid() & "?a=q&qid=1")) We cover topics like this each weekday in our "Office Hours" webinars. This is an open conversation inviting questions and live modeling of how-to solutions. M-F at 1pm Eastern time. Register at: http://quickbase.com/webinars/ ------------------------------ Kirk Trachy QuickBase | Senior Solutions Engineer | 603-674-5454 M | ktrachy@quickbase.com ------------------------------
