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! 71Views0likes0CommentsQuickbase 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. ?57Views0likes5CommentsSend 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!55Views1like0CommentsGetting Started with Quickbase
Getting Started on Quickbase – A Guide Do you fit into one of the below three categories? 1) You are looking for a Dynamic Work Management tool and you're exploring Quickbase. 2) You're in the middle of a Trial with Quickbase and you want to see what the Quickbase community has to offer. 3) You're a new customer getting started on Quickbase. Yes? Fantastic. It doesn't matter how you got here, what's important is that you're here! Congratulations on getting to this stage, and welcome to The Qrew! This is a guide designed to help you navigate Qrew Discussions, our online Community forum, as well as our other resources designed to help you launch your Quickbase accounts the right way. What should you do first? Check out Quickbase University's Getting Started series. There's no better way to start Quickbase than to watch this series, where you'll get a few short and to the point videos on… 1. How to start a builder account* 2. How to download a free app from our Exchange* 3. Navigating Quickbase 4. Quickbase Concepts 5. Intro to Database Concepts 6. App Planning Basics *Highly Recommended for those getting started with Quickbase to have both 1) a builder account as a sandbox space and 2) a few templated apps from Exchange to see what a finished result could look like! What's Next? Now that you have a builder account and a couple apps off our Exchange, you should start applying some of the basics you learned from this series. But are you running into a challenge? Or do you want to know how to do something not covered in these videos? Join the Conversation! You see that big green button on your Getting Started Community homepage next to "Latest Discussions" that reads "Join the Conversation"? Click it! This is how you ask The Qrew a question. Go on, ask a Quickbase question that's top of mind? As you're just getting started with our platform, this is the perfect place to tap into this community. The Qrew is filled with subject matter experts who love answering questions in the Getting Started Qrew Discussions space. A few recommendations as you ask your first question to The Qrew… a. Give yourself a strong Discussion subject that summarizes your question. "How Easy Is It to Migrate Excel Files to Quickbase Apps?" is an example of a good subject title. "Excel" is an example of a subject title that needs more detail. b. Ask your question, add an attachment if you think it will be useful for The Qrew to see what you're working on, and hit "Post". Make sure you present your challenge clearly and do your best to be as specific as possible. Questions with more specificity are more likely to be responded to than questions that span too many topics all at once. c. Sit back and wait for responses, a member of The Qrew should post a response for you soon! Fantastic! You have asked your first Qrew Discussions question, you're well on your way to being a full fledged Qrew member. What else can you do? Find YOUR Qrew. Do you want to find others in The Qrew from your industry or who have a similar role? Maybe you're interested in surrounding yourself with the best and brightest from The Qrew and seeing the potential for this platform? Join a Qrew Group! Qrew Groups are both virtual or in-person. Some are led by Quickbase staff, some are led by Qrew Champions from the Quickbase Community. This is how you connect with others in The Qrew to grow your game and expand your professional networks. While you're on our Qrew Groups page, a few suggestions… d. Check Out the Discussion threads within each of our Qrew Groups. These exist so Qrew Groups can keep the conversation going in between meetups. e. Register for an upcoming event on the Quickbase Events page. These are free. This is the best way to see what The Qrew is all about; meetings will typically involve Qrew Members sharing how they use Quickbase and what value they've created for their organizations using this platform. Hopefully by now you have watched our Getting Started series. In doing so you created a builder account and downloaded apps from the Exchange, which is a great way to start. As you discover new questions, you can start a Qrew Discussion. Hopefully you want to meet others in the Qrew, and you have signed up for a future Qrew Group meetup. Congratulations on starting your Quickbase journey in the smartest way you can, which is to tap into the best resources The Qrew has to offer. Welcome to The Qrew. You're in. ------------------------------ Ben Simon bsimon@quickbase.com Do you have Feedback on how to make Qrew Discussions a better experience? Let's chat! https://calendly.com/bsimon-2 ------------------------------46Views1like2CommentsUsing multi select field in pipelines
Hi, I have a pipeline set up that takes a data( a name) from a look up field in one table and moves it to a multi select field on another table. However, each time the pipeline runs, the original name is replaced by the new name instead of added to the list in the multi select field. Does anyone know a way round this? I basically want to have the original AND new value in the same field. For various reasons I cant copy the data between tables through relationships, so need to do this through a pipeline. Many thanks, Laura ------------------------------ Laura Taylor-McAllister ------------------------------Solved36Views0likes3CommentsCheckbox 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 ------------------------------26Views0likes4CommentsWelcome to the Quick Base Knowledge Base
5 MIN READOur Community neighborhood expanded with the inclusion of the Quick Base Knowledge Base. The Quick Base Knowledge Base has served as a library of technical information, walkthroughs, and tips collected over time by Quick Base employees to help users build apps to solve their business problems. Previously this information lived on our Developer site as a separate resource that was retired on April 22nd, 2018. In order to help enrich our Community, the Knowledge Base has been integrated as a new category and article thread type. Not only will helpful content from our existing Knowledge Base be hosted in the Community but the Knowledge Base will be expanding with new tips and tricks. To help users identify our Knowledge Base look for articles with the Quick Base Knowledge Base branding below to help mark our Quick Base curated content: If there are helpful resources or articles you have questions about, please let us know in the comments below. To help streamline access to the Knowledge Base content here is an index of our existing articles: APIs and Custom Code Where can I find the Quick Base SDKs? Why does the API respond with a Sign In required error? Why do I get the error: Invalid Application Token? How do I have a single button perform multiple Quick Base API calls? Automated Emails How many subscriptions and reminders can I send daily Why aren't my notifications showing all the information that they should? Why are my users not receiving Quick Base Email as expected? What are the IP addresses that the Quick Base email servers use? Best Practices Sharing Data Across Quick Base Apps: Part 1 Cross App Relationships, Table to Table Imports, and Sync Sharing Data Across Quick Base Apps: Part 2 Quick Base Actions, Webhooks, Automations, and 3rd Party Integrations Billing and Service Plans From which Countries Does Quick Base Accept Credit Card Payments? My Billing is Set Up So I Am Supported to Receive a Receipt Each Time My Credit Card is Charged. Why Don't I Receive the Receipt via Email? Where Can I Find Quick Base's Terms of Service? We No Longer Have a Company Credit Card Can We Pay Via Purchase Order and Receive an Invoice For Quick Base? Where Can I find Updates On Service Issues? My Account is Credit Suspended, What do I do? How Can I Keep From Being Billed for Users Who Are No Longer Using Quick Base Billing Account Transfer - When my Admins have left the company and I need someone to take over Data Import and Export Quicker, easier imports using a Merge Field Why do I have problems in Quick Base when I cut and paste text from Microsoft Word? When I export data from QuickBase (in CSV format) to MS Excel, some data appears incorrect or incorrectly formatted. Why? How do I stop my append-only fields from being truncated when exporting to Excel How would I automatically download QuickBase tables and their attached files using Microsoft Access? Why do I get an error message when I try to upload more than 10,000 records containing a user field? How can I export and then reimport records so existing records are updated? Forms How can I use a field in a form rule without displaying it on the form A field on my form won't accept values. I type in it and save, but the field remains blank. What's wrong? Formulas & Functions __The Automatic Formula Machine How Can I Add An Approve or Reject Button to My Email Notifications? Using "&z="&Rurl() and rdr in formula URL fields Is there a way to display just the last entry in an append-only field? How do I make a formula date field called "Completion Date" that captures the date when a multiple choice field called "Status" is set to a particular value like "Complete" How can I create a formula URL button which opens a specific form for data entry Floating Point in Quick Base-Why are there discrepancies when rounding and comparing floating point numbers? How do I show only the most recent comment from an append field in my report? How do I make a formula to calculate the third Friday of the current month? How do I create an automatically increasing sequential number or alpha-numeric string? How can I discount or override my list prices when entering a customerÕs order? Once a text field reaches a certain length how can I truncate the text and add a link to allow the user to see the rest of the field's content? Relationships How can I set up a numbering system to track quotes that numbers each quote for a customer sequentially, and includes the customer ID and quote number? If I have a Projects table and a Tasks table where each Project record has many Task records how do I automatically indicate that the Project is completed when the "Status" field in all the related tasks are marked "Completed"? Gain more insight by summarizing text fields Release Notes Where Can I Find Quick Bases Previous Release Notes? Reports and Charts Why are the detail records not displaying in the master record form? What time of day are report subscriptions emailed? How do I adjust (resize) the column width in my reports? How can I find duplicate values in a specific field? How do I embed a report of the details of details? Roles and Permissions I shared an application with a user, but they can't access it. Why not? Some of my applications don't appear on my Application List. Why? How can I set Permissions for a Role to be able to view only Master records which have related Details records that are "connected" to me? Is there a way to set up a management hierarchy and use that hierarchy for permissions in an application? Securityand Single Sign-On What are Quick Base's IP addresses? System Performance What are the most common Quick Base functions that can affect performance? How can I troubleshoot Quick Base performance? How do I clear my browser cache? Quick Base Memory Calculations How we built Quick Base to scale and you can too! Tables and Fields How do I find the database ID (dbid) of a QuickBase table? Some of my entries appear in red text. What happened? User and Group Management I have more than one user name but all are associated with my (single) email address. How do I get rid of all but one user name? What do I do if I'm having trouble with my password reset? Wizards and Add-Ons What's the easiest way to create a data entry form for a Quick Base application that I can embed on my website? How can I synchronize my Quick Base app with Microsoft Project? How can I import Microsoft Project data into Quick Base? Other Tips and Tricks How can I create colored backgrounds, pictures and stoplight graphics in my forms and reports? Colorful Icons Make Quick Base More Productive Does Quick Base support Unicode or other multi-byte character encodings like Big5, GB, HZ, Shift-JIS, JIS? How do I baseline a project or snapshot a sales pipeline? How can I set up a numbering system to track quotes that numbers each quote for a customer sequentially, and includes the customer ID and quote number?25Views1like0CommentsTrying 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 ------------------------------20Views0likes4CommentsHow 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 ------------------------------