Data Entry Helpers

303: Data Entry Helpers

Previous: 302: Checkbook Sample Data

Before you can work with and analyze data, you have to get the data into the database, and that usually means data entry with the keyboard. This job is never easy, but Panorama does have tools that can make data entry faster and less error prone. Let’s take a look at how Panorama can improve the data entry process for each of the fields in the checkbook database.

Date

The first field in the database contains the check date. Panorama has a special data type for storing dates, and you should aways use the Date type, not the Text type, for storing dates.

When you use the Date type to store your dates, Panorama can sort your dates in the correct order, check your dates for validity as they are entered, and calculate the number of days between two dates (see Dates to learn more). If dates are stored in a text field, Panorama can’t do any of those things.

Smart Dates — Panorama is very flexible about how you enter dates from the keyboard. We call this feature smart dates. You can enter dates numerically (for instance 04/09/02 or 4/9/2) or you can spell out the date (for instance April 9th, 1997 or Apr 9 97). You can use any character as a separator between numeric dates, for example 4-9-01 or even 4.9.01.

To enter today’s date, simply type today. You can also enter yesterday or tomorrow. Panorama will automatically convert these entries to the correct month, day and year.

If the date is in the current week, you can simply type in the name of the day, for example saturday or tue. To specify a day in the previous or upcoming weeks add the words last or next, for example next tuesday or last saturday. (This movie was recorded on January 11, 2017.)

When you enter a date, you can leave the year off and let Panorama figure it out for you. Or, you can include the 2 digits of the year and let Panorama figure out the century. See Dates to learn more about this feature.

Output Pattern — Internally, Panorama stores dates in a pure binary form. But when a date is displayed or printed, it is converted to text so that you can read it. This conversion can take different forms, for example the date July 20, 1969 could be formatted many ways, some of which are shown here:

7/20/69
07/20/69
20-Jul-69
July 20th, 1969

By default, Panorama will pick the simplest format, but you can explicitly control how dates are formatted by changing the Pattern option. See Date Patterns to learn all the details.

Default Value – This field has been set up to default to today’s date.

When a new record is added to the database, the default value is automatically entered into the field. (This movie was recorded on May 4, 2017.)

If you don’t want a default value, simply leave this option empty.

Check Number

The second field in this database contains the check number. Panorama has automatically set up this field to contain number values, in this case integers (whole numbers). You should always use the Number type to store numbers, don’t store them as text. When you use the Number type to store numbers, Panorama can make sure that you don’t enter invalid numbers, it can perform computations on the numbers, and it can sort and search the numbers correctly (see Numeric Data). If numbers are stored in a text field, Panorama can’t do any of those things.

Default Value – If this field is set up to default to +1, the check numbers will automatically increment when new records are added to the database.

Of course you can change this default to any value you want, or you can remove it entirely. See Field Properties.

PayTo

This field has two settings that have been inititialized to help make data entry easier, Auto Caps and Clairvoyance.

Auto Caps – Use this pop-up menu to tell Panorama to automatically capitalize data entry in a field. Panorama can automatically capitalize everything, or just the first letter of each word or sentence.

Clairvoyance — Many databases contain fields where the same information is repeated over and over. For instance, a checkbook will contain the same bills month after month — rent, phone, utilities, charge cards. Another example is an inventory database that contains many items from each vendor, with the vendor name repeated over and over. Panorama’s Clairvoyance feature anticipates when you are about to enter data that has been entered before, and completes the entry for you. This can save you a lot of typing, and helps improve consistency as well.

How can Panorama anticipate what you are about to type? The secret lies in Panorama’s ability to scan the database in a fraction of a second. When you are using Clairvoyance, Panorama scans the entire database each time you enter a character. As it scans the database, it checks the characters you have typed against the data already in the database. When there is only one possible match, Clairvoyance guesses that you are about to repeat yourself and completes the word or phrase for you.

Of course, Clairvoyance can only be helpful when you are repeating a word or phrase that is already in the database. If you are entering a new word or phrase, Clairvoyance cannot help you — but it won’t get in your way, either. As you type in a new word or phrase Clairvoyance may guess that you are entering an old word or phrase. Just keep typing, and Clairvoyance will automatically erase its guess when it no longer matches what you have typed.

Clairvoyance™ Helps Ensure Data Consistency – One problem when building large databases is making sure that information always gets entered the same way, especially when more than one person is keying in the data. For example, a single company could be entered in your inventory database many ways—

Fuji
Fuji, Inc
Fuji USA
Fuji Photo, Inc
Fuji Photo Film USA
Fuji USA, Inc.

Clairvoyance helps solve this problem by accurately repeating the information time after time. You may find that Clairvoyance’s ability to ensure data consistency is more important than the keystroke savings.

Memo

This is just a garden variety text field, with no special options.

Debit, Credit & Balance

Panorama has automatically set up these three fields to contain number values, in this case floating point numbers (which means they can be fractional values, i.e. they can contain a decimal point – see Numeric Data).

Output Pattern — To make sure the floating point values are nicely formatted as money, these fields have an output pattern that formats the numbers with a dollar sign and with two digits after the decimal point. You can change the output pattern at any time.

To learn more about custom numeric output patterns, see Numeric Patterns.

Extra Credit: Automatic Lookup of Category and Debit

In this database, a particular PayTo will almost always go with a particular Category. For example the category for checks to Blue Cross will always be Insurance, while the category for Staples will always be Office Supplies. Panorama can be set up to use a formula to automatically look up the category from the previous record, if any. To do this, open the Field Properties Panel for the PayTo field, click on the Code panel (see Automatic Field Code) and enter this code:

Category = lookuplast("",PayTo,PayTo,Category,"")

To learn more about how this formula works see Linking With Another Database. For now, just try typing Staples into the Category field. When you press the Enter key, Panorama will automatically look up the appropriate category, in this case Office Supplies.

If you write a check to someone you’ve never written one to before, of course Panorama won’t be able to look up the previous category. Just tab over and type in the category manually (with an assist from Clairvoyance, if this is a category that has been entered before).

Sometimes the check amount is the same from check to check also, for example a rent or subscription payment. You can use a similar technique to build a formula to look up the previous check amount. The formula is almost the same as before, but this time it looks up the Debit field and has a default value of 0 instead of “” (see Linking With Another Database).

Debit = lookuplast("",PayTo,PayTo,Debit,0)

Now I’ll type Pacific Properties into the PayTo field. When I press the Enter key Panorama will look up both the category and the check amount from the previous check to Pacific Properties.

If this isn’t the correct amount I can simply tab over to the Debit field and type in the actual amount.

Next: 304: Selecting Data

See Also

Automatic Field Calculations -- performing formulas automatically when data is entered into a field.
Automatic Field Code -- running a short program when data is entered into a field.
Date Patterns -- control how dates are displayed or converted to text.
Dates -- working with dates.
Field Properties -- available field attributes.
Field Properties Panel -- examining and modifying field attributes.
Fields -- introduction to database fields.
lookup( -- searches a database for a value, then returns other information from the same record. For example, the *lookup(* function can look up a phone number given a customer name, or look up a price given a part number.
lookuplast( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplast(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookup( function which searches from the top of the database, the *lookuplast(* function searches backwards from the bottom.
Numeric Data -- numeric data (fixed and floating point).
Numeric Patterns -- control how a number is displayed or converted to text.
Smart Dates -- keyboard entry of dates.
superlookup( -- searches a database for a record that matches a query formula, then uses a second formula to return other information from the same record. This is similar to the lookup( function, but instead of using an individual field for the key and data, any valid true/false (Boolean) formula can be used.

History

Version	Status	Notes
10.0	Updated	Carried over from Panorama 6.0, with updates.