You've turned to Quickbase to help you track a process or to store important information. Whether you're creating an app from scratch or plan to expand upon an app from Quickbase Exchange, you'll need to examine your process and the information involved so you can structure your app in the most efficient way. The structure of your app affects not only how data is stored, but also the forms through which users enter data and the data available in reports.
The same app can be designed in many different ways. You might design an app one way and your colleague might take a slightly different approach. Both designs may work well. Get your staff members to test your design, then get their feedback. Is it easy to use? Does it help them do their job? Do your reports display the expected information? Based on their reviews, you can make changes. The best part about working with Quickbase is that it's flexible. Try an approach you think works well. If you find it doesn't suit your process, you can change your app structure at any time.
Follow these steps to design a Quickbase app.
Step one: Define what information you want to track - at its highest level
Identify general categories of information you need to track. These categories might be things like customer names, companies they belong to, and products you sell. What are the largest chunks of information you need to store and follow? Is it projects and related tasks? Or sales people and their activities? Usually these general categories of information will have additional data that comprise or describe them. For example, a product may have a name and a price. You'll get to these details later. For now, decide what the largest categories of things are.
These general categories will become the tables that make up your Quickbase app. Learn more about tables.
Note: You should give your table a pluralized name, such as projects, tasks, and products, because the table will contain multiple individual records. For example, the Tasks table contains many task records.
How do you know what information belongs in its own table? Some guidelines:
Will you be entering certain values again and again? If so, those values belong in their own table.
Values that change frequently don't belong in the same table as values that never change. For instance, you may place a new order of books every day, but the information on the vendors (name, location, telephone) from whom you purchase these books rarely, if ever, changes.
Tip: Think in the most general terms about what tables you need. For example, say your goal is to track invoices by month. So, specific months should not be tables, in fact, you should never use a table to represent time periods. Date information is always tracked within a date field. Instead, you'd create one table called invoices. Then you could generate reports organized by month or create one large report grouped by month or by quarter based on invoice date.
Step two: Itemize details within each category
Next, identify the components, the individual pieces of information you want to track for each category. In other words, what fields do you want to appear on each data-entry form that your users fill out, and what data do you want to appear on your reports?
Tip: As you contemplate what pieces of information you need, keep in mind all the ways you'll want to use this information. For example, say you want to track customer name. Will this be one field? Should users enter the first and last name together? Or should they be separated in case you want to sort records by last name or extract first names to generate personalized letter salutations? Usually, it's best to create two fields: first name and last name.
For example, if you were to create an invoice, you'd need to record a date and an invoice number. It's your job to create a place for these individual pieces of information to live. In Quickbase, that place is called a field. A field is like a column on a spreadsheet. Learn more about fields. So, to store this information, you'd create two fields within the Invoices table: Invoice Data and Invoice Number.
You also need to track customer name and address, because someone needs to pay this invoice and understand what item they're purchasing. This information definitely belongs on the invoice too, however, these additional details probably exist in one or more of the other "general information categories" (tables) that you defined in step one. So, customer info belongs in its own table, but it also belongs on an invoice. What this tells you is that information in different tables is related. Customers buy products that are billed via invoices.
Step three: Identify relationships between tables
Are the general categories you defined in step one related to each other? If so, how? Do they have a one-to-one relationship, a one-to-many relationship or a many-to-many relationship? For example:
One-to-one: One employee may be assigned one workstation.
One-to-many: One customer may buy many products.
Many-to-many: Many students may take many classes.
To illustrate the process outlined in Steps 1-3, imagine that you run an art gallery.
Say you examine your art gallery business and determine that your Quickbase app needs to track the following general items: works, artists, customers and invoices.
Next, outline the smaller pieces of information that relate to each general category:
Artists Works Customers Invoices First Name Title First Name Invoice # Last Name Price Last Name Date Tax ID# Media Address
Note: Invoices require much more information, like customer name and purchased art. All those values already exist in other tables. The power of Quickbase is that these details live in only one place, but can be displayed across tables. In other words, you'll make customer and art values appear on an invoice as a result of the relationships you're about to define.
Finally, figure out if and how each category (table) relates to others. In this case:
One artist creates many works
One invoice can list many works
One customer can have many invoices.
Tip: When creating an app, Quickbase includes the Visual Builder tool that lets you create a data model diagram at the same time you’re figuring out your tables and how they relate.
When you create a relationship between tables, you designate a field with which to link them. This field is called a reference field. Reference is not a field type, but an attribute of a field. For example, when you create a relationship between invoices and customers, the reference field in the invoices table lets you select which customer gets the Invoice. If you want to display additional details about the customer, like address, use a lookup field in the invoices table do so. Learn more about relationships.
Step four: Identify your form and report needs
Now that you have identified the basic structure of the app, consider what data you want to see in your forms and your reports.
When you link tables together in a one-to-many relationship, the “one” table is called the parent table, and the "many" table is called the child table. For example, Customers is a parent table and Invoices is a child table. You can set up your app so that forms and reports in your Customers table include data from related Invoice records, and vice versa.
You can include lookup fields in the Invoices details table to pull in information from the related Customer parent records, like the customer's name and address. You can include summary fields in the Customers parent table to perform calculations on data in related Invoice detail records. You can even embed a report of Invoices records within the Customers form.
Read more about creating forms, and read more about common reporting needs and creating reports.