Archive for Tips
Using the Windows Clipboard in FoxPro
Posted by: | CommentsThe Windows Clipboard in easily accessed in FoxPro and Visual FoxPro (VFP). There are currently two commands that directly relate to it. These are _CLIPTEXT to copy and paste text and DataToClip() to copy values from a table. These are not highly sophisticated commands, but they are quick and straightforward and are often the simplest way to transfer data between applications.
Let us explore these two VFP commands further and provide some examples:
_CLIPTEXT System Variable
The _CLIPTEXT System Variable contains the content of the Windows Clipboard and can be engaged to write text to and read text from the Clipboard. This feature has been available since the earliest versions of FoxPro for Windows. The syntax is very simple:
_CLIPTEXT = cExpression && writes cExpression to the Clipboard cExpression = _CLIPTEXT && stores the Clipboard content to cExpression
One of the most common uses of _CLIPTEXT is during program development. Long SQL statements, commands and other strings generated by programs at runtime are often difficult to debug using VFP’s standard tools. In the following code sample an SQL statement is copied to the Windows Clipboard so the developer can paste it into a wordprocessor or other application for additional analysis:
cSQL = cFields + cSource +cFilter + cOrder + cOutput _CLIPTEXT = cSQL SET STEP ON && opens the Trace window and suspends the program
DataToClip Method
DataToClip() copies a set of records to the Windows Clipboard. It is a method of the Application Object or the _VFP System Variable. The field names appear as the first line of the text copied to the Clipboard followed by a separate line for each record. This method has been available since VFP 5 and is only slightly more complicated:
Syntax
ApplicationObject.DataToClip([nWorkArea | cTableAlias] [, nRecords] [, nClipFormat])
Arguments
- nWorkArea
- Specifies the work area number of the table for which records are copied to the Clipboard. If you omit cTableAlias and nWorkArea, records are copied to the Clipboard for the table open in the current work area.
- cTableAlias
- Specifies the alias of the table for which the records are copied to the Clipboard.
- nRecords
- Specifies the number of records copied to the Clipboard. If nRecords is greater than the number of remaining records in the table, all the remaining records are copied to the Clipboard. If nRecords and nClipFormat are omitted, the current record and all remaining records are copied to the Clipboard.
- nClipFormat
- Specifies how fields are delimited. The settings for nClipFormat are:
-
nClipFormat Description 1 (Default) Fields delimited with spaces 3 Fields delimited with tabs - If nClipFormat is omitted, fields are delimited with spaces.
This following copies the current work area from the current record to the end in tab-delimited format:
_VFP.DataToClip(,,3)
This following copies the current work area from the current record to the end in space-delimited format:
_VFP.DataToClip()
There are some important limitations with the DataToClip Method. Memo field content and General fields are not copied, there is no way to apply a filter or select which fields will be copied, and bugs have been found in the method’s implementation in VFP 5 and VFP 6. Also note executing DataToClip() does not move the record pointer.
What is Encryption and Why Use It?
Posted by: | CommentsEncryption is the process of transforming information using an algorithm (called a cipher) to make it unreadable to anyone except those possessing a special key to unlock the information. The unlocking process reverses the encryption and is know as decryption.
Encryption has long been used by militaries, diplomats and governments to facilitate secret communication, and it is now commonly used to protect information within many kinds of civilian systems. According to a 2007 report by the Computer Security Institute, 71% of the companies surveyed utilize encryption for some of their data in transit while 53% utilized encryption for some of their data in storage.
In recent years the use of encryption has increased substantially as more and more information has become accessible through the internet both while at rest on servers and while in transmission from one network to another. The internet is a wonderful resource that allows us to communicate instantly with anyone, virtually anywhere—but it has also allowed access to cyber criminals and electronic hackers who can steal millions of dollars in seconds.
While it is still safest to keep sensitive information on removable storage media such as portable external hard drives and flash memory drives, these can be lost, and this is not a practical security solution in many situations.
Encryption is particularly important for protecting credit card numbers, Social Security numbers, passwords, personal identification numbers (PINs), client lists, business plans and corporate intelligence. However, for many it is also important for concealing political dissent as well as opposition in the workplace.
The best known encryption is Pretty Good Privacy (PGP) developed by Philip R. Zimmermann, Jr. Originally designed as a human rights tool, PGP was published for free on the Internet in 1991. It is now owed by PGP Corporation (a part of Symantec).
There is no known method which will allow someone to break PGP encryption, and it has been described by computer security specialist Bruce Schneier as “the closest you’re likely to get to military-grade encryption”. Nevertheless, early versions of PGP have been found to have theoretical vulnerabilities, so new versions have been developed or recommended.
PGP and similar products follow the OpenPGP standard for encrypting and decrypting data. Defined by the OpenPGP Working Group of the Internet Engineering Task Force (IETF) in Proposed Standard RFC 4880, OpenPGP is today the most widely used eMail encryption standard in the world.
In most cases the impact of unwanted disclosure of information is negligible—but NOT ALWAYS! Do not discount encryption as too difficult, time consuming or expensive. It is not and in some cases is required by law. There is a good chance one day you will be glad you utilize it.
Using the pdNickname RELFLAG Field
Posted by: | CommentspdNickname is a unique nearly 50,000 record database designed to facilitate comparing sets of first name data based on nicknames, diminutives, pet names, variations and given names. One of the most important fields in the database product is RELFLAG, which stands for “Relationship Flag”.
The RELFLAG field contains one of two possible values:
-
Close relationship between the name and variation (common variants):
Includes closely associated nicknames, diminutives and pet names as well as first name variations that are considered closely related.
-
More distant relationship between the name and variation (less common variants):
Includes alternate forms of the names, often deriving from another culture, as well as nicknames, diminutives and pet names that are relatively uncommon.
PDNICKNAME VARIATIONS FOR THE GIVEN NAME “SAMUAL”
The RELFLAG field indicates if the name and variation have a (1) close or (2) more distant relationship.
The RELFLAG field is useful for controlling what is to be considered an acceptable match. As more distant relationships are included in matches, the error rate naturally rises. The error rate increase is usually not substantial, but it is measurable in hundredths and tenths of a percent.
RECOMMENDATIONS
RESIDENTIAL LISTS: While additional accuracy can be achieved if only close relationships are considered, with residential lists, the margin of error rate increase is almost always very small even when the more distant relationships are included—rarely more than 0.02% in our testing. Therefore, under best practices, it is fully acceptable to use all RELFLAG relationships when matching residential lists. With the exception of the George Foreman family, most errors that might occur result from different given name that share the same nickname or other variation.
BUSINESS & ORGANIZATION LISTS: On the other hand, with business and organization lists, when the more distant relationships are included the margin of error rate increase is typically higher, compared to residential lists. However, our testing normally shows an increase that is still less than 0.1%, but we have seen it as high as 0.3% with some large lists. Under best practices, it is recommended that only close relationships be considered when processing business and organization lists.
Keep Database Content Consistent
Posted by: | CommentsDatabases and spreadsheets are great for organizing information, but to get the most out of them, content must be entered in a consistent manner. Too often data entry results in a mishmash of styles and formatting. For example, one record may have the name entered first name first while the next has last name first. Or some telephone numbers may be entered with parenthesis delimiting the area code while others employ a hyphen or slash. Etc.
It is important for database managers, usually in conjunction with interested associates, to develop rules for entering information into their tables. Inconsistencies make it difficult filter, sort, query, merge and process the database records and can even lead to data corruption and security vulnerabilities. Data entry rules can take the form of a written style guide, preprogrammed validation routines or a combination of both.
STYLE GUIDE
A style guide (sometimes called a style manual) is a written set of standards for entering content. It is meant to foster uniformity in style and formatting by providing rules indicating how each data element is to be handled. These elements can include names, addresses, telephone numbers, dates of birth and any other fields and field sets that would benefit from constancy. Below is an example of part of a style guide:
| Data Element | Style Requirements | Example |
|---|---|---|
| Customer name | Include. Enter first name first. Exclude title. Punctuate initials and suffix abbreviations. | George E. Jones, Jr. |
| Home telephone | Include. Format: (###) ###-####. | (800) 609-9231 |
| Fax |
Include, if applicable. Format: (###) ###-####. |
(818) 480-4391 |
| Web address | Include with http://. | http://www.peacockdata.com |
A style guide does not have to be exhaustive, only comprehensive enough to advance sufficient standardization. Depending on the situation, one or two pages is often enough, while other circumstances may require a book-sized document. In any case, style and formatting rules must be clear and readable to be effective, and the inclusion of examples has proven to be extremely helpful for end users.
VALIDATION ROUTINES
In addition to or instead of written guides, style and formatting rules are often enforced with validation routines built directly into the interfaces used for data entry. These make sure information is valid, reasonable and secure while at the same time further reduces the opportunity for user error.
Some validation routines are automated so input is restricted at the time of data entry. The most common of these is an input mask which sets a template or pattern for a field or field set that automatically formats entered content. Input masks are particularly suitable for telephone numbers, ZIP and postal codes, times and dates.
Other validation routines are programs set to run when an end user tries to exit a field or save a record. These are similar to (or often exactly like) scripts that check content entered in web forms. Some routines are simple and may only guard against invalid characters and missing information, while others may be very complex and even check spelling and grammar.
• • Data entry rules and preprogrammed validation routines are an important part of data management, and taking time to tackle this part of the development process will inevitably reap generous rewards.
How to Zip & Unzip Files
Posted by: | CommentsWe receive and send a lot of files as part of our data processing services, and some of our clients are unfamiliar with how to zip and extract files. The following instructions show how to compress and uncompress a file under the Windows, Mac and Linux operating environments. Note than under most systems you can select multiple files as well as folders/directories to zip into the same archive.
Zipping a file creates a compressed version of the file that is usually considerably smaller than the original file. The zipped version of the file has a .zip file extension.
Unzipping a file reverses the zip process and extracts the file from the compressed archive.
IMPORTANT: Please be cautious about opening .zip files from unknown e-mail senders because they can contain viruses. Confirm with known senders before opening a .zip file.
WINDOWS ME, XP, SERVER 2003, VISTA, SERVER 2008 and 7
Compress files (zip files)
- Locate the file you want to compress.
- Right-click the file; then point to Send to and click Compressed (zipped) folder.
A new compressed zip file is created in the same location.
Uncompress files (unzip files)
- Locate the compressed zip file you want to extract.
- Do one of the following:
- To extract a single file or folder, double-click the zip file to open it; then drag the file or folder from the archive to a new location.
- To extract the entire contents of the zip file, right-click the zip file; then click Extract All; then follow the instructions on the screen.
Windows 95, 98, 98SE and 2000 do not have built in zip file support, and it is necessary to utilize third-party software to create and extract zip files.
MAC OS X (10–10.4)
Compress files (zip files)
- Use Finder to locate the file you want to compress.
- Control-click or right-click the file icon; then click Compress [. . .]
A new compressed zip file is created in the same location.
Uncompress files (unzip files)
- Use Finder to locate the compressed zip file you want to extract.
- Double-click the file icon.
The files contained in the archive will be extracted to the same location.
MAC OS X (10.5–SNOW LEOPARD)
Compress files (zip files)
- Use Finder to locate the file you want to compress.
- Control-click or right-click the file icon; then click Create Archive of [. . .]
A new compressed zip file is created in the same location.
Uncompress files (unzip files)
- Use Finder to locate the compressed zip file you want to extract.
- Double-click the file icon.
The files contained in the archive will be extracted to the same location.
LINUX
Compress files (zip files)
- Open a shell prompt.
- Enter the following:
zip -r filename.zip filedirA new compressed zip file is created in the selected location.
Uncompress files (unzip files)
- Open a shell prompt.
- Enter the following:
unzip filename.zipThe files contained in the archive will be extracted to the same location as the zip file.
Regularly Review Database Systems
Posted by: | Comments
Maintaining a successful database infrastructure requires regular review to establish what is going right and where problems may be lurking. This should consist of close consideration of all aspects of the database system, including: hardware & software, administration & input, documentation, staff & training and disaster recovery.
The frequency of database system reviews is dependent on the size of a company or organization and the complexity of the system. At a minimum, we recommend quarterly reviews, but many situations warrant more frequent action.
Prior to conducting any database system review it is important to establish a set of benchmarks and checklists to compare against the findings.
HARDWARE & SOFTWARE: A database system review should begin with an assessment of the applications, computers, workstations, network servers and other devices that underlie and run the system. Decide if they are meeting the expectations and requirements of end users and if they are doing so efficiently. Needs change and technologies grow rapidly, and keeping on top of the machinery is essential in a competitive environment.
ADMINISTRATION & INPUT: Take a long look at how the database system is administered and the input into the tables. Determine if there has been unauthorized augmentation of a database, particularly rogue changes, or if unapproved or non-standard practices and tools are utilized. This will help maintain the system’s physical and logical models as well as prevent costly downtime and gaps in performance.
DOCUMENTATION: The fun often stops for tech people when they have to hang up their programming tools and put their work down on paper. Consequently, documentation is often lacking for database systems. Make sure this is not the case because end use of the system is highly affected. Documentation should include a data dictionary and reflect the current physical and logical state of the infrastructure as well as be understandable to the less tech savvy.
STAFF & TRAINING: The people part of the review is very important because a database system is only as good as those that run and use it. It is important to align duties properly, and the staff needs to have the necessary expertise and training to adequately leverage the technology and be equipped to handle new complexities in the infrastructure. Investment in this area can reap large rewards.
DISASTER RECOVERY: Last but definitely not least, asses the database system in terms of its ability to recover from a disaster. Backups need to be performed regularly and properly stored, and it is vital this includes offsite backup. Additionally, make sure there is an adequate plan for unforeseen complications and worst-case scenarios and that the system’s immunization against viruses, worms and other web-based attacks is at full strength. This is particularly important when there are substantial changes to the database infrastructure.
• • Database management can become overwhelming as requirements escalate and the volume of data mushrooms. Regular review of a database system is essential to preserve the return on investment, meet objectives and insure long-term success.
Be Kind—Don’t Pollute Telephone Fields
Posted by: | CommentsOne of the most exasperating things about processing telephone data is all the junk that often gets added next to the numbers. Little notes like “cell”, “parent’s phone”, “call before 8 p.m.” and “disconnected” can wreak havoc when the information is processed with telephone update services or sent through merge/purge, as well as when utilized in-house.
While here at Peacock Data we clean up telephone fields before processing, many service provides do not. Having the extra information included is particularly destructive when trying to verify telephone numbers or running a reverse append. Often these numbers are flagged as invalid and not properly processed.
Merge/purge can also be harmfully affected if telephone information is used in the match criteria and matches are missed due to the “database pollution”.
The little notes can be a problem in-house as well. They show up when printing telephone lists and when supplying data to your call center. And they can also cause issues when performing queries on the tables.
If notes about telephone numbers are necessary, a separate field should be provided and utilized by the end user. And if the problem persists, database managers can limit the size of the telephone fields so there is not enough room for the notes.
Most importantly, lessons in database etiquette, including a list of do’s and don’ts, should be included in training for anyone who accesses the database tables.
A company or organization’s data resources are among its most important assets. Following a few simple rules when accessing them can greatly help maintain their value and extend their effectiveness.
Be kind—don’t pollute!
Restructuring the pdNickname Database
Posted by: | CommentsAn alternative structure for pdNickname is to have one record per name with the variations in fields next to it. This tutorial explains how to do it.
Matching and merging names can be tricky. How do you relate William Smith with Bill Smith? The pdNickname database can be utilized to match names that are dissimilar because one has a given first name while another has a nickname or other variation, or vice versa.
Out of the box pdNickname is structured to allow immediate compatibility with the greatest number of database systems as well as to make it easy to become familiar with.
The nickname database is setup with two names per record. The first name field contains the names you are looking up, and in the second is a variation for each name—nickname, diminutive, given name, variant, etc. The same name can be listed several times in the first field, each time with a different variation. (See Figure 1.)
FIGURE 1: PDNICKNAME OUT OF THE BOX
If the names compared are Alexander Jones and Alex Jones, all names matching Alexander (NAME-A) are scanned until a variation is found that matches Alex (NAME-B). This works well, but there are other ways of organizing pdNickname that could work even better for you. In fact, we have restructured the table for utilization in our own services.
An alternative structure is to have one record per name and the variations in fields next to it. It is not practical to have separate fields for each variation, which can range from one to over two hundred. So what we do is have two Memo fields (also known as Long Text), one for close variations (relflag = "1") and the other for more distant variations (relflag = "2"), with the string of variations separated by delimiters for easier matching. (See Figure 2.)
FIGURE 2: PDNICKNAME RESTRUCTURED
Note: when browsing a table, normally you cannot see the content of a Memo or Long Text field because the database keeps it in a separate file. For this screenshot we have made the content visible.
Structured this way, when your program finds a match for NAME-A, it then determines if NAME-B can be found in variation field one or variation field two. This can be faster because you only access one record in each search request. The code sample below is an example in Visual FoxPro that illustrates this. Of course other programs use different commands and syntax to achieve the same outcome.
* CODE SAMPLE *- this Visual FoxPro function receives as parameters *- the two first names being compared - it returns a *- variable indicating what matches are found - this *- function is based on the restructuring of the *- pdNickname database described in this tutorial FUNCTION pdNickname LPARAMETERS cNameA, cNameB LOCAL nMatch IF NOT USED("nicknames") USE nicknames ALIAS nicknames IN 0 ENDIF cNameA = PADR(UPPER(ALLTRIM(cNameA)),25," ") cNameB = "/"+UPPER(ALLTRIM(cNameB))+"/" nMatch = 0 IF SEEK (cNameA, "nicknames", "name") DO CASE CASE OCCURS(cNameB, nicknames.variations) > 0 nMatch = 1 CASE OCCURS(cNameB, nicknames.var2) > 0 nMatch = 2 ENDCASE ENDIF RETURN nMatch
pdNickname, like all our Database Products, are structured to satisfy most users from the start. But there are many ways to integrate the databases into your system. It is up to you to determine what works best for you. Do not be afraid to experiment.
Start at the Finish Line
Posted by: | CommentsWhen planning a new data management projects, results will be better, costs lower and headaches lessened if you consider what your objectives are before you begin. Always start by thinking about what you want to do with your data both now and down the road.
The worst kind of database system is one put together piecemeal as new demands arise. At some point it becomes more of a hindrance that a help. Many become monsters that seem to have a life of their own.
Before you design your databases, tables and user interfaces and decide on purchases, consider all the kinds of data you want to track and the best and most resourceful way of doing so. But to do this you need to gather some information first.
Talk to the end users who will utilize your data. Find out what they need and how they will be using it. Just as important, determine what they would like to do in the future and what has frustrated them most about data in the past.
Also talk to those who will be entering information and the techs who will be working directly with the database system. Find out what will make them more efficient and what has previously held them back.
Finally talk to the vendors who will be processing your data and supplying equipment and third-party lists. Ask them what you can do to help them achieve the best results for you and reduce costs without sacrificing quality.
All of this will affect what tables you design, what fields they will contain, what relationships there will be between them and how end-users will access the information. It will also affect what equipment and lists you acquire, when you buy them and who you hire to make it all work.
Your new data management project should not be planned until after gathering the insight needed to establish what the end results should be. Once you have seen the view from the finish line, you will be much better equipped to create a database system that will get you where you want to be days, months and years from now.
