Sneak Peek: Type-Ahead Search Picker
While we at Quickbase love to work on great new features, like Kanban reports or Automations, we also take time to focus on the fundamentals. And what could be more fundamental to Quickbase than quickly and accurately entering data? So many of our customers tell us they come to Quickbase apps because they've outgrown spreadsheets and need to move to a relational database to handle the complexity of the work they need to track. Recently, we've been taking a hard look at how we can improve this fundamental activity of Quickbase apps: relating data. What did we find? After talking to customers with big data sets and small, we realized our record picker needed to be better at finding records quickly and making sure users knew exactly what record they were picking! If you're not familiar with our record picker, here's a crash course. (You can find out more about configuring it in Quickbase University.) When you're working with a relatively small data set, we show a simple HTML drop down. It works well if you've got some easily discernible records and you know what you're looking for, but if you've got a larger set of data, it can be much harder to find what you're looking for. Now, one of the things we all love about Quickbase is how deeply customizable it is. So what if I want to only pick from records which meet a certain criteria? Or if I want to make sure the most likely to be picked options are at the top? Quickbase has a solve for that! In the form properties, you can choose a report on which to base the record picker, which allows you filter and sort the field. It's great that you can apply sorts and filters, but unless you really trim down the columns, it can be really hard to read for end users. Beyond that, they may not understand what data is displayed in the list and given that each record displays all its data on one line, it can be hard to match up values to compare them. In addition, our users told us they expected to search through these records to find what they were looking for, but our simple record dropdown doesn't support that. For search, we need to go into the record picker pop-up. In field properties, you can configure this relationship to always use our record picker pop-up but getting to it requires more clicks and takes the user out of the context of the record they're editing. What we found in talking to customers was that their teams needed a faster way to search their data. After seeing all this, we got to work. Presenting the new Quickbase Type-Ahead Search Picker: If your app builder has chosen to display only one field to pick records on, we're now going to present a fast, searchable picker, very much like the new field picker we rolled out back in spring or the user picker we've have. If your app builder has chosen a report or the standard record picker to select from, you'll see a multicolumn picker, able to display up to nine fields of relevant data, with each column labeled so users clearly can see what they're picking from. We load the first 50 results automatically, so users can browse. As you search, the list updates in real time, pulling in more records and highlighting the matching terms, very much like our user field type. This new picker will be rolling out to all users in the coming months. If you'd like a sneak preview, we have it available in early access starting the week of September 24th. Just have your realm administrator open a support ticket and request it at https://login.quickbase.com/qb/support/listcases Thanks for reading and be on the look out for more posts and previews about our new type-ahead search picker! Sam Jones Quickbase Product Manager
Service Worker to the Rescue
There has been a lot of traffic opining about the new release that changed various aspects of the GUI. To be honest I didn't read most of the posts in detail nor do I have any problem overcoming any of the undesirable changes. That is because I can modify any aspect of QuickBase using Service Workers. You can to. As an example, here is a grid edit viewof an application that shows the current green New Record button which I want to remove using a Service Worker: Here is a screenshot of the same page where a Service Worker has removed the green button: Now you may be wondering what the left hand panel is and why the URL is showing the domain https://cloudflareworkers.com/. In a nutshell, cloudflare has implemented a jsfiddle like service where you can demonstrate and share the implementation of a Service Worker as applied to any URL. It just turns out that this is an easier way for me to publiclly share a Service Worker demo rather than implement it wholly within QuickBase. Here is full source of the Service Worker used above: addEventListener("fetch", event => { event.respondWith(fetchAndModify(event.request)); }); async function fetchAndModify(request) { console.log("got a request:", request); // Send the request on to the origin server. const response = await fetch(request); // Read response body. const text = await response.text(); // Modify it. const modified = text.replace( '<a onclick="GE_embMenu_newRec(this)" id="GE_NewRecordPick">New Record</a>', ""); // Return modified response. return new Response(modified, { status: response.status, statusText: response.statusText, headers: response.headers }); } Basically the Service Worker looks for the <a> element associated with the New Record button and removes it - BEFORE THE BROWSER EVEN STARTS TO RENDER THE PAGE. Now the important thing to notice is that this very same Service Worker implemented as a part of the CloudFlareWorker demo can be used as a client side Service Worker. (wholly within QuickBase) Now I could write a hundred pages extolling the virtues of using Service Workers with QuickBase but the bottom line is there is nothing Service Workers can't do. Service Workers is god mode for QuickBase. UPDATE: I forgot to post the link: https://cloudflareworkers.com/#28a28d6d0a693d85a38d856f44a8de37:https://haversineconsulting.quickbas...~ Similar to a jsfiddle, you can modify the Service Worker code in the left panel and create your own version for experimentation before porting the code to your own application.How to install BOL (Branding On Load)?
We previously posted the BOL (Branding On Load) instruction as a file attachment but in the conversion to the new forum attachments were dropped. Here is an updated method of installing BOL: Step 1 Create a code page named moduleBOL.js and place some simple content in it such as this one line of code: console.log("moduleBOL.js loaded"); Step 2 Visit your Branding Page https://<YOUR SUBDOMAIN>.quickbase.com/db/<YOUR APP DBID>?a=AppPropertiesBrandGuide Step 3 To turn BOL ON, press F12 and paste this code into the console tab: //turn on BOL (function(){ var page = "moduleBOL.js"; var formData = new FormData(); formData.append("PageToken", $("input[name=PageToken]").val()); formData.append("customHeader", "on"); formData.append("uiCustomHeaderOptions", "iBasic"); formData.append("uiCustomTextColor", 'white !important\n}</style><img src='' style='display:none' onerror=$.getScript(gReqAppDBID+'?a=dbpage&pagename=${page}');><style>foo {\nbar: baz'); fetch("?a=SaveAppSettingsBrandGuide", { method: "POST", body: formData, credentials: "include" }); })(); Step 4 To turn BOL OFF, return to the branding page, press F12 and paste this code into the console tab: //turn off BOL: (function(){ var formData = new FormData(); formData.append("PageToken", $("input[name=PageToken]").val()); formData.append("uiCustomHeaderOptions", "iDefault"); formData.append("uiCustomTextColor", "white"); fetch("?a=SaveAppSettingsBrandGuide", { method: "POST", body: formData, credentials: "include" }); })(); Notes: 1) This code uses the Fetch API, the FormData API and ES6 backticks. You can google these terms or ask a question here if you need a better understanding. 2) Some people have inquired about the credentials: include property and value used in the code. This is not your QuickBase login/password credentials and is perfectly safe to use. By default the Fetch API does not send cookies so including credentials: include merely sends your cookies to QuickBase which includes your ticket. Sending your cookies to QuickBase is the normal operation. 3) Setting up BOL will include moduleBOL.js on every page in your application including administrative pages. In contrast the IOL technique injected code only into {new, view, edit, report, grid edit} pages. Also, moduleBOL.js will load relatively early in the process of loading your page. You will have to supply your own decoding logic to determine what page you are on and what actions you want to take. 4) Part of the reason we are introducing this version of BOL is to acquaint you with the Fetch API, the FormData API and ES6 features (not just backtick). These are powerful features to use with QuickBase in their own right as you can do some amazing things in a short amount of code. 5) But the real reason is to slowly introduce you to Service Workers which will blow away all these other methods of introducing JavaScript into your QuickBase pages and provide amazing new features you never though possible in a browser (like offline QuickBase - nope I am not joking). In the next few days (maybe tomorrow - it depends on if I get my grass cut today) we will launch the Service Worker Travel Log which will feature a new aspect of using Service Workers with QuickBase. So clear you head, pack your bags and bring lots of cash as we embark on a 30 day adventure of using Service Workers with QuickBase.
summary report formulas
As a Data Analyst at Quickbase, I'm often asked to create reports and scorecards to track our performance over time. In most cases, it's easy to provide this data with the summary report feature – it can be done simply by adding the numeric fields to the report, choosing how to aggregate them (sum, average, etc.) and grouping by the appropriate date fields. But what can you do when you're asked to perform additional calculations on those aggregated metrics? Some people refer to this as the averages of averages problem, even though it is much more! There are plenty of these scenarios in every department: lead conversion in marketing, quota attainment in sales, net retention in finance, just to name a few… Also, different business units often have their own targets, and they need reports to inform how they are executing against them on a monthly, quarterly and annual basis. Since this was not possible on summary reports, my usual workaround was creating new tables, adding summary fields and doing the final calculations there. I'm very excited that now our product has a new feature called summary report formulas, which makes those calculations much easier than before, and will save me a lot of time and effort! Now, all I need to do is create a summary report on my source table, choose how to group my data and follow 3 simple steps: 1. Define summary variables – Simple example – Calculating win / loss ratio in Sales. Values to define: Total # of won deals Total # of lost deals. Complex example: Calculating utilization ratio against target for a customer support center. Values to define: Duration of direct customer support (hours) Duration of additional customer support tasks (hours) # of vacation hours # of sick leave hours 2. Write summary report formula – Formula to calculate win / loss ratio: Total # of won deals/ (Total # of won deals + Total # of lost deals) Formula to calculate utilization ratio against target (assuming that the report is grouped by week and by rep, each support rep works 40h a week and is expected to have a utilization of 60%): (Duration of direct customer support (hours) + Duration of additional customer support tasks (hours)) / (40 - # of vacation hours - # of sick leave hours) / 0.6 3. Add my new field(s) to the report. This way, instead of creating separate tables with calculations for each time period, I can build all of my reports with percentage calculations on the same table. Stakeholders won't have to wait for me to create a few tables, join them, add summary fields, and then write the formulas to get the results. Thanks to summary report formulas, I will be able to deliver data to the business in minutes, rather than days and hours! This feature is currently in beta. You can express interest in our beta registration application. We are inviting people into the program in waves but if you believe you have a use case that may enable you to provide valuable feedback on this feature, please reach out to your account team. ------------------------------ Aleksandra Cybulska ------------------------------
Service Worker
I am working on implementing changes with Service Workers. I am following the series of posts from Dan Diebolt, Service Worker Travel Logs, and it is making sense. The one part I am stuck on is in the last one; https://community.quickbase.com/quickbase/topics/service-worker-travel-log-day-13, specifically implementing the cheerio.js library. Do I need to bring over every file in the GitHub page, or is there a way to simply reference them all at once?Creating a Survey to Collect Data from Non-users
At the end of each course in Quick Base University, our training team uses a Quick Base app that I created to collect feedback from our learners. Some of our leaners are users in our app and some are not. We want to collect feedback from everyone whether they are users in our realm, our app, or non-users (people who do not have permission on our realm or in our app). To do this, I created a form and a role for the group ÒEveryone on the InternetÓ (EOTI). The team then creates a custom URL and embeds it on the web page in the last lesson of each course. 1. Create a survey form with a submit button You need a table to house survey questions. I create different tables for each type of survey because it makes reporting easier. I suggest using short field names. For example, if the question is, ÒHow would you rate your overall experience on Quick Base University?Ó, a good field name might be ÒOverall ExperienceÓ. Then when you create the form, you can add the full text as an alternate label. Be sure to add a Submit button. Create it using a Formula-Rich Text field type. The formula for the button is: "<a class='Vibrant Success' onclick='DoSaveAdd()' href='#'>Submit</a>" You can edit the default form or create a second form. I use 2 separate forms, a form for my teamÕs role and a form for my EOTI view, because I have fields that our team uses to add comments and assign feedback for team members to address. 2. Create a redirect HTML page I created a simple thank you page. You can use code pages to create an html page. You will need this page to load after a user submits feedback. You may want to include some text or images on this page to notify the user that the survey has been submitted. 3. Create a role with permissions to see only the necessary fields I create an EOTI role. I do this so that whenever I see the role I know that it is open to everyone and I need to be cautious about the permissions that I set. I usually set the role permissions on a survey table to only be able to add a record. I add custom rules for fields, so users assigned this role can see and modify only the fields on the form. You can create an EOTI role and assign it to the ÒEveryone on the InternetÓ group. This will allow non-users to respond to your survey. 4. Invite the ÒEveryone on the InternetÓ group to your app To allow non-users to access the survey, I shared the app with the ÒEveryone on the InternetÓ group. When you are sharing with a new user, if you type Everyone, the group will populate for you. Set the role for this group to the EOTI role that you just created. 5. Create the URL The team creates a unique URL for each course that they develop. You can edit the URL and then either add it as a link or use an iframe to embed it on another page. Copy the form URL from your browser. It will look something like this: https://team.quickbase.com/db/bneukue8v?a=nwr You can hide the Quick Base interface by adding &ifv=1 to the end of your URL. It will look something like: https://team.quickbase.com/db/bneukue8v?a=nwr&ifv=1 Our team wants to know which course the feedback came from, so we create a slightly different link for each course. We set the Course Number field in the URL. You can use &_fid= to set the text in the field. The Course Number field is field number 22 in our app. We add the course number after the =. In this example the course number is ZZZZ. https://team.quickbase.com/db/bneukue8v?a=nwr&ifv=1&_fid_22=ZZZZ Our team wants our end users to know that their feedback has been received, so we use a redirect page which loads after the survey has been submitted. You can use &nextURL= to redirect the iframe or page. You can find the URL for the page by going to the Pages page in your app, previewing the page, and copying the URL from your browser. The URL will look something like this: https://team.quickbase.com/db/bneuj8f7m?a=dbpage&pageID=2 You need to encode anything in the URL that is not a number or letter. HereÕs the encoding you will need: Your final URL will look something like this: 6. Link or embed the URL On our team, we embed the survey using an iframe. You can embed the link with an iframe or use the URL to create a link on a webpage or in an email. If you are embedding the form into another web page, wrap the URL in an iframe tag, like this: <iframe src="https://team.quickbase.com/db/bneukue8v?a=nwr&ifv=1&_fid_22=ZZZZ&nextURL=https%3A%2F%2Fteam.quickbase.com%2Fdb%2Fbneuj8f7m%3Fa%3Ddbpage%26pageID%3D2"></iframe> You are now on your way to gathering feedback using a Quick Base app. What do you want to learn next? Go to Quick Base University or leave a comment below.Task Templating with Automations in Quick Base-Job Aid
Project task templating with automations Do you find yourself typing the same tasks over and over for your projects? Instead of entering these tasks each time that you create a project, you can create an automation to automatically add the tasks when you choose the project type. We will use 4 tables, 3 relationships, and 1 automation to automate task entry. Let’s see how to set this up. Step 1: Create the table and fields The templating process for our example is based on having these tables in place: Project Type: holds the project types that will be related to the sets of tasks on the Task Template table and to the projects on the Projects table. Tasks Template: holds the actual template for the tasks and the Project Types they are associated with. Projects: holds project data and is where the automation will be triggered. Tasks: holds the tasks that the automation copies from the Tasks Template This table needs to have the same fields and field types as the fields on the Tasks Template table. Step 2: Create the relationships Next create the relationships between your tables as per the relationship diagram. One Project Type has many Task Templates. This ensures that the proper set of tasks are copied over when the Project Type is assigned One Project Type has many Projects. This ensures that the proper template is assigned to the project based on the Project Type assigned to it. One Project has many Tasks. This ensures that when the automation copies over the tasks, they are associated with the proper project. Step 3: Create your automation The automation copies the data from the Tasks Template table into the Tasks table and associates it by the related Project. Create the automation to trigger when data changes. Click When data changes to set the trigger Enter a name for your automation: Template Automation Enter a comment for your automation that will help you or another builder understand the automation in the future Configure the trigger to when a user modifies a record in the Projects table Set the additional criteria to start the automation when Any of the following fields change and select Project Type Create the action to copy records to the Tasks table. Click Add an action Click Copy records Click Next – will say Finish if you don’t have any relationships Choose the Tasks Template table as your source Choose the Tasks table as your destination Configure the action to add data into fields in a new record. Open the Filter Records section and set the condition to Project Type to Trigger: Projects: Project Type For Field, select the field that you want to copy from the Tasks Template table. For Source, select Copy value from another record For Value, select Source: Tasks Template and the correct field Click the + icon to add new rows for additional fields and repeat steps 2-4 Add the “Related Project” to create the relationship with the project: for Field, select Related Project For Source, select Copy value from another record For Value, select Trigger: Projects: Record ID# Click Save & close Great job! You have created a task template with an automation! Looking for more on how you could be using Automations in your app? Audit Logging with Automations About Quick Base Automations (Help) Working with Automations (University Lesson) Complex Workflow and Automations (University Lesson)
