Qrew Tips - The Qrew Knowledge Base
Qrew tips and more
Recent Content
Removing the Confusion from Relationships, References, Proxies and more
8 MIN READ Removing the Confusion from Relationships, References and Proxies and more Relationships are the core functionality of Quickbase. If you are new to Quickbase or relational databases this concept may be difficult to wrap your brain around at first. However, once you have a strong grasp of how to properly set up relationships your Quickbase building will come naturally to you. This article will break down when and how to properly use a relationship and all the pieces that are involved so you can get the most out of your Quickbase applications. Before diving into the specifics, it is important to understand the concept of a relationship. A relationship is how we are able to "connect" two tables of data to each other. This allows you to create a workflow in your application, see information from one table(parent), on a record in another(child), as well as summarize important information from one table(child) and have it visible on another record(parent). In this blog we will cover the following: 1. Key fields - unique identifiers 2. Parent vs Child Tables 3. Reference Fields 3. Lookup Fields 4. Reference Proxies 5. Summary Fields Below is a diagram of an example relationship we will cover throughout this post. Our example will be a relationship where Accounts have many Contacts. Key Fields Before we tackle creating a relationship it is important to understand the concept of a key field.Key fields are unique identifiers for each table in your Quickbase application. This is what allows you to relate or "connect" data between two different tables. By default each new table you create in Quickbase uses Record ID#as the key field. Record ID# is a built in Quickbase field that is an auto incrementing number. The first record you add will have a value of 1, the next 2,3,4...etc. Quickbase also allows you to set your own key field. For example, if I had an Accounts table where each record represents a unique account, we may have a field we are already using to track each individual account e.g. an Account ID field. We could set this field as the key for our table. Moving forward when we add a new Account record the Account ID field must be filled in and when the record is saved and it must be a unique value or the record will not be able to save. Setting your own key field can come in handy down the line when we are importing records to a table that is related to our Accounts table. It can also be useful to make sure we are not adding any duplicate records when importing data from a system outside of Quickbase. Parent vs Child Tables in Relationships Now that we have an understanding of key fields we can move on to the meat of this article, Relationships. When creating a relationship in Quickbase we need to understand what our data is and how it logically needs to be structured. Let's continue with the example of an Accounts table. What type of information may we want to relate to an Account? How about our Contacts for each Account? Every relationship in Quickbase requires at least two tables be involved. In this scenario we have an Accounts table and a Contacts table. Before we move on it is important to understand the difference between a "Parent" table and a "Child" table. Each record on a Parent table is unique, while there can be many records of a Child table related to a single unique Parent record. When a relationship is created a few things happen. 1. A button is created on the Parent table to add a new Child record. 2. A report link is created, which displays a report of all related Child records, on the Parent record form. 3. A dropdown is created on the Childrecord, to select/relate the Child to its Parent. To get started let's go to the settings on your table > table to table relationships > click New Relationship. Once you get to the new relationship screen, you will be prompted to select a table to connect with like the screenshot below. In this example, I'll select my Contacts table, once I do so I will have two options. Ok, now we need to choose an option. The first option in the screenshot above is saying One Account(parent) can have many different Contacts(children). The second option is saying One Contact(parent) can have many different Accounts(children). For this scenario the first option makes the most sense. What is important to understand in this step is where you need a button to add a new record and where you need a drop down to relate or "connect" that record to another table. If we choose the option where an Account can have many Contacts, we will have a button on the Account record to add a new contact and an embedded report on the Account record displaying all the related Contacts for this account. On the Contact record you will have a dropdown to select the correct Account to relate to your Contact. This will bring us to our next topic, Reference fields and Reference Proxies. Reference Fields A reference field, is a field on the Child table that matches its value with the value of the key field of the Parent table. By default when a relationshipis created the reference field is automatically created at the same time. The way Quickbase knows if the Child record is related to the Parent is if the reference field value matches the key field value. For example, if you have a Parent record with a Record ID# of "1", the reference field on the Child will need to have a value of "1" to be related. Lookup Fields A Lookup Field is any field originating from the Parent Record, that you would like to view on the Child Record. All Lookup fields are read-only and are populated when the Child record is related to the Parent. For example in our Accounts have many Contacts relationship, we can lookup any field specific to the Account and view on the Contact record, e.g. the Account Address, Account ID etc. To create a lookup field, go to your table settings > table to table relationships, and select the relationship you wish to modify. Once you're in the relationship screen you will see the "Add Lookup Field" button on the right hand side. Note: the first lookup field I created defaulted to the reference proxy, we'll cover what this means in the next section. Reference Proxies A reference proxy is a lookup field that allows you to use another field from your Parent table as the dropdown field. Without a proxy field, when you have a dropdown on the Child record, and save it, it will display the reference field value. For example, if we're adding a contact, select the correct Account and save. Instead of displaying the Account Name it would display the key field value of the Account e.g. 1,2,3,4 etc. In many cases, it makes more sense to display another field rather than our reference field value. If you do not want the first lookup field added to be the reference proxy or you do not want a reference proxy at all, go to the field properties of your reference field(in this case "Related Account"), under "Reference Field Options", change the Proxy field to the field you would like or use "select a field.." to leave it blank. Updating the Dropdown for a Reference Proxy Another important feature of a reference field or proxy, is the ability to determine what fields and records are displayed in your dropdown. By default Quickbase uses the default record picker for your dropdowns. To locate the fields being used for your default record picker, go to your Parent table > Advanced Settings > Default Record Picker. You can also use the "Preview Record Picker" feature to see what the dropdown will look like. If you've noticed, with the default record picker we can only display 3 fields in the dropdown. If you would like more fields displayed to make sure you're selecting the correct record, you can modify your form to use a report you've created on the Parent table as well. Not only does this allow you to add more columns to the dropdown, it will also honor any filters you may have added to the report as well. For example, with our Accounts to Contacts relationship, maybe we want to filter to only include Active Accounts in the dropdown. We can create a report on the Accounts table, with a filter for Active and use this as our report for the dropdown. To update the report for your dropdown, customize your form, locate the element with the reference field/proxy, on the right hand side under the section "When used for data entry", it will say "Use the default record picker". From here you can select your report from the Parent table. Summary Fields Summary fields allow you to summarize data from your related records and display that information on the Parent record. When creating a summary field you will have two options to summarize, 1. The number of records related to the Parent 2. A summary of a specific field. Note: Both of these options will also allow you to add "Matching Criteria", think of this as a way to filter the data. For the first option it is a counting function. If you create a summary field, make no changes to the default and click create you will have the total number of related records. For our example, this would be the #of Contacts. To take this a step further, let's say we have a field on the Contacts table for Priority(High, Medium and Low). We could add a condition to the above so we count the number of high priority contacts. This would look as follows. For our second option we can summarize data from a specific field. We have the ability to Total a value, determine the Maximum value, determine the Minimum value, Average a value, find the Standard Deviation of a value, determine a distinct count of a value, and Combine the text of a value. Below I'll list out some common applications for these functions. Total - typically used to sum a value of a numeric field. For example on an Order with many line items, we could total the price of each line item to come up with an order total. Average - could be used where we have an Accounts have many Orders relationship. We could then average our Order Total field, to gain an understanding of how much the account typically orders. Standard Deviation - could be used against our Average Order summary to determine the variability of this value. Maximum - Can be used with numeric fields or date fields. For a numeric field we could figure out what the Maximum value of orders related to that account. For a Date field we can determine when the most recent order for that account was. Minimum -Can be used with numeric fields or date fields. For a numeric field we could figure out what the Minimum value of orders related to that account. For a Date field we can determine when the first order for that account was. Combine Text - This will display the values of a text field across your related records in a multi-select text view. An example of this would be summarizing the Contact name of each order related to that account. It will display each Contact name like a multi select text field. Distinct Count - Determines the distinct number for the criteria you're summarizing. For example, we could use a distinct count on the Title field for our Contacts. This would give us an idea of how many distinct positions our contacts have at their company. Now that you've had your crash course in Relationships on Quickbase, you're ready to start building! 
Formula to Show "Day of Week, XX/XX/XXXX to Day of Week, XX/XX/XXXX"
Hello, Can someone provide any insight into how to create a formula that produces text that will show [event date start] to [event date end], when [event date end] is empty. I need a field that will combine two date fields to show, for example, "Thursday, May 6, 2021 to Friday, May 7, 2021". I have the following formula in a formula - text field set, but it's showing me what I need in a simple "5/6/2021-5/7/2021" format when I need to spell it out more like above. If(not IsNull([event date end]),ToText([event date start])&"-"&ToText([event date end]),ToText([event date start])) Thank you in advance for your help! ------------------------------ Gabriella Tremoglie ------------------------------Send SMS Text Messages with Pipelines and Twilio
2 MIN READ Send SMS Text Messages with Pipelines and Twilio Twilio is a service used to automate SMS text messaging. Including Twilio within your Quickbase ecosystem can be a huge value gain, adding efficiency to internal processes, like alerting employees out in the field or providing transparency to your customers with real-time updates. In this blog, I'll be focusing on a Work Order use case where we provide the customer with real-time status updates throughout the lifecycle of the Work Order. Getting Started First things first, login to your Twilio account and make sure you have your Account SID and Authentication token ready to go in order to authenticate your Twilio channel. From your Twilio profile, click onSettings→General. The API Credentials dialog will display the following... Once you have the above information go to your pipeline, locate the Twilio channel, and copy/paste the Account SID and Auth Token accordingly. For a more detailed breakdown of authenticating to your Twilio account, click here. The Use Case As I mentioned above for my use case I'll be using the example of a Work Order. We will be sending status updates to the customer as the order is received, the technician is on his way, and when the technician has arrived. In this example, I have two tables, 1 for my Work Orders and a related child table for Status Updates. Below is a breakdown of the relationship and the fields we'll be using in the pipeline. The idea here is everytime we add a new status update it will trigger Twilio to send an SMS text message to the phone number of the contact on our Work Order with any additional details we provide. The Pipeline Step A: Quickbase record created(trigger) 1. Locate the Create record trigger and drag it into Step A. 2. Select your Status updates table from the table dropdown. 3. Specify fields needed for subsequent steps. e.g. Status, Contact Name, Contact Phone Number, Order #, Details etc. Step B: Send Message (Twilio Action) 1. Locate the Send Message Action within the Twilio channel and drag it into Step B below the Trigger in Step A. 2. Authenticate to your Twilio account and select the number you wish to use for these messages. 3. Drag the Phone Number from Step A into the corresponding "To" field. 4. In the body create your own message using a combination of your own words and the fields from Step A. e.g. Hi {{a.work_order_contact_name}}. Your status for Order#{{a.work_order_order}} has changed to {{a.status}}. {{a.details}} That's it! The pipeline is complete! Now to see the finished product! Conclusion After reading this you should now be ready to create your first pipeline utilizing the Twilio channel! It's a quick and easy way to augment your Quickbase ecosystem by gaining more efficiency and transparency throughout your many workflows! Drop a comment and let us know other ways you plan on using the Twilio channel!
Checkbox Formula to give me a 1 or a 0
Hey hey I was trying to figure out the formula so that when I check a checkbox (In another field) it will give me either a one or a zero in this formula field that I'm trying to build. I imagine it will look thing like... If([Checkbox Field]="Checked",1,0) This doesn't seem to work... error message is "the operator = can not be applied to types bool" please advise how I can set this formula field up - Thank you! ------------------------------ Jack Woods ------------------------------Get the Help You Need: A Guide to Quickbase's Technical Support Tools
4 MIN READ Navigating Quickbase is more than just building and managing applications. It’s about knowing how to ask for help, how to leverage the resources available to you, and where to find product updates to ensure smoothoperations. In this blog post, we’ll recap the many ways you can get technical support to help you navigate Quickbase. How to Open a Case with the Technical Support team For help from our support team to troubleshoot, ask a question, or check on errors, you’ll want to connect with our wonderful technical support team. Click here for instructions on how to open a support case or manage your existing cases. Technical Support Team Expectations: Use Tech Support for one-off technical help, troubleshooting, and bug reporting. Technical Support Team is available 7AM-8PM ET M-F (or 24/7 for customers with an Ultimate Service Package) Our standard SLA for first response is 24 hours (shorter SLAs for customers subscribed to Enablement Services) 30-minute max phone calls or email communications Tech Support will not build for you; we have service offerings available for this Tech Support will not coach you through developing full solutions; we have service offerings available for this Watch How to Open a Support Case How toSearch Online Help You can click the “?” in the top right corner of your application to search the online help. Use the search bar to find pertinent help files, Qrew Discussions, release notes and more. From here, you can also access the most recent release notes and get a sneak preview of what our product team is cooking up. You can also click here to access the latest version of our Online Help Library. Along the top of the page you’ll also find categories of articles that you may find useful: Building apps (Building Fundamentals, Relationships, Forms, Formulas, and more) Using apps (Add and modifying records, reports, emails, attachments, and more) Pipelines (Channels, Creating Pipelines, Scheduling Pipelines, and more) Managing accounts (Billing and Service Plans, Administration, Permissions, and more) APIs (JSON RESTful API Guide and JTTP API Reference) Resources (University, Community, Release Notes, Feedback, Managing Cases, & Terminology) Watch How to Access Online Help How to Check the Status Page Subscribe to this page to get the most updated information on Quickbase's platform status. I like to compare checking the status page to looking under the hood of a car. Is everything running smoothly? If your apps, reports, or Pipelines are moving a lot slower than usual, you can check the status page as part of your troubleshooting. This page will tell you if there’s anything atypical happening on the Quickbase platform that our product team is addressing. Be sure to bookmark the status page to get a real-time status report with one click. Watch How to Access the Status Page How to use the App Exchange The App Exchange is a source of pre-built Quickbase apps made by other users like you. It's a good way to make use of other Quickbase app builders' expertise and get a fully functional app live faster than you could if you built it from scratch. Any app you get from the App Exchange is customizable, so you can make changes to suit your specific workflow and processes. To access the App Exchange, go to the “My Apps” page in Quickbase and click “explore sample apps”. Click here to learn more about using the App Exchange. In addition to the App Exchange described above, we have a new version of the exchange in beta! It is available as a tab on the top left side of the screen next to the solutions tab. What is neat about this beta version is that you can access both apps and formulas that you can use anywhere in your apps. Click here to learn more about this beta version of the exchange. Watch How to Access the App Exchange & Beta Exchange Release Notes Stay up to date on the latest Quickbase enhancements, new features, and early access opportunities from the Quickbase Product team. Bookmark the release notes page here. This is where you can find details about what’s been rolled out and what’s coming soon! Watch How to View Release Notes Conclusion Quickbase is more than just a platform for building and managing applications. It's a world of possibilities waiting to be explored! And with Quickbase's technical support tools, you can navigate this world with ease. From opening a case with the Technical Support team to using the App Exchange, Quickbase offers a variety of resources to help you troubleshoot issues, ask questions, and get the help you need. Plus, with the latest Quickbase enhancements and early access opportunities available through the release notes, you can stay ahead of the game and keep your apps running smoothly. Leveraging these technical support tools will ensure you’ll always get the most out of the platform.
Trying to display a PDF on a form
Good Afternoon, I recently attended a webinar "Advanced Formulas," where I learned how to display an image from another table on a form. This utilizes a Table to Table Relationship, a 'file path' Formula-URL field, and a 'thumbnail' Rich Text field. It seems that everything has been set up properly, except QB is displaying a broken thumbnail instead of my PDF file. I'm wondering if there is a simple fix to this? Here is my formula: "<img height='300px'src= '" & & " '/>" Thanks, Aidon ------------------------------ Aidon Olligschlager ------------------------------How to download File attachments using API ?
I am trying to download the files from my Quickbase table using the download file API (https://developer.quickbase.com/operation/downloadFile), but I only get the data in base64 format. How do I download the file itself ? ------------------------------ Hemanth ------------------------------Create a button that displays an embedded report from a different table
Hello, I want to create a button that displays an embedded report from a different table that is related to what's on the current record. I want a button to go in the green circle and open that report that's embedded and related to that unit. Thank you! And I'm such a novice, so please be elementary with me :) ------------------------------ Renee Hansen ------------------------------
Quickbase Code page to query a table and display records
I have created a code page which should query a table and display certain records using API -GenResults table. I am going to embed this code page in forms. Based on value in each record(in field - test temp inventory query) the contents in the table changes. Below is the code page code <html> <head> <script src= ~test temp inventory query~ lang="text/javascript"> </script> <style> td.m { font-family:verdana; font-size:70%; } td.hd { font-family:verdana; font-size:70%; font-weight:bold; color:white;} </style> </head> <body> <h1>Example</h1> <table cellpadding=5 bgcolor=lightgreen> <tr> <td> <script lang="text/javascript"> qdbWrite(); </script> </td> </tr> </table> </body> </head> </html> The third line, value of src determines the query. ~test temp inventory query~ is the field where query is created as a formula text . The value in formula text looks something like this https://Domain.quickbase.com/db/iddbdbdbid?a=API_GenResultsTable&apptoken=tokn123 t=1&query={'29'.CT.'TYCU700'}OR{'29'.CT.'TYRD60'}OR{'29'.CT.'TYTG8580'}&clist=29.17.7.9.8.38.44.49&slist=17 But the contents are not getting pulled correctly. Which is best way to pass value in field to the QuickBase code page and display the link to code page as embedded in the same form. ?
