Developing with SQLWindows : Formatting and Validating

Formatting and Validating
This chapter explains:
About formatting
You can apply a format to objects such as data fields and table window columns. The format determines how the data is displayed. After the user enters data, the application displays in the format that you set.
Formatting is related to validation. Validation, the process of ensuring that data the user enters fulfills certain conditions, is explained on page 7.
The formats that you can apply depend on the data type of the object. You format string and long string data types differently than number and date/time data types.
Setting a format
Select Format in the Attribute Inspector to display the formats available for the object. The default format for all objects is unformatted.
String and Long String formats
You can format string and long string data types as:
Date/Time and Number formats
You can use two types of formats for number and date/time objects:
Picture format
Picture formatting is the recommended method.
For number or date/time data types, you can:
You can format date/time or number data types in different ways. For example, you can format a date/time data type so that the date is displayed as 7/1/91 or July 1, 1991.
You can format a number data type as a decimal, percentage, or currency.
Picture formats
Picture formats are a visual template of the formatted data. They “look like” the data after the data is formatted. For example, the date/time picture format m/d/yy formats a date as 7/1/91, and a number picture format #.## formats a number as 1234.56. These same types of formats are used in Excel and COBOL.
You can only use picture formats for date/time or number data types.
You can use a built-in picture format, or you can create a custom picture format.
Creating a custom picture format
Using the picture characters in the following tables, enter the format in the Attribute Inspector you want for an object. SQLWindows adds the new format to the Formats section in Global Declarations. The format then appears in the format list for existing objects and new objects.
You create a new picture format using the characters in the following tables.
Date/Time picture format characters
These format characters are case-sensitive.
Number picture format characters
Zero suppression. SQLWindows does not add extra zeros if the value has fewer digits on either side of the decimal point than there are #'s on either side of the format.
Zero fill. SQLWindows adds extra zeros if the value has fewer digits on either side of the decimal point than there are zeros on either side of the decimal point in the format.
If the number has more digits to the right of the decimal point than there are zeros to the right of the format, SQLWindows rounds the number to as many decimal places as there are zeros to the right.
Thousands separator. Commas must be three positions to the left of the decimal point. For example, '#,###, ##0.00' is legal but '#,##,##0.00' is not.
Percentage. SQLWindows multiplies the number by 100 and adds the % character if the percent character is immediately after the last zero.
Fixed or floating dollar sign. A fixed dollar sign is always in the same position at the start of a field; a floating dollar sign is next to the most significant digit.
Scientific notation (exponential). If a format has a 0 or # to the right of an E-, E+, e-, or e+, SQLWindows displays the value in scientific format and adds an E or e. The number of digits (#'s or 0's) sets the exponent's number of digits. E- or e- places a minus sign by negative exponents. E+ or e+ places a plus sign by positive exponents
Decimal point. The number of digits (#'s or 0's) to display to the right and left of the decimal point.
If the format has only #'s to the left of the decimal point, SQLWindows begins values less than 1 with a decimal point. To avoid this, use 0 as the first format character to the left of the decimal point instead of #.
Character fill. Repeat the next character enough times to fill the field width. Place this character before the first # or 0 character. For example, $*, ###0.00 results in a fixed dollar sign.
Separator. Separates positive and negative formats. For example, ##0.00; -##0.00. There can only be one “;” character in a format. If there is no “;” character and the value is negative, SQLWindows adds a “-” to the value.
Alignment. Leaves a space for the character that follows the _ (underscore). This is useful for aligning fields. If this character is used at the end of a number, place the “_<some character>” as the last two characters in the format.
Profile-driven formats
Warning: The profile-driven formats have been deprecated. The code that enables this functionality is still present in SQLWindows but only for the purpose of backwards compatibility. GUPTA recommends using the SQLWindows Picture formats instead. For more, read Picture formats on page 11-3.
You can use profile-driven formats for Date/Time and Number data types.
Profile-driven formats use Microsoft Windows country profiles. The formatting used depends on the settings in the International control panel in Windows NT or the Regional Settings control panel in Windows 95. For more, read Country profiles on page 11-6.
The data type of the field determines its possible formats. For example, you can format a number field as currency, decimal, percentage, or unformatted. The table below lists data types that you can use with each profile-driven format:
Country profiles
Since countries use different ways to express formatted data (such as the currency symbol), SQLWindows associates a format with a country profile.
The country item specifies the country profile to use to format table window columns and data fields.
A country profile is a collection of format specifications for different countries. SQLWindows has internally-defined profiles for certain countries.
When you set Country, SQLWindows uses the format characteristics from the associated profile.
For example, when you select USA as the country for a formatted currency field, SQLWindows uses two decimal places to the right of the decimal point and the dollar sign ($) currency symbol. These currency parameters are defined in the USA profile.
Default country profiles
When you set the Country to Default, SQLWindows uses the country profile settings in the control panel. Usually, the default profile is for the country where you live.
Country profile parameters
The table below describes the parameters for a profile and lists the corresponding format.
Whether to use a leading zero with decimal values less than 1.0 and greater than -1.0. Set to 0 for no leading zero (.7); set to 1 for a leading zero (0.7).
About validation
Validation is the process of ensuring that data the user enters meets certain requirements.
Validation is related to, but separate from, formatting. Formatting determines how data is displayed after it is entered; validation ensures that data meets conditions before it is accepted and displayed.
There are three types of validation you can use in SQLWindows applications:
Default validation
SQLWindows performs default validation for number and date/time data types.
SQLWindows performs default validation as soon as the focus leaves the object. If an entry is invalid, SQLWindows does not let the user move the focus away from the object until the entry is correct or the entry is blank.
Number data types
For number objects, any sequence of numeric characters is valid.
If the object has a number picture format, SQLWindows first checks if the format matches the settings in the Regional Setting control panel. If it does not, SQLWindows then accepts the entry if it exactly matches the picture format. This is useful for entering numbers in scientific notation.
Date/Time data types
To validate date/time data types, SQLWindows follows the steps below. As soon as the entered data meets one of the following criteria, the entry is valid and SQLWindows formats and displays the data.
SQLWindows looks in the Regional Settings control panel for the sShortDate setting. If the entered data matches sShortDate, the data is valid.
If the data does not match sShortDate, SQLWindows checks if the data matches the sLongDate setting. If the entered data matches sLongDate, the data is valid.
If the data does not match sLongDate, SQLWindows checks it using the following standard date formats, shown here in picture format:
If the entered data matches any of the formats above, the data is valid.
If the data does not match any of the formats above and the object has a date/time picture format, SQLWindows checks the entry against the picture format. If the entered data matches the picture format, the data is valid.
If the entered data does not match any of the above, then the data fails validation and SQLWindows displays an “invalid data” message.
Custom validation
You can replace the SQLWindows default validation with custom validation.
For custom validation, you process the SAM_Validate message for an object. Whenever the user changes an object, SQLWindows sends SAM_Validate. Usually, you Return VALIDATE_Cancel if validation fails or VALIDATE_Ok (this resets the field edit flag to FALSE) if validation succeeds. If you do not return a value when processing SAM_Validate, SQLWindows performs its default validation. The value you return from SAM_Validate processing controls whether the focus changes.
For example:
Data Field: dfName
On SAM_Validate
If SalIsNull( dfName )
Call SalMessageBox( 'You must enter a value',
'Data Error', MB_Ok )
Return VALIDATE_Cancel
SQLWindows does not send SAM_Validate when the user selects a menu item (a menu selection does not change the focus). You can force SQLWindows to send SAM_Validate by calling SalSendValidateMsg in the menu actions of a menu item. This forces validation before processing a menu selection (without changing the focus). SalSendValidateMsg returns the value that the SAM_Validate message processing returns.
For a table window, process the SAM_RowValidate message to check the contents of a row:
Table Window: tbl1
Message Actions
On SAM_RowValidate
If SalIsNull( col1 )
Call SalMessageBox( 'You must enter a value',
'Validation Error', MB_Ok )
Return VALIDATE_Cancel
For more, read Chapter 9, Messages.
Input masks
An input mask validates data as a user enters it. You use an input mask to specify a criteria for a value. These are the criteria that you can specify:
Alphabetic or numeric characters. If the user types a character that is invalid, SQLWindows beeps.
Uppercase or lowercase characters. SQLWindows automatically converts the character if the user enters it in the wrong case.
Constants. SQLWindows inserts a constant (such as a hyphen) in a position automatically without the user typing it.
Defining input masks
You can set input masks for fields. In this section, field means:
You can set an input mask for any data type.
You define an input mask for a field using the Attribute Inspector.
You can choose an existing input mask or define a new one. The existing input mask formats are in the Formats section (under Global Declarations) with the Date/Time and Number picture formats. SQLWindows adds new input masks that you define to the Formats section.
Input mask characters
You can use these characters in input masks:
Mask character
In the first position of an input mask, this character means not to unmask the value on get operations. This only applies to String fields and overrides the setting of SalFmtKeepMask for the field.
All other characters (including spaces) in an input mask are a constant that SQLWindows inserts automatically.
These are examples of input masks:
Input masks for Numbers
SQLWindows treats masked number fields in this way:
When the user moves the focus off a masked number field, SQLWindows formats the field contents according to its output display format
This lets you to create both a mask for input and a format for display (such as for currency).
Input masks for Date/Times
You can define an input mask such as 99/99/9999 or 99-99-9999 for a Date/Time field so that the user does not have to type the date separator (such as slash or hyphen). However, if the user moves the focus off the field and then moves the focus back to the field, SQLWindows does not use the input mask when the user edits the value.
Warning: Because of this behavior, Gupta Technologies LLC recommends that you not use input masks with Date/Time fields.
Setting a field’s value
When you set a field, SQLWindows applies the input mask and truncates, if necessary. In the example below, the df1 data field has this input mask: “XX-XX”. When the user clicks the pb1 push button, the df1 data field displays “ab-c-”.
Data Field: df1
! This field is a String data type and has
! this input mask: "XX-XX"
Pushbutton: pb1
Message Actions
On SAM_Click
Set df1 = 'abc-d' ! df1 displays "ab-c-"
Getting a field’s value
When you set a variable with the value of a field, SQLWindows removes input mask characters. In the example below, the df2 data field does not have an input mask. When the user clicks the pb2 push button, df2 displays “abc-”.
Data Field: df2
! This field is a String data type and does not
! have an input mask
Pushbutton: pb2
Message Actions
On SAM_Click
Set str1 = df1 ! Assume that df1 displays "ab-c-"
Set df2 = str1 ! df2 contains "abc-"
Window Variables
String: str1
You can change this behavior by calling SalFmtKeepMask (see the next section).
Input mask functions
You can use the input mask functions below at runtime.
This function returns the input mask of field hWnd in strMask:
bOk = SalFmtGetInputMask( hWnd, strMask )
This function sets the input mask of the field hWnd to the value in strMask:
bOk = SalFmtSetInputMask( hWnd, strMask )
This function validates the input mask in strMask and returns TRUE if the mask is valid:
bOk = SalFmtIsValidInputMask( strMask )
By default, SQLWindows removes input mask characters when you copy the value in a data field, table window column, or combo box. For example, if you create a data field with the input mask “AA-AA” and copy its value to another data field, SQLWindows removes the hyphen. You can call SalFmtKeepMask to change the default behavior and include input mask characters when you copy the value in a field:
bRet = SalFmtKeepMask( bKeep )
If bKeep is FALSE (default), SQLWindows removes input mask characters when you copy a value. If bKeep is TRUE, SQLWindows keeps the input mask characters when you copy a value.
This function returns the value you specified in bKeep.
If a field has a “^” character in its input mask (see above), it overrides the setting you make with this function.
This function unmasks the contents of hWnd and puts the result in strInput:
bOk = SalFmtUnmaskInput( hWnd, strInput )
Only use this function in SAM_Validate message processing.
SAM_Validate processing
You can process SAM_Validate messages for fields that have input masks.
You can call SalFmtUnmaskInput in the SAM_Validate processing to get the value the user entered without the input mask constants.
SalIsNull works the same for a field that has an input mask as for other fields. If the field is empty, SalIsNull returns TRUE, even if the input mask has constants.
Developing with SQLWindows

Unify Corporation