Browsed by
Author: David Tinoco

What Is A Database?

What Is A Database?

In this article, I will attempt to shed some light on the question “what is a database?” and how a database can be useful in organizing large amounts of data. I will also cover exactly how FileMaker Pro helps you to create databases quickly and what advantages you will gain in using this product.

I would like to start off by stating that it is highly important to look up words you don’t know when studying a new subject. The reason for this is that you can grasp the theory of of a subject much more easily when you know the key words, and it makes for easier application.

In order to build FileMaker applications that will really be useful and efficiently built, you must understand what a database is and how it is put together. In can seem like a formidable subject at first, but once you grasp about 5-8 important key words, it becomes a simple subject that will build upon these fundamentals concepts.

What Is A Database?

A database could be defined as a storage place for a series of records or several groups of records. A key characteristic of a database is that its records are accessible in many ways and tend to be structured (have a specific form) instead of being random bits of data.

Probably the easiest way to explain what a database is would be to compare it to an Excel file. While these two things are different, the database can be likened to an Excel file in many ways. As you have probably seen, an Excel file has at least one or more spreadsheets, which, in database talk, is the same as a table made up of columns and rows.

A Word On Tables, Columns and Rows

In the example below, our Excel file contains 3 spreadsheets which would be referred to as “tables” in database terminology. Each table, contains a specific set of structured data, meaning that the data is stored in a way that is the same no matter the record. More specifically, the 3 tables in our Excel file are called People, Locations, and Appointments. Each of these tables/spreadsheets have a different set of structured data. They aren’t the same type of data, but they are somehow related, so they go in the same file/database.

A database compared to an Excel spreadsheet to show what a database is

In Excel, you can navigate between spreadsheets at the bottom of the window, by clicking on the spreadsheet tabs. You will then view the records for each type of data set:

Spreadsheet tabs and titles are like tables in a database

In a database, you would always have at least one table. The table will consist of columns and rows. In our above example, we have 4 columns, called First Name, Last Name, Date of Birth, and State. The table also happens to have 4 records (interchangeable can be called rows). Rows are read up and down whereas columns are read from left to right.

Finding Data In A Database – Using Queries

In this way, you can store all data sets in one file without having to make a separate file for each set of records. The reason for doing this is that you can later cross-reference these records and compare the data in each row of a table to records in another table. That is where databases come in handy, and how they are different from Excel files.

For example, say you wanted to do a search for each person from California using our file above. Imagine that this table had something on the order of 5- or 10,000 records. It would be a hassle to have to scroll through a list of so many individuals. Moreover, they may not have been added into the spreadsheet in alphabetical order, making it a daunting and almost impossible task which, at the very least, would be extremely time consuming.

With a database, you could simply perform a query and ask your database to find the pertinent records using a standardized language called SQL (Structured Query Language). The database, being a mixture of storage data and retrieval code, would obediently perform the task in milliseconds, and only show the records that match your criteria.

A typical query of this nature might look something like:

SELECT `FIRST NAME`, `LAST NAME` FROM PEOPLE WHERE `STATE` LIKE "CALIFORNIA"

This would then return the following set of records:

Thomas Smith
Erica Rhodes
Bill Rodgers
Peter Todd

Normally, you have to learn this language in order to know how to ask for your data. Sometimes, it isn’t easy. That is why FileMaker is so powerful–it has tools that get rid of most of the need to directly write query language and allows you to focus on design and functionality. With FileMaker Pro, you don’t need a degree in Computer Science to make a database that will increase efficiency and reduce paperwork.

Summary

A database is a storage and retrieval device with contains a structured set of data that can be accessed in many different ways.

A database always has at least one table, but can have many more, depending on the complexity of your system.

A table consists of columns or fields, and each record/row in that table contains one instance of a set of data.

Tables can be queried to create, retrieve, update or delete records within it, and tables can also be cross-referenced against other tables using SQL (Structured Query Language).

FileMaker Pro uses advanced functionality to eliminate query writing, enabling the user to create advanced table structures and database schema. This allows the user to easily query and display sets of records or cross-referenced data in a database.

How these tables are related to another and how they are tied together is called database schema, (schema means “the form, figure or outline of something.”)

Be sure you check out more articles in this blog to get a better understanding of how FileMaker can help you with your database development.

How To Generate Barcodes With FileMaker Pro

How To Generate Barcodes With FileMaker Pro

Want a free and simple solution for creating barcodes right inside of FileMaker? No plugins, no paid extra stuff–totally free. Well, that’s what this tutorial teaches you to make. This comes in handy when you start developing applications for FileMaker Go as it enables you to use barcodes in your app without the need for barcode fonts or plugins (save money, develop more).

You can create many types of barcodes from Code128 to Code39, etc., and even QR Codes.

All you have to do is know a little HTML and Javascript to make it happen, along with FileMaker Pro’s Web Viewer.

The tutorial below shows just how to go about it.

Many thanks to artdomg @ jqueryscript.net for providing the jQuery Plugin to make this happen.

You will need to download:

  1. jQuery and
  2.  jQuery Barcode

Good Luck!

Below is a link to the sample file:

Barcode Generator Sample File [BarcodeGenerator.fmp12.zip]

Searching Large Amounts of Records with Dynamic Portals By Name or Category

Searching Large Amounts of Records with Dynamic Portals By Name or Category

If you have ever wanted to search a large amount of records either by Name or Category, or other fields, there is a somewhat simple solution which uses an auto-calculated text field, which is used as a foreign key multi-value list and global fields to perform a relationship match.

You only need 1 table to perform this type of operation.

1. Define the fields

Let us say we have a table of companies which has the following fields:

id
name
category

To this, we add a fourth field which is set to auto-enter a calculation as follows:

"_all" &
If( not IsEmpty( category ) ; ¶ & "_cat_" & category ) &
If( not IsEmpty( name ) ; ¶ &
Left( name ; 1 ) & ¶ &
Left( name ; 2 ) & ¶ &
Left( name ; 3 ) & ¶ &
Left( name ; 4 ) & ¶ &
Left( name ; 5 )
)

What this gives us as a result when the name is “ABC Co.” and the category is “Phone Companies” is this:

_all
_cat_Phone Companies
A
AB
ABC
ABC
ABC C

In other words, we use the keyword “_all” with an underscore before it to avoid confusing it with any company beginning with “all”. That way, if we want to match any company, we would match against “_all” as the keyword.

The same idea applies with “_cat”.

And then, we enter up to the first 5 characters of any name so that we can search for up to 5 characters.

This field needs to be indexed with minimal indexing for it to work in our relationship we will create.

Next, we create some globals:

SEARCHOPTION
SEARCHCAT
SEARCHNAME
SEARCHFILTER

We use capitals to make them stand out as globals. Be sure to set them as globals. Naming them with capitals is not enough. We are following the standards as provided at FileMaker Coding Standards.

We then add an auto-enter calculation to the SEARCHFILTER field as follows:

Case( 

SEARCHOPTION = "all" ; "_all" ; 

SEARCHOPTION = "category" ; "_cat_" & SEARCHCAT ; 

SEARCHOPTION = "name" ; SEARCHNAME ;

 ""

 )

NOTE: IN BOTH AUTO-ENTER CALCULATIONS, MAKE SURE YOU UNCHECK DO NOT REPLACE EXISTING VALUE OF FIELD (IF ANY)

2. The next step is to set up your relationships and layouts.

Take a look at this sample file to see the rest.

Dynamic Portals Sample File.fmp12

Conditional Formatting for Highlighting Required Fields

Conditional Formatting for Highlighting Required Fields

Every now and then, you will need to highlight fields required on a layout/database. You may have observed that validation turned on in a table field can be cumbersome especially if that field won’t be on certain layouts. Here is a lesson to show a workaround for this, which also highlights mandatory fields which have been left blank by the user.

Here is a link to the file referenced in the above video:

Conditional-Formatting.fmp12.zip

How to Make Progress Bars in FileMaker Pro

How to Make Progress Bars in FileMaker Pro

Have you ever wanted to created a live-updating progress bar? Piecharts and graphs are a cinch, but the ability to make a good-looking progress bar seems almost impossible–unless you know how to use the Web Viewer Tool!

Using HTML/CSS and a few calculations, you can simulate a progress bar that will update in real time using Script triggers and the ExecuteSQL function.

Watch the video at FMTutorials on Youtube.

Creating Modal Dialogs

Creating Modal Dialogs

This tutorial is a remake of an earlier one, with up-to-date information on how to create a modal dialog using FileMaker Pro version 14. This technique is more simple, with FileMaker’s update to the New Window script step, which now allows for 3 different types of windows. You now have the ability to turn off resizing, close button and other features to make a truly modal window which cannot be exited or canceled by the user without following through with the desired action.

Autosizing in FileMaker

Autosizing in FileMaker

Learn how to use the autosizing feature in FileMaker Pro 14 to create fields and objects that resize themselves to fit the window width, height, or even to right align objects on the layout.

A very useful feature if you want to maximize screen usage and provide layouts that dynamically “adjust” themselves.

Leave comments and questions below.