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 Child record, 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 relationship is 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! 400Views0likes0CommentsSend 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 on Settings → 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!399Views1like0CommentsFormula 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 ------------------------------399Views0likes1CommentCheckbox 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 ------------------------------299Views0likes4CommentsUsing 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 ------------------------------Solved200Views0likes3CommentsQuickbase 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. ?199Views0likes5CommentsTime and Date for If a Field was Changed?
Hi Folks, I'm not sure this is the best way to put this together, but here is my scenario. I have 1 table (Intersections) that includes fields for "Name" (Text), 5 different "Route" Checkboxes, 5 different "Route Sequence" (Numbered 1 - 50ish), "Status" (Dropdown choice). Caveats: Each Intersection "Name" can have multiple "Route" Checkboxes checked. My Questions are: I would like to capture a Date Time, if the "Status" Dropdown was changed. AND Create a report based on the last Status time. (If Row Item status was changed within the last Day, add to report). Is there any way to do that? Thank you for any assistance. Will ------------------------------ William Wallace ------------------------------199Views0likes4CommentsThree Words of Advice on Pipelines
Check out DanielBewley from Signet Health dropping three words of advice when using Pipelines. Couldn't agree more with Daniel. Tagging is the way. Come up with a naming convention that works for you and stick to it. Maybe instead of using a Pipeline, you could use a Formula Query. Like anything in Quickbase, there's more than one way to solve the problem. A well-designed formula query could accomplish everything you need without having to build a Pipeline. If email notifications are piling up within your account, that's the sign you should be using Pipelines. This allows you to set up a log for your notifications and give you more QA oversight on your Quickbase generated emails. Check out more Qrew Tips like Daniel's at the next Qrew Meet Ups, which can be found on our Events page! Help Articles and Other Resources: Formula Queries Help Intro To Pipelines Training Join The Pipelines QrewHow to Use the Bucket Channel to Schedule an Archive to Box
3 MIN READ How to Use the Bucket Channel to Schedule an Archive to Box You may be wondering what the Bucket Channel is now that you've started to utilize our newest feature, Pipelines. The Bucket channel is a built in channel that allows you to create temporary storage tables, or objects, in the pipeline and then access that data which will be available for all your future logics. In this tutorial we will utilize this method to create a temporary CSV table to archive data from Quickbase to Box on a monthly schedule. Before we start the tutorial you will first want to access pipelines and connect to the Box Channel. For step by step information on connecting to Box, click here. Now that you have connected to the Box Channel we can start creating our Pipeline! Step A: Define Your Table Using the Bucket Channel 1. Locate the Bucket Channel > Pipelines Rows > Define Table. Drag this Action to the first step of your Pipeline. 2. Determine your table Header Separator. In this example, I'm using Comma. 3. Define your header rows. NOTE: These will correspond to fields in Quickbase in later steps. For my example, I'm archiving Time Cards. I want a Date, Employee, Hours, and Related Time Card as my headers. Which should look as follows... Date,Employee,Hours,Related Time Card 4. Determine the Date and DateTime Formats. 5. Define the type of field needed for each column. In this example it is fine to leave all columns as "String". Step B: Search Your Quickbase table 1. Go to the Quickbase Channel > Records > Search Record. Drag this Step below Step A. 2. Select the Table you want to search your records for. 3. Select all the fields from the table that will be needed in upcoming steps. e.g. Date, Employee, Hours, Related Time Card. I also created a formula check box for "Previous Month" to use as a query. 4. (optional) Create a query for your records so we're only archiving the data you need. In my example, I'm saying when the "Previous Month" is "Yes"(true/checked). Step C: Add a Row for each record returned from Step B. 1. Locate the Bucket Channel > Pipelines Rows > Add a Row, drag this step under where it says "For each Record Do" for Step B. 2. Select Step A where it says "Pipeline Row". This will then populate your fields defined for each column in your spreadsheet. e.g. Date, Employee, Hours, Related Time Card. 3. Drag the appropriate fields from Step B to their Corresponding columns. Step D: Download CSV 1. Locate the Bucket Channel > Pipeline Rows > Download CSV. Drag this step below Step B. 2. Select Step A, in the area for "Pipeline Row". Step E: Upload File in Box 1. Locate the Box channel > Files > Upload File In. Drag this step below Step D. 2. Determine the Folder Path needed. In this example I will be uploading to a folder I titled "QB". Your folder path should look as follows...All Files/QB 3. Name your file. In this example I want the File name to be the date the upload was made. To do this I'm using a date/time conversion to return todays date and append with .csv. e.g. {{time.now|date_mdy}}.csv For more information on working with date and time in pipelines, click here. 4. Drag the download URL object from Step D to the URL in Step E. Final Step: Schedule your Pipeline! In the upper right corner of your screen locate where it says "Schedule pipeline" (just to the right of "Run Pipeline"). From here you can set the pipeline to run on the 1st of every Month. NOTE: the timezone is in UTC. Our pipeline is now complete! Below is what the final product should look like. When you're ready feel free to test the pipeline by clicking, "Run Pipeline". Go to Box and you will see your file uploaded! Find more Pipelines How-Tos Below: Pipelines and Object Linking How to use Pipelines to email a report at a specific time of day Importing Pipeline YAML Build better trend reports with Pipelines Generate template records with Pipelines100Views2likes0Comments