ContributionsMost RecentMost LikesSolutionsInventory management with a constraint Basic Inventory Management Whenever you are faced with a workflow or business problem, QuickBase provides you with building blocks that you can use to solve your problem. In this real-world example, we will look at how you can solve an inventory management problem using a combination of summary and formula fields to calculate inventory transactions and metrics. Problem: A customer wants to track what items - we'll call them widgets - were taken from available inventory over the course of each month. The problem is that they only keep track of what was added to inventory each month, along with the total amount on hand at the beginning of each month. They need to know what amount is available at any given time. Known - "adds" and "total available at month end" Unknown - "takes" On the App Exchange: ABC Inventory Mgt Please visit the Exchange and download the application ABC Inventory Mgt to your realm for reference. Solution: Using two tables - a parent table for each period, in this case each month, and a child table for the transactions that occur as people add, and "take inventory" or count what amount is available at the end of each month, we will be able to back into the calculation and determine what amount of inventory was taken, or used, during the month. Seeing that QuickBase tables do not allow you to calculate a running, or accumulating, balance record by record, we need to use a parent table in order to use summary fields to calculate the relevant dates and values for each of our transactions. When you have the relationship created, then you can define a parent record for a given period, in this case let's use "Years" for each period. We can use any period we want, but for simplicity's sake, let's say that transactions can occur throughout the course of the year. Since we know that there will be Count transactions, let's first identify the "Last Count Date" so that we can determine the last known total amount on hand for the available inventory. We can do this by creating a summary field using the operation function of "Maximum" to see the date of greatest, or most recent, value. We also want to make sure we use the condition where the Adjustment Type is equal to the value of "Count" so we are only evaluating the Count transactions. To complete this step, we also need to create a Lookup field to lookup to the date value we just identified. Let's call this "Inventory - Last Count Date." We will use this in our next summary field to determine the last count amount. Let's now create the next summary field for getting the Last Count Total Amount. Be sure to use the function of Total, and you will also need to set the condition so that your date field is equal to the "Inventory - Last Count Date." The next step is to determine how much has been added to inventory since our last count date. We do this by creating a summary field "Total Added Since last Count" where the function is Total, and the field to summarize is Amount, and the condition Date field is equal to the "Inventory - Last Count Date." This field will provide us with a total for all the "Adds" to inventory in this period. The final step is to create a formula field called "Total On Hand" that evaluates the summary fields we created earlier. The equation is as follows: [Last Count Total Amount] + [Total Added Since Last Count] This amount will help us determine how much was taken from inventory seeing that we haven't been tracking those transactions. Using the same logic as described above, you can create further summary and formula fields to "back into" the "Takes" from inventory during various periods. As an exercise, I would highly encourage you to try to recreate this application on your own, from scratch. This will help you develop your QuickBase building skills, thinking through the problem as well as coming up with the right mix of summary and formula fields. ------------------------------ Sean Padian ------------------------------ Blueprint - Building for Success Blueprint - Building for Success Data Modeling Building the Basic Framework of Your App Cleaning Up Your Forms Creating Reports Data Modeling Please see my earlier post for a general understanding of data modeling. https://community.quickbase.com/communities/community-home/digestviewer/viewthread?GroupId=43&MessageKey=b1bca734-1c3f-4491-8a5d-20deaac39fff&CommunityKey=157a0aac-5622-4134-8b0a-ae35c9b68e53&tab=digestviewer As you begin to think about your application and how it should be constructed, list out the terms that pop into your mind when trying to articulate your vision. If you're thinking about building a project management application, terms like projects, tasks, project managers, start and end dates easily come to mind. Brainstorm for a while and simply list out every term that you can think of – at times you may be struggling to think of them, but keep listing them out, even over the course of several days as you daydream about your app. Over time this process will become easier and more fluid, but for now try to imagine everything you could possible use the app for in terms of your use case. If it's a CRM application, then clients or customers or accounts, how they found you (a.k.a. a "lead source"), or even main contact will suffice for now. With these terms in hand, start to group them in an organized way, so that you can see the flow of the application as these data metrics are collected by you and your staff. When you start to see the most important groups or terms, think of them as what will become your tables in your app. After gathering your tables, go through them one by one and think of all of the fields that you need to keep track of. List EVERYTHING, no matter. How trivial they may seem. You can always pare these back and reduce the list later. Building the Basic Structure of Your App With your tables and fields listed out, come back to QuickBase and begin building the basic structure of your app using either the Visual Builder or by simply creating an empty app with no tables from within Visual Builder. The idea is that you want to create each of the tables you listed in step one, followed by creating each of the fields you have listed for each separate table. Don't worry about order right now, just create the fields and make sure to save your work. Once you have the tables and fields created, then you should start to think of the relationships that need to exist between the tables in order to share linkage. In the popular use case of project management, it's easy to see that projects and tasks need to be related. When you create the relationship, the system will prompt you to select the direction of the relationship. In this example, each project can have many tasks, so the direction is easy to follow. It helps to speak these choices out loud (as silly as it may sound, it truly has been known to help) in order to "see" which one is correct. You can always go back and delete an incorrect relationship once it's created, so don't get too worried about making a mistake here. The important point is to make a selection and then go into a form view to see what's been added to your application. When you build the relationship, QuickBase will automatically create a section heading, a button to add child records to your child table, and an embedded report link to your form so that you can then begin adding sample data. Cleaning Up Your Forms As you start to add relationships and sample data to your app, you may notice that your forms look like they need a little cleaning up, or reorganizing. What you should do is take some time to visualize how your data should look to an end user of your app – what does someone need to see in order to accomplish a tasks or to simply do their job? You click on the customize your form link in the upper right hand corner of the form view and now you will be able to move form elements up or down, in addition to putting elements in the same row. It is suggested to play around with the look and feel of your forms in order to get them to your liking. Creating Reports The last stage of getting your up and running is creating reports. There are several report types to choose from, but I would suggest you start with the table reports first in order to get familiar with how QuickBase lets you configure your data and how it's displayed. Table reports are a great way to learn how to display data, filter out data, sort and group, as well as colorize rows or areas based upon a formula. In application exchange, see the app titled "Charts are Fun" for an excellent tour of the various graphic reports we offer and how they can display different sets of data. As create your reports, begin to arrange them on your dashboard so that you can see your data as soon as you log into your application. You will most likely be creating many reports for all of your dashboards, as each role in your application will be assigned to a specific dashboard page. As always, have fun as you go through this process, and as your application grows, begin to think about the roles and their permissions that you will soon need to define. Governance is an important part of your application's rollout. ------------------------------ Sean Padian ------------------------------ Using QuickBase for Surveys Surveys in QuickBase Part of the charm of QuickBase is that the platform can be used for many different purposes – there are all kinds of use cases already at work on the platform, but also every day new ones are being thought of to be implemented. One of the more popular ones is conducting surveys. Customers will need an easy way to gather survey-related data from either their customers or partners or others, and will need to report on this information to see how they are doing. Setting up an application to do this is relatively straightforward and reports can be easily created to display insights either in tabular or graphic format. The first thing you will need to do is create a table for storing the results. This is the easiest part. You may want to link this table to a customers table or elsewhere, but for now, let's keep the table on it's own. Next, grab your survey and take a look at the questions that make up your survey. Some will be questions that require a user to place a rating as a response, while others may be to select from a multiple choice type field. List out your questions in a document or spreadsheet, and then identify what types of fields they will be. Some will be ratings, others multiple-choice, while others may be open area text answers that will require some extra space. You'll probably want to save the date these survey results were added too. What may or may not be needed is the identification of the respondent. If it's possible that the responder will be a QuickBase user, then a user field will be needed. After listing out these questions and their field types, start by creating the fields in the table you have started. Don't worry about the order you enter them in, as you can reorder these on the form in a minute. Once you have the fields created, click on the "New ___" green button so that you can view your form, this is where the survey will come to life in a few minutes. Using the document where the questions and fields are listed, go through and right-click on each field in order to access the form's properties – you want to customize the form here, the fourth option in the menu list ("Edit the properties of this form element"). For each form element, you will simply click on the "Use Alternate Label Text" checkbox and type in what your survey question is for that field, where the answer or response, will go. Take time to get used to this editing process, and reorder your survey questions appropriately. You may want to also create formula fields that summarize respondent replies, so try to name your fields in a manner that makes sense to you, the administrator. You'll use these field names in formulas and reports, so it helps to keep them simple and straightforward. For further information on surveys, please take a look in our application exchange for several excellent examples! ------------------------------ Sean Padian ------------------------------ HIPAA Compliance and QuickBase... Building HIPAA Compliant Applications with QuickBase What is HIPAA? HIPAA Compliance Overview How to Ensure HIPAA compliance when using Quick Base What is HIPAA? HIPAA (Health Insurance Portability and Accountability Act of 1996) is United States legislation that provides data privacy and security provisions for safeguarding electronic Protected Health Information (ePHI). HIPAA was created primarily to modernize the flow of healthcare information, stipulate how ePHImaintained by healthcare industries should be protected from fraud and theft, and address limitations on healthcare insurance coverage. HIPAA Terminology Covered Entity -A covered entity is a health care provider, a health plan or a health care clearing house who, in its normal activities, creates, maintains or transmits PHI. Business Associate -A "business associate" is a person or business that provides a service to – or performs a certain function or activity for – a covered entity when that service, function or activity involves the business associate having access to PHI maintained by the covered entity. The Customer is the Covered Entity andQuick Base is the Business Associate. HIPAA Compliance Overview The HIPAA Privacy Rule addresses how PHI canbe used and disclosed, while the Security Rule mandatesAdministrative,Physical,andTechnicalSafeguards. HIPAA Security Rule Requirements Administrative Safeguards Physical Safeguards Access Controls Administrative Safeguards Access Controls: Access management - employees only see the "minimum necessary" information to do theirjob Authentication of the identity or individual seeking access. Session controls(inactivity logoff) Data encryption (in motion and at rest) Audit controls: Mechanisms for recording and examining activities pertaining to ePHI within the information systems. Password Management: Procedures for creating, changing, and safeguarding passwords Data Backup Plan: Establish & implement procedures to create and maintain retrievable exact copies of electronic protected health information Disaster Recovery Plan: Establish & implement procedures to restore and loss of data Emergency Mode Operation Plan: Business continuity and disaster recovery plans are developed and approved annually Physical Safeguards Facility Security Plan: Implement policies & procedures to safeguard the facility and equipment from unauthorized physical access, tampering, and theft Data Backup & Storage: Automatic full backups of customer production data each day Replicates production data to backup data center every 4 hours We enable our customers to build HIPAA compliant applications on our platform. By leveraging this shared responsibility model, together we share the administration responsibilities and setting controls where appropriate. Further Information This year's audit includes a HIPAA Attestation which validates Quick Base is a HIPAA compliant platform which customers may build HIPAA-compliant apps on. This report can be sent to customers under contract or prospects under NDA. Click here for our marketing website for Security & Compliance Info ------------------------------ Sean Padian ------------------------------ Data Modeling Data Modeling So, what exactly is data modeling? Well, it's simply a way to structure and organize your data. It provides you with a structural foundation in graphical form that you can use as you build out your application correctly - the first time. Data modeling provides the structural foundation - in graphical form - of what you're going to build in QuickBase. There are three (3) types of data models: Conceptual Logical Physical Conceptual models aim to provide context as to the business understanding of data, not a technical one. These are used to facilitate a discussion among businesspeople about their systems, processes, and organizations. Logical data models describe entities and attributes and the relationships that bind them. While the physical models then implement the logical model as tables, fields, field types and relationships. We are going to focus on the physical model, as it relates to QuickBase, implementing the logical model as tables, fields, field types and relationships in a QuickBase application. The #1 value is identifying which entities are your tables, and how are they related. This tremendously helps builders who are new to our platform, when building their first apps. Let's get started… So, where do we start? How do we make sense of our data? The first step is to write down, to articulate, what it is that you are attempting to do. Using plain language, simply state what the business problem is that you are trying to solve, and what a desired solution may involve. There is no need to get overly detailed at this point in the process, but you will want to make sure that you clearly state what it is that you are setting out to do. Then, begin by simply observing the language that was used to describe the business problem we are attempting to solve. By looking for the nouns – the people, places or things – that used. By observing the words used in your description, you will begin to see patterns or trends in the language, and these terms will serve as common denominators, as categories or buckets and they may become your tables in QuickBase. All of this do this already every day – on our computers we all have created file folders to structure and organize our data so that we may search and retrieve what we're looking for quickly, and efficiently. This exercise is going to help us identify the tables we will use in QuickBase. After categorizing all of your data into their appropriate groups, let's now turn our attention to relating these groups to one another. This is sometimes referred to as determining the cardinality in your data model. This is a practice in identifying the table-to-table relationships that help structure your data, commonly the "1-to-Many" we articulate when building a new relationship in QuickBase. You will want to simply say out loud what the possible relationships are, because as you listen to yourself state these out loud, it will be clear to you which 'direction' each relationship should be. Many folks are eager to get started and jump right into QuickBase and begin building, without taking the time to plan their application. This will inevitably lead to some mistakes, and the applications can quickly grow out of control as more functionality is added and the app evolves over time. This can make applications difficult to manage as they become more complex. Taking the time to plan out your application will help guide you as the needs of the system grow and more tables are included in the solution. Creating an effective data model not only benefits you in building new apps from scratch, but also is invaluable in reverse engineering an application you've inherited. Take the time to create an effective data model, it will pay dividends in the future! ------------------------------ Sean Padian ------------------------------ Succeed in Your Trial Succeed in your Trial Congratulations! You have decided to embark on your first trial experience with QuickBase. This post has been written with your success in mind, hoping to guide you to a successful completion of you trial and sign up as a customer. Trials are intended to give you the ability to put your tests to the software you hope to purchase, to see if what is advertised is truly as described, and to see if the general feel of using the software meets your expectations. In order to succeed at this, I've put together some 'best practices' to implement during your trial to help ensure you enjoy success! Set Aside Time to Work in the Product In order to learn about QuickBase, the platform, and how to build applications successfully, you will need to make sure you set aside time. This is usually the most important consideration when evaluating our platform. Identifying what your purchase timeline is, and lining it up with your teams' timeline to properly make your purchase decision takes some effort, as well as some honest reflection. You will want to be realistic about how much time this process will take, and it's one you won't want to rush. Write Down All of Your Questions You will not be able to know all of the questions that will arise during your trial up front, before you begin, but you should make an effort to identify all of your stakeholders' questions, as well as what they would like to see or learn during the trial. Once you begin to think of your desired solution, new questions may emerge that you never previously thought of. Identify Your Stakeholders Make sure to gain input from all of your key stakeholders. That is, each of the end users of the solution will need to give feedback, ask questions, and state what it is they are seeking in a new solution. Meeting with your team before you begin your trial will help get everyone 'on the same page' and steer your team towards success. Don't Just Wing It, Have a Plan The most successful QuickBase customers are those that know what it is they want to build on the platform, and as they set about on this journey, they continually reflect back to this plan to make sure they are aligned with it. By attempting to build in product with specificity and purpose lends itself to a more successful outcome. Ask Questions This may seem to be obvious, but the more questions you ask, the more detailed your vision of a solution becomes, and this will help you feel more prepared for the change that is coming. We find that the best evaluations are those which include open conversations between customer and QuickBase staff. We always try to ask "what else?" when engaging potential customers, in the hopes of uncovering other items on their wish list that we may be able to help with and provide a solution for. Software evaluations are meant to be a positive experience for all involved, and when you look to use your time effectively by following guidelines such as these, you will ensure a more positive outcome for you and your team. ------------------------------ Sean Padian ------------------------------ Re: Can I use concatenate in Quickbase?Hi Test Member, Yes, you can certainly concatenate fields in QuickBase using our formula language. In your formula, simply use the ampersand ("&") in between two fields in brackets to bring them together for display purposes. [Field 1] & [Field 2] This example has a field type of Formula-Text, and a 'Best Practice' is to use spaces to separate your two fields for a end-user better experience. [Field 1] " & " [Field 2] Hope this helps! ------------------------------ Sean Padian ------------------------------