JamesTravaglini
Quickbase Staff
Joined 6 years ago
User Profile
User Widgets
Contributions
Send SMS Text Messages with Pipelines and Twilio
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!How to Use the Bucket Channel to Schedule an Archive to Box
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 PipelinesRemoving the Confusion from Relationships, References, Proxies and more
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! Automating Tasks with Pipelines!
Automating Tasks Using Pipelines! Now that you have your shiny new toy in the way of our latest feature, pipelines, you will want to learn ways you can begin to take advantage of this amazing new feature. In this post you will learn how to automate your tasks quickly, efficiently and most important easily through pipelines. In this example I'll be using a simple Project Management application for the example use case. In this example we will need 3 tables; a Projects table, a Tasks table, and a Task Template table. I will also have a single relationship where Projects have many Tasks. Below is an example of my relationship diagram I will be working with. For this example, I will keep the fields as simple as possible, so we are only looking at the information we will need for this pipeline. The Projects table will consist of two fields: Project Name and Project Type. The Tasks table will have a Task Name, Status and a Sort field. The Task Template table will have a Task Name, Project Type and a Sort field. The Project Type field is the most important field in this scenario as this is how we will query our Template Tasks table to locate and add/relate the correct records to our Project upon triggering. The Sort field is also important as this allows us to sort the Tasks in the order they should be completed for this particular Project type. Step 1: Populate the Task Template table with the tasks you need for each variation of your Project Types. Below is the example I'll be working with. Step 2: Assign a user token to your application Click on your name in the upper right > click, My Preferences > then Manage user tokens. From here create a new user token and assign it to your application. Or if you have already created a user token you can assign your app to an existing token. Remember to click save when you are done. Step 3: Connecting to the Quick Base Channel Click on the Pipelines tab in the upper left of your screen. Then click "Create a pipeline". Click on the Quick Base channel and click "Connect to Quick Base". Now a screen will appear and you will be prompted to enter your company subdomain on Quick Base (Realm) and your user token. This is the first portion of your Quick Base URL. For example, if your URL is Hooli.quickbase.com. The subdomain will be Hooli. Once ready click, Connect to Quick Base. Step 4: Creating your trigger! Now that we have established a connection for the Quick Base channel we can get to the fun stuff! In this scenario I'll be using the Add Record trigger. Start by locating the Add record trigger and click and drag it into the first position in your pipeline. Once in place, select the table you want to use as the trigger. In this case we will be using our Projects table. Next specify the fields needed for subsequent steps in your pipeline. For this example we will need the Project Type field. Below is an example of what your trigger should look like. Step 5: Creating your Query Click and drag the "Search records" step just below our trigger. Select the Template Task table from the table dropdown. In the fields section, select Order, Project Type and Task Name. Next click the "Add conditions" button and select the Project Type field. Update your operator to "is", then locate the Project Type field from your previous step and drag it into the text box. Your query should now read "Project type is {{a.project_type}}". Below is an example of what this looks like. Step 6: Adding your Create Record Action Locate the "Create record action" and drag it in place below our Search record query. Select the Tasks table from the table dropdown. Under "Select fields to Specify values" select; Task Name, Status, Order and Related Project(this is important so we can properly relate our new records to the Project record we just added). Next use the steps above to populate each of the fields we selected. For Status, I will select the value "Not Started". NOTE: my status field is a multiple choice field which allows me to select this as it is a choice for the field. For Related Project, expand the Projects step and drag the Record ID# in place. For Order, expand the Search records step and drag the Order field into place. For Task Name, expand the Search records step and drag the Task Name into place. Below is an example of creating this step. Our pipeline is now complete! Flip the toggle in the upper right and turn your pipeline on! Go back to your application and let's trigger our pipeline! ------------------------------ James Travaglini ------------------------------Automating Tasks with Pipelines!
Automating Tasks Using Pipelines! Now that you have your shiny new toy in the way of our latest feature, pipelines, you will want to learn ways you can begin to take advantage of this amazing new feature. In this post you will learn how to automate your tasks quickly, efficiently and most important easily through pipelines. In this example I'll be using a simple Project Management application for the example use case. In this example we will need 3 tables; a Projects table, a Tasks table, and a Task Template table. I will also have a single relationship where Projects have many Tasks. Below is an example of my relationship diagram I will be working with. For this example, I will keep the fields as simple as possible, so we are only looking at the information we will need for this pipeline. The Projects table will consist of two fields: Project Name and Project Type. The Tasks table will have a Task Name, Status and a Sort field. The Task Template table will have a Task Name, Project Type and a Sort field. The Project Type field is the most important field in this scenario as this is how we will query our Template Tasks table to locate and add/relate the correct records to our Project upon triggering. The Sort field is also important as this allows us to sort the Tasks in the order they should be completed for this particular Project type. Step 1: Populate the Task Template table with the tasks you need for each variation of your Project Types. Below is the example I'll be working with. Step 2: Assign a user token to your application Click on your name in the upper right > click, My Preferences > then Manage user tokens. From here create a new user token and assign it to your application. Or if you have already created a user token you can assign your app to an existing token. Remember to click save when you are done. Step 3: Connecting to the Quick Base Channel Click on the Pipelines tab in the upper left of your screen. Then click "Create a pipeline". Click on the Quick Base channel and click "Connect to Quick Base". Now a screen will appear and you will be prompted to enter your company subdomain on Quick Base (Realm) and your user token. This is the first portion of your Quick Base URL. For example, if your URL is Hooli.quickbase.com. The subdomain will be Hooli. Once ready click, Connect to Quick Base. Step 4: Creating your trigger! Now that we have established a connection for the Quick Base channel we can get to the fun stuff! In this scenario I'll be using the Add Record trigger. Start by locating the Add record trigger and click and drag it into the first position in your pipeline. Once in place, select the table you want to use as the trigger. In this case we will be using our Projects table. Next specify the fields needed for subsequent steps in your pipeline. For this example we will need the Project Type field. Below is an example of what your trigger should look like. Step 5: Creating your Query Click and drag the "Search records" step just below our trigger. Select the Template Task table from the table dropdown. In the fields section, select Order, Project Type and Task Name. Next click the "Add conditions" button and select the Project Type field. Update your operator to "is", then locate the Project Type field from your previous step and drag it into the text box. Your query should now read "Project type is {{a.project_type}}". Below is an example of what this looks like. Step 6: Adding your Create Record Action Locate the "Create record action" and drag it in place below our Search record query. Select the Tasks table from the table dropdown. Under "Select fields to Specify values" select; Task Name, Status, Order and Related Project(this is important so we can properly relate our new records to the Project record we just added). Next use the steps above to populate each of the fields we selected. For Status, I will select the value "Not Started". NOTE: my status field is a multiple choice field which allows me to select this as it is a choice for the field. For Related Project, expand the Projects step and drag the Record ID# in place. For Order, expand the Search records step and drag the Order field into place. For Task Name, expand the Search records step and drag the Task Name into place. Below is an example of creating this step. Our pipeline is now complete! Flip the toggle in the upper right and turn your pipeline on! Go back to your application and let's trigger our pipeline! ------------------------------ James Travaglini ------------------------------
