Sesame Library | Getting Your Q&A Data in Good Shape for Sesame

Good old Q&A lets you get away with haphazard data entry,
but the coming Q&A replacement product wont be so open-minded.

With Sesame just months away, now is the time to be looking at your Q&A databases and tidying them up for a smooth transition. Although the vast majority of the elements of your Q&A databases will translate seamlessly into Sesame, there are some things you will need to do manually. Most of these fall into the category of getting your data in good shape: healthy and consistent. In this article Ill show you how you can ensure that your Q&A data will conform to the more rigorous data typing of Sesame.

What is data typing?

Your database consists of fields. When you create a new Q&A for DOS database or redesign one, the next screen you see when you press F10 to save the design is the Format Spec. (In Q&A for Windows, use Select / Database Structure.)This is where you tell Q&A what type of data you propose to store in each field. The choices are as shown in the help panel that automatically appears with the Format Spec.

The way the data behaves varies according to which data type you select for a field. For example, if you make a field a Date (D) field, then Q&A will only let you enter a valid date. The 31st of September is not a valid date and Q&A will tell you so as you exit a date field containing such an entry. A number field will only permit numbers to be entered, not text. A Yes/No field will allow only suitable entries. (These are more numerous than you may think; as well as Yes and No and Y and N, Q&A allows you to enter True, False, T, F, 0 and 1.)

This is all well and good, but theres a snag. Q&A is not very strict about enforcing these rules. If, for example, you try to enter Maybe into a Yes/No field, you will get this message:

This doesnt look like a yes/no value. Please verify.

If you press Enter, Q&A then considers you have verified the entry and allows you to keep it! Similarly with dates. If youre inclined to just ignore the warning message, then you can put Shakespeares 23rd sonnet into a date field!

You might see this as a virtueif you dont know the date, you can put in a partial date, such as Feb. 99?. While this might appear to be a neat solution, the downside is that this record wont show up if you search a date range on this field. It effectively disappears from the database. This wont matter if all you want to do is view the record and check the date visually. But imagine if a user enters a transaction date of 30 February 2002 and ignores the warning. This sale will be missing from reports that show sales for any date rangesales orders, billings, receivables. You get the picture.

Whats different about Sesame?

The Sesame team feels that data integrity is far more important than the ability to enter data haphazardly or for the sake of momentary convenience. So Sesames data type rules will be strictly enforced. A date field will only accept a valid dateeven if you try to force a non-date entry via a Mass Update! Likewise with Yes/No, Time and currency fields.

So, what about existing values? When importing a Q&A database, Sesame will look at the data in a field and check that it conforms to its data type. It will only import, in full, fields where all the values are valid. (Invalid entries will be handled in a sympathetic way.) Consequently, its a good idea to sort out any invalid field entries in your databases nowbefore you get to the point where youre migrating them to Sesame. This is not onerous from a technical standpoint, but it might be from a business one.

How to find errant field values

/..

This search parameter (a forward slash followed by two periods) will find all occurrences of invalid data types in the field (in other words where, if manually entered, Q&A would have displayed a This doesnt look like .. warning). You can likewise search for valid entries with:

.. (two periods)

If you find that there are quite a few such bad entries, then you might like to isolate just some of them to perform a Mass Update, for example, or to mass delete or export these records. To do this, you will need to isolate just some of the invalid entries at the Retrieve Spec. The problem is: you cantyoull get a Not a valid Retrieve Spec error message, and this time you cant just press Enter and continue!

Help is at hand, though, with another, even more obscure search pattern. Suppose you found you had loads of records with bad dates in a Date field. Besides numerous other invalid dates, there are many where the date field contains just the year, such as 1993, 1997, 1999 or whatever. You decide that you will make these dates valid by entering a date of January 1 in that year via a Mass Update. Heres the Retrieve Spec parameter you can use to find such records:

]199?

(That is: the right square bracket followed by 199?). The right square bracket, when its the first character in the search pattern, tells Q&A to perform a text search in a non-text field. For the Update Spec, you would might use programming like this (where field #10 is the field that contains the year):

#10 = Jan 1 + @Str(#10)

But youd soon find that this doesnt work. Why not? Well, because you cannot perform string manipulation on a text value in a date field. So, what you need to do is this. First, back up your database! Then, change the date field to a text field at the Format Spec (File / Customize / Format Values). Then you can perform the Mass Update. (You can use the ]199.. syntax if you wish, but of course a normal text search will now work.) The Update Spec then becomes even simpler:

#10 = Jan 1 + #10

After that you can change your field back to a date field.
You can use wildcards with the ] character. Suppose in a date field there are entries such as See John or See Beth. You can find all of these by using the following search pattern parameter in the Retrieve Spec:

]see..

Conclusion

The time you spend making your database(s) well-disciplined will not be wasted. The conversion to Sesame will go more smoothly and, in the meantime, youll get used to making and keeping your data clean and reliable.

Its likely to take you no more than an hour or so to find and correct the anomalies, if they are relatively few. What could take very much longer is if you have numerous invalid dates, numbers, and the like. This might have happened because data was imported, Mass Updated or inserted by regular programming without regard to what was actually going into the field or, as I have seen on more than one occasion, temporary data entry staff being brought in and spending weeks ignoring the frequent bright red Q&A warnings! In these cases, finding the faulty records might take only minutes, but formulating the business rules for how to handle the anomalies might take weeks!

Better start tomorrow!