Panorama > Database > Fields
 
Data Input Validation

Panorama can check your data as you input it to try to block invalid data before it ever gets entered into the database.

No Duplicates/No Unique

Panorama usually does not care if you enter duplicate information into a database. However, if you wish you can ask Panorama to check for duplicate data every time you enter or edit a data cell in a given field, and warn you if a duplicate is about to be entered. It can also warn you if a new unique value is about to be entered, a value that has never been input before.

Panorama has three options for checking duplicate data — Allowed, No Duplicates and No Unique. Use the Field Properties Panel to select the option you want for each field.

The Allowed option simply tells Panorama to allow duplicates. This is the default.

Use the No Duplicates option to make sure that a value is not entered more than once. For instance, a checkbook database should never have duplicate check numbers.

The No Unique option tells Panorama to warn you if you attempt to enter a value that is not already in the database. For instance if a field contains only Yes/No values, this option would warn you if you attempted to enter True or False instead.

When Panorama encounters a duplicate or unique value (depending on the option), it warns you. However, it does not prevent you from entering the value. You are given the option of entering the data even though it conflicts with the existing data – it’s up to you. You can also re-edit the data.

Keep in mind that even if the No Duplicates option is enabled, there are still several ways for duplicate data to get into a database. First, you can override the duplicate check, as shown above. When data is imported into a database it isn’t checked for duplicates, and data entered via programming code also isn’t checked for duplicates.

Checking for Duplicates in Existing Data

Checking for duplicates only happens when new data is typed into the database. Panorama does not check data that has already been entered, and it does not check data that is imported into the database or brought in via programming.

There are several techniques for checking for duplicates in existing data. See Select Duplicates Dialog to learn how to use the Select Duplicates command. Another method is to sort the data and then use the UnPropagate command to identify the duplicates (by searching for blank cells). See Duplicate Removal with Unpropagate for details on using this technique.

Numeric and Date Validation

Panorama automatically checks all numbers and dates you enter into a database, and warns you if you attempt to enter an invalid value. For example, 3 bedrooms is not a valid numeric value, so Panorama will not allow you to enter that into a numeric field.

Dates are also checked for validity (and yes, Panorama knows all about leap years and the varying length of February).

Don’t forget, Panorama allows entry of Smart Dates like today, yesterday, tuesday or last fri.

Custom Data Validation

If the options above don’t meet your needs, you can set up custom data validation. You can use any formula to validate the data (including Regular Expressions, or even a combination of formulas. To set up custom validation for a database field, add the label validateFieldInput: to the field code (see Automatic Field Code), then add code that calculates true if the data is valid and false if it isn’t. Panorama will execute this code as a function, so the true/false value must be returned using the functionvalue statement, as shown in the example below. This example also shows how the formula can access the special variable _VALUE_, which Panorama sets up with the text that has been input. This example checks to make sure that the numeric value entered is between 1 and 8.

If a number outside of the allowed range is entered, Panorama will display an alert. You have the choice of cancelling the input, accepting the value even though the formula rejected it, or re-opening the editing window.

An important point to note is that the _VALUE_ variable always contains text, even if the field is numeric. This text is the raw text that was entered with the keyboard, before it has been converted to a numeric value or date value (of course for a text field, there is no conversion). So if you want to check a numeric value, you usually need to use the val( function, as shown in the example above.

To check a date value, you usually need to use the date( function. Here is an example that uses the monthmath( function to check data entry in a date field to ensure that no dates older than 3 years ago are entered accidentally. If a date is entered that is more than 36 months before today’s date, the warning alert will appear.

Of course text fields can also be validated. There are many ways to do that, but Regular Expressions are often very useful for this. Here is an example that uses the regexmatch operator to verify that the State field contains exactly 2 alphabetic characters, not 1, 3 or more.

In this case since the Input Range field property is set to AZ, we already know that only alphabetic letters can be entered. So an alternate validation technique in this case would be to use the length( function, like this:

You might think that this validation formula would display a warning if no text was entered at all, since in that case the length would not be 2. However, Panorama always allows empty data input in any cell – in other words, empty text is always valid input. In fact, if the text is empty, Panorama does not even run the validation formula.

Panorama always performs its standard validation checks before any custom validation checks. This means that your custom validation formula doesn’t need to check whether the input contains a valid number or date, that is taken care of for you. So for a numeric field, for example, Panorama always checks for a valid numeric value first. If the value isn’t a valid number, the warning alert is displayed immediately, and your validation formula isn’t even evaluated.

Multi-part Validation Formula

All of the examples so far have shown a single validation formula that returns true or false. You can, however, build a more complex procedure with multiple lines of logic. For example, this code contains multiple regular expression formula to check both US and Canadian postal codes.

validateFieldInput:
    if Country="United States"
        functionresult _VALUE_ regexmatch "^[0-9]{5}(-[0-9]{4})?$"
    elseif Country="Canada"
        functionresult _VALUE_ regexmatch "^[A-Z][0-9][A-Z]\ [A-Z][0-9][A-Z]$"
    else
        functionresult true() // no check for other countries
    endif
    return

Of course this particular example could be expressed in a single formula, but it is often convenient to split it into multiple components like this, especially when customizing the error that is displayed (see below).

Customizing the Message

As described above, Panorama normally displays the message Data input is not valid if your formula returns false. You can customize this message by returning text instead of a false value if the data is invalid. This text will be displayed in the alert. For example, suppose you have a real estate database with a field named Offer. This code will verify that the offer price is not less than 20% below the list price.

validateFieldInput:
    if val(_VALUE_)≥«List Price»*0.8
        functionresult true()
    else
        functionresult "Minimum offer for this property is "+
            pattern(«List Price»*0.8,"$#,")+" (80% of list price)."
    endif
    return

If the offer price is too low, an alert with this very specific message is displayed.

Full Customization of the Alert

It’s a bit more complicated, but you can fully customize the data validation alert, including the button names and descriptive text. This is done by returning a dictionary (see Data Dictionaries) containing components specifying different aspects of the alert.

Here is a revised example that displays a custom alert if the offer is too low.

validateFieldInput:
  if val(_VALUE_)≥«List Price»*0.8
    functionresult true()
  else
    functionresult initializedictionary(
      "MESSAGE",
        "This offer is too low!",
      "EXTRA_MESSAGE",
        "The minimum offer for this property is "+
            pattern(«List Price»*0.8,"$#,")+" (80% of list price).",
      "CANCEL_BUTTON",
        "Cancel Offer",
      "ACCEPT_BUTTON",
        "Accept Low Offer"
    )
    endif
    return

The custom alert will look like this:

With a slight revision, the alert will not give the option to accept a low offer. This example can also be revised to be silent (no beep).

validateFieldInput:
  if val(_VALUE_)≥«List Price»*0.8
    functionresult true()
  else
    functionresult initializedictionary(
      "MESSAGE",
        "This offer is too low!",
      "EXTRA_MESSAGE",
        "The minimum offer for this property is "+
            pattern(«List Price»*0.8,"$#,")+" (80% of list price).",
      "CANCEL_BUTTON",
        "Cancel Offer",
      "ACCEPT_BUTTON",
        "-"
      "BEEP",false()
    )
    endif
    return

Here is the alert with no Accept button. The only options are cancelling the input or re-editing it.

Keep in mind that this will not 100% prevent invalid data from getting into your database, for example invalid data could be entered by importing it from a text file, or by program code.

Redirecting to an External Procedure

Working with a lot of code in the Field Properties panel can get unweildy. If you start finding this difficult, you can move the code to a separate named procedure, and then use a call statement to reference this procedure in the panel, like this:

For this technique to work, the call statement must be the only code in the Field Properties panel. Everything else has to be moved into the separate named procedure.

See Also

History

VersionStatusNotes
10.2NewNew in this version.