Problem:

How can I handle Dates and Null Values in Relativity?

Resolution:

Some of our Relativity customers have experienced problems with the use of date fields from COBOL applications. The problem primarily occurs because many applications written in COBOL make use of certain field characteristics that cause a date field to be invalid in ODBC applications.

For instance, a date field in COBOL that contains spaces or zeroes is perfectly acceptable since the application code can test for those values and take appropriate action. Whereas, when ODBC tools employ a field which is defined as being a date, it must only contain a valid date.

As a way around this problem, some Relativity installations have avoided the use of Date Transforms and simply used the date as a numeric field so that the ODBC application avoids any date checking. This solution doesn't overcome all the issues, but it does limit the need for intervention to some degree. All that needs to take place is that a numeric field, which contains non-numeric data (that is, spaces), needs to have a null value returned to the ODBC application.

With this approach, however, some of the extraordinary functionality of such ODBC applications can be lost. For instance, with MS Excel, the Pivot Table facilities open up extremely powerful query abilities. If a date really is a date (and not just a number), then the date range facilities become possible. In this way, the results of the query can be analyzed very quickly by date ranges so that, for instance, sales figures can be grouped by month or quarter in a matter of seconds.

The following text explains one method to get the desired results. There are other ways of going about it which are briefly mentioned in the body of the article. It is a good idea, when using new techniques such as this, to try them out with a small test system before applying the methods in a real catalog. This way, you can make modifications and test quickly.

Date Transforms In Relativity

The first step is to make sure that the 'Preferences, Date Formats' form has the required date formats checked. If not, check them on, save the preferences and then close them.

In this example, we will be using Data Types to declare the Date Transform, but you can also use the Transform column in the FD design form directly, and you could also use the Item Pattern facility, if it is applicable to your application.

To add a Data Type, choose 'File, Data Types.' The Data Types form appears. In the form, click the ' ' button to add a new Data Type. The Add Data Type form appears. Enter a name for the Data Type that you are declaring, such as 'Century_Date' for a date of the format YYYYMMDD. From the Type pulldown menu, choose 'Date'. Next, choose the applicable date format from the Transform pulldown menu. In this example, you would select YYYYMMDD. Note that once you have chosen Date in the Type field, only the Transform field is available to you since the other three fields are not active and their values do not apply. Click the 'Add Data Type' button to add the new declaration. Close the dialog box.

You can now open up the required FD form to apply the Data Type. Click the magnifying glass icon (Show Detail) to expand the display of the FD, highlight the required field, and then move across to the column headed 'User Data Type'. (Note that you may not see the full name of this column, it may just say 'User Data.' You can adjust the size of the column if you wish). Select this column for the field in question, and you will now see a pulldown arrow. Press this arrow and then choose the Data Type that you declared.

That's all that needs to be done in order to be able to use the field correctly when it arrives at the ODBC application. Now you only need to drag and drop the FD field into the Table definition. Complete the Table definition as normal and then leave Relativity and go to the ODBC application and test it out. If you are using MS Excel with MS Query, when you bring the column for the dates into the spreadsheet, you will now see that the data has been adjusted to a standard date format such as YYYY-MM-DD. When you return the data to Excel, the format will now be YYYY-MM-DD HH:MM:SS:NN. Because that is the default setting for dates in Excel. To change it, highlight the column and choose your preferred format from the Format, Cell facility.

Once you reach this point, you can now start to work with Pivot Cells, and so forth, to get automatic date handling without having to modify your legacy data.

This example deals specifically with the YYYYMMDD format, but if your application has the YYMMDD structure, you can, of course, use the appropriate Transform when declaring the Data Type. The system also handles the situation in which an application stores dates in the format for the number of days since (typically since 31st December, 1899). If that is the way that you hold dates, then the 'Days' Transform is the one for you.

Invalid Dates

As mentioned earlier, it is a frequent occurrence whereby dates held in COBOL contain either zero or spaces, generally meaning that they are not entered yet. Relativity provides a means to help you in these circumstances! Let's first look at the simplest case of a numeric field containing spaces. Unless you do something about it, when you try to look at such a field while using the ODBC application, it is going to return an error.

First, go to the Preferences folder in Relativity and, in the File tab, click on the 'Invalid Numerics return Null' check box. Save the Preference and close Relativity.

A bit more work is required for a field containing zero, when zero is invalid (as in the case of a date field). First, if you already have the fields containing zero declared across to a table, you will need to delete them from the table and move them back once you have completed the FD changes.

In the Preferences 'Data Source' tab, select the 'Show Null Info' check box.

If you already have a Data Type declared for the date format, you will need to open it and check the 'Nullable' checkbox to on. If you are declaring it now, just check the option as you declare it.

Relativity needs to be able to recognize a null value in order to return a null to the ODBC application. The way to do this is via the option to add an 88 level (assuming that it does not already exist). With the FD open, highlight the field concerned and then press the 88 icon to add the new conditional statement. Give it a name (such as DATEZERO), turn the Single Value radio button on, and then enter the value 0 (zero) in the area alongside 'Entered'. Press 'Add' to complete the task. You now have the 88 condition name associated with the field available for use in the FD. The next step is to associate the field and the condition with a null value.

In the FD, move to the column labeled 'Null Value' (again you may need to adjust the column width to see the full column heading). Click on the relevant field within the column and choose the pulldown arrow. Select the relevant condition name and drag and drop the field across to the table. Save the table. You should now be able to access the data properly from the ODBC application, with any invalid data fields being returned as null. In practice, that means that the relevant column/row will be spaced out if that is how the ODBC tool handles null values