Learn how to reference tables, work with formulas, share your work, import and export data
Note: The screenshots shown here might look a bit different from what you see in your application but the workflow is the same.
If you need a refresher before starting this tutorial, the basics tutorial shows how to create a database, add, edit, and delete a table, use view, how to sort and filter, and how to save your work.
The intermediate tutorial covers the following topics:
As mentioned in the basics tutorial, a table is designed to hold data. In the intermediate tutorial, we cover how table references can enhance your tables.
In contrast to manually populating fields with choices like we do in the event management example, a table reference connects another, separate table to a field. In other terms, every single record in this separate table becomes a choice in a field. Linking tables to each other significantly expands your choices.
To begin, we create a separate table called Company. Next, we want to connect all of the data in the Company table with a new field in the Events table. Once done, you can select a record from the Company table by clicking the newly-created field.
To follow along, create the following fields:
- Company Name → Text
- Industry → Choice: Combobox → List of industries
- Event Sponsor? → Choice: Combobox → Yes or No
- Address Line 1 → Text
- Address Line 2 → Text
- City → Text
- State → Choice: Combobox → List of states, provinces, etc.
Next, create a section for a Company Representative with the following fields:
- Full Name → Text
- VIP Guest? → Choice: Switch → Yes or No
- Birthdate → Date
To download and import sample data for your new Company table:
- 2.Follow the steps in the Importing Data section below to add the Company table data to your database.
Alternatively, if you prefer to manually populate fields with data, use your own sample data or copy some of the text below for inspiration.
Left side of the table view
Right side of the table view
Include Headers to better organize your tables.
Example headers ”Company Information” and ”Company Representative:”
To add a header:
- 1.From the table view, click the Actions gear icon and then choose Edit fields…
- 2.On the right-side panel, select Add layout element.
- 3.Drag the Head field on the right to a location in the Fields panel.
- 4.Rename the field (e.g., “Company Information”) and hit <
The next step is to format the header text based on your own personal preferences. In this example, we make the text “Company Information” appear in bold lettering, but other customization options include borders, alignment, size, and background color.
To customize the header:
- 1.Select the Company Information header.
- 2.Select the Style field.
- 3.In the Font Style field, click Bold.
- 4.Click the OK button.
- 5.In the Field editor, click the OK button.
- 6.Click the Save changes button.
The new header is ready:
To add a table reference, return to the Administrator mode and edit fields in the table.
To edit fields in the table:
- 1.Navigate to the Events table.
- 2.In the top-left corner, click the Actions gear icon.
- 3.In the drop-down menu, select Edit fields…
For visual assistance, refer the animated graphic below:
To add a table reference:
- 1.In the bottom-right corner of the Edit Fields window, select Create table reference.
- 2.A list of all tables appears. The list of tables includes your new Company table.
- 3.Drag your newly-created table to the fields area, like you are adding a new field.
- 4.Rename your field or name it “Company” like in shown in the example below. Hit <
ENTER> after you type the field name.
- 5.Click the Save changes button. Your table reference is ready.
After saving your changes in the previous step, return to the Events table.
Start with the Form view by selecting the Form button at the top of the screen—this displays the form to the right of the table.
A new selection field called Company is visible.
Click anywhere in this field to go to the Company table.
Select a company record or, if your referenced table is very large, enter a search term and matching results appear automatically. For visual assistance, refer to the animated graphic below:
With your newly referenced table, you now have multiple layers of information. In this section, we learn how to ”drill down” into your data.
”Drilling down” is just a fancy way of saying, ”I want more detailed information.” In the example above, we selected ”Otis Manufacturing” as our company.
Your Company field should now list your selection (“Otis Manufacturing Inc. Agriculture…” etc.). Select the field to drill down into the record to view detailed company information. For visual assistance, refer to the animated graphic below:
Formulas enable you to automatically populate fields based on other content. For example, formulas can automatically format text, perform calculations on values, and even use logic to produce conditional information (i.e., “If this is true, then do that...” type statements).
In this tutorial, we use a formula field to automatically calculate age based on a provided birthdate.
If you replicated the Company table as shown above, one of your fields should be Birthdate (created by adding the Date field). For visual assistance, refer to the screenshot below:
Start by navigating to the Company table you created earlier. To jump between tables, select the table name in the main menu. Then select Edit fields.
To navigate to the Company table:
- 1.In the top-left corner, click the Actions gear icon.
- 2.In the settings drop-down, select Edit fields...
For visual assistance, refer to the animated graphic below:
Next, we’re going to add a Formula field and call it “Age.” This process is exactly the same as in the basics tutorial. We add a field and give it a name (remember to hit <
ENTER> after you type “Age”).
Let’s add it directly after the Birthdate field, as shown in the animated graphic below:
Now that the Age field has been added, we need to define a formula—this is done in the Formula editor.
To navigate to the Formula editor:
- 1.Select the Age field.
- 2.In the Formula window, select the Formula field.
- 3.The Formula editor appears.
The Formula editor is where the magic happens! This is a 100% drag & drop environment. First, let’s have a look at the four navigation tabs:
- Fields: Displays a list of all fields used in the active table
- Calculate: Tools to add, subtract, multiple, exponents, etc.
- Logic: Tools to create conditional formulas, for example like if / then / else, and, or, =,