Tod means Fox

Supporting decisions through sound data management

Archive for July, 2007

5 Ways to Automate Development in FoxPro

Jul 30

Posted by Tod McKenna in Software Development

I enjoyed putting together my last top seven list so much, that I decided to make another, more targeted list. This one, lists five ways that you can automate software development in Visual FoxPro. To clarify: this article is not about FoxPro automation! But rather, five tips on how you can get the development environment to work for you.

It surprises me how many FoxPro developers I know do not automate, even though the Fox team at Microsoft has given us a lot of tools to do so. As I mentioned in my last article, automation is one of the keys to better productivity. The more tasks you can do automatically, without thought and with little effort, the greater productivity you will enjoy! On top of that, by automation, we can eliminate a lot of thinking, planning, repetition, and needless toiling over the most mundane tasks.

Not every version of FoxPro features every item in my list (although, VFP9 gets them all). The point of this article is not to be exhaustive or to painstakingly document every feature’s introduction into the product; but rather, to get you thinking about automation to improve your production.

Without further ado:

1.) Use Macros

No, no. Not macro substitution! Macros from the Tools menu! FoxPro Macros are powerful little scripts that you can initiate with a mere key combination (like ALT+CTRL+A). You can write a macro to do almost anything. Their primary purpose in life is to automate keystrokes. For example, I have a whole set of macros that open various projects, sets system defaults, and closes/opens databases as necessary. A nice thing about FoxPro Macros is that it will record your keystrokes for you. To start, simply go to Tools / Macros; Click Record; enter the keystroke combination and macro name; and start typing in the command window. When done, click back on Tools / Macros and click OK to stop recording. Next time you want to run the sequence you just created, simply do the keystroke you defined!

Please note: chances are good that you’ll need to tweak the generated code, but don’t fret. In my experience, this consists of adding {ENTER} at the end of a command, or perhaps cleaning up some automatically-inserted values from IntelliSense. No big deal.

2.) Utilize and Customize IntelliSense

Speaking of IntelliSense! As most of you are aware, IntelliSense is a form of automated auto-completion of keywords, class names and methods, parameter definitions, _VFP and _SCREEN system variables, ActiveX controls, COM servers, and the like. But you can also add your own records in the IntelliSense database! You might want to define common enumerated values (like a long list of DEFINES you have tucked away in an include file), custom class definitions, and type libraries. You can also add IntelliSense support for registered type libraries, user-defined types, members, and code elements, enumerated values, and custom classes. IntelliSense is a powerful and easy to manipulate tool that can save you tremendous amounts of time during development.

There is already a great repository of custom IntelliSense scripts for you to browse through and incorporate into your FoxPro programs (hey, did you already forget how selfless the Fox community is?): find them over at the FoxPro Wiki. Andy Kramek also wrote (part 1, part 2) a very informative blog entry about the subject some time ago.

3.) Use Project Hooks

Project hooking allows you to manipulate your project and contained files programmatically. I find this incredibly helpful during builds (tapping into BeforeBuild and AfterBuild, for example), but there are dozens of great uses for hooking into your project. One of my favorite things to do is to log my project builds in a build table. This allows me to monitor and audit the build process (and produce some interesting metrics such as build time, number of builds, etc.).

If you’re at all interested in pursuing this any further, I highly recommend you look into White Light Computing, Inc’s Project Builder and ProjectHook. On that page, there is a very good Whitepaper detailing the tool (and a screenshot of the UI). Best part is, it’s free and developed by one of the community’s best.

The bottom line is that you can automate a ton of activities using these hooks. You can perform backups, make copies, test integrity, check for updates, copy builds to remote locations, etc. The limit is your imagination (for the most part!).

4.) Customize Your Toolbox

I love the Toolbox in VFP9 — especially because I’m used to developing in C#.Net and SSIS. The Toolbox (available form the Tools menu) to me is an intuitive piece of the IDE, and fits nicely into an automated work environment. It is divided into sections which you can customize. These sections are full of collections of various tools. These tools are typically classes, text scraps, Active X controls, and can even hold tables, images, reports, labels, and forms. You can drag and drop items from the toolbox into the command window, a program file, or onto a form. For automation, you can create lots of very cool text scraps (like comment headers) to save you some type and formatting time while developing.

To dig into the toolbox, and customize it fully, right click anywhere on the control and select ‘Customize Toolbox’. You can adjust various behaviors and add and remove items from the different categories. Note: to dock in VFP9, first set the ‘always on top’ property to false. Then, you can dock the toolbox anywhere your heart desires!

5.) Automated Testing

Although I have not had any success with commercial automated testing products (the last one I tried was Borland’s SilkTest, which did not work well with VFP9), I have had mild success using the Automated Test Harness that ships with VFP. The harness gives you the ability to create and run various scripts that will play back mouse and keyboard events — essentially running various parts of your application for you. I admit that even this tool isn’t the best, but you can automate enough tasks and perform enough tests automatically to save you time and effort on regression. The harness taps into Microsoft Active Accessibility (MSAA) technology. To run, type DO (HOME() + “toolstestaatest”). It takes a bit getting used to, but in an afternoon, you can have the tool up and running, testing your application automatically. As an added bonus, the harness allows you turn coverage profiling on and off.

…

Well, that’s that! I hope you find these five ways to automate development in FoxPro useful. Assuredly, there are others. Feel free to comment and post articles, tips, or other feedback to round my list out!

Tags: , , ,

8 Comments

7 Productivity Tips for Better Software Development

Jul 24

Posted by Tod McKenna in Software Development

I’ve always wanted to make a ‘Top 7′ list.

Most of our time is wasted away throughout the day on trivial and unimportant tasks. Some of this is busy work that we create, while other things creep in unexpectedly. Here is a list of seven things that I feel will increase your productivity during a typical workday. Let me know if you think I’m missing or over-emphasizing something. I’m always looking for ways to improve my effectiveness and efficiency; to do more with less.

1.) Find your most productive time
Some developers prefer to work through the night. Most CEOs start their day before 6am. Find your sweet spot and plan your day around this block of time. From what I’ve read, this block of time varies from 4 to 7 good hours. Mine happens to be from about 5am through to 11am. After this, my concentration wanes and my patience thins (until I get home from work, at which time I’m re-energized).

2.) Identify critical tasks
List your most critical tasks for the day, and do them first. Critical does not mean most difficult. Critical tasks may be the easiest and quickest tasks of your day. Perhaps it’s as simple as calling back an important client, or a more difficult task such as finally fixing that nasty bug that’s hard to reproduce. I find that making this list the day before helps me to get started the next day much quicker.

3.) Avoid distractions
Distractions can really lead you astray and spell disaster for your carefully planned schedule. Avoid these things, especially when in your most productive block of time, and certainly while you are completing your critical tasks. Because I spend a large part of my workday in a cubical, and cubes are distraction magnets to begin with, I’m often inundated with visitors who have come by for various reasons (few of them have any relevance to my current tasks). To help combat this, I will often put headphones on to deter all but the most important guest.

I consider mail (e-, voice-, and snail-) a distraction. Very few actual mail items require my immediate attention throughout the week. I suspect you’re in the same boat. So, Manage your mail wisely. Set aside specific time for reading and responding to mail in all its forms. This is especially good advice for email. Never look at email during your most productive time. And please, turn off the automatic notification and get rid of that envelop icon that Outlook puts in your tray. Every email that hits your Inbox is a potential detour. To combat this, I try to tackle email first thing in the morning (5am), with 2 additional checks throughout the day (10am, 3pm-ish). I will only open Outlook during these times.

4.) Automate as much as possible
Automation is the key to more productivity. The more you can automate, the more time you can spend on important tasks. For years I had been manually making additional ’sanity’ backups of my source code (above and beyond VSS and normal backups). Finally, I woke up and realized that this (and many other things) can be automated. You can be fancy and schedule a backup through some backup software, or be simple and create some .bat files to do the heavy lifting. Sounds simple enough. This is just one example. There are potentially hundreds of tasks you can automate.

5.) Apply Pareto’s Law
Otherwise known as the 80/20 principal (or rule), this law states that 80% of the effects comes from 20% of the causes. The vital many verse the trivial few. Learning how this works can be like hiring a new employee (a better you) and firing that resource hog (the old you). Find out what busy work you’re doing and eliminate it. Identify the work you do that produces the best results, and capitalize.

6.) No more multitasking
Despite popular belief, multitasking breaks concentration, causes distraction, and inevitably costs you time and money. This is especially true of technical work, like software development. Shutdown your Outlook, your Accounting software, your Internet Browser, your News Aggregators (you can leave mine on, though ;-)), etc. Focus on the task at hand, complete it, and move on.

7.) Ergonomics are your friend
Get comfortable and put a greater focus on your work environment. It should be healthy, comfortable, and safe. Stand up once in a while to stretch and relax your hands, wrists, and neck. You can loosen the kung fu grip on your mouse, flatten your feet on the floor, adjust the top of the monitor to be at eye level, and do many other things to improve your condition. I’m terrible at all of this: I seldom get up, I sit Indian-style, and my dual monitors are angled and a little high. But I have seen others recover from this and start on the path of a more ergonomic-enriched existence. Being comfortable helps you to be productive.

Do you have productivity advice? Is there anything missing form my list?

Tags: , ,

6 Comments

Parsing firstname / middlename / lastname

Jul 20

Posted by Tod McKenna in Software Development

There are many ways to parse or tokenize data in VFP. I like using STREXTRACT and GETWORDNUM for example. But there are cases in which the actual parsing needs to be a bit smarter than what these common methods offer. Take a concatenated name field for example. Common to many legacy (and unfortunately, some modern) systems, is a ‘name’ or ‘address1′ field that stores a person or company’s full name. This method is not looked upon favorably by data integrators or people who like atomic, normalized data (like me!). These types of fields don’t work well with indexes either.

Of course, the design solution is to create 5 or more fields that represent a person’s name: prefix / first / middle / last / suffix. These atomic values can then be arranged as needed (”last, first middle”; “first last”, initials only, etc.) in reports and for display. But oftentimes, making this decision now is too late.

An alternative is to write a smart parser that can place each name element into its appropriate box. In the following code example, I provide a solution that can handle this tasks. My only assumption for this code is that the name is stored in prefix / first / middle / last / suffix order. By data profiling, you can determine how true and consistent this assumption is on your data. You may discover that 95% of the file is stored in lastname / firstname order instead. Whatever the case may be, you can use the method below as a starting point.

The following snippet gets us started, and in the end, loops through the names and prints the parsed results to screen:

#DEFINE PREFIX  1
#DEFINE FIRSTNAME  2
#DEFINE MIDDLENAME  3
#DEFINE SURNAME  4
#DEFINE SUFFIX  5
 
CLEAR
 
*-- some names to demonstrate
DIMENSION aMyNames[13]
aMyNames[1] = "Andrew MacNeill"
aMyNames[2] = "Andy Kramek"
aMyNames[3] = "Bernard Bout"
aMyNames[4] = "Calvin Hsia"
aMyNames[5] = "Cesar Chalom"
aMyNames[6] = "Craig Baily"
aMyNames[7] = "Craig Berntson"
aMyNames[8] = "Emerson Santon Reed"
aMyNames[9] = "Eric den Doop"
aMyNames[10] = "gonzomaximus"
aMyNames[11] = "Tod J. McKenna II"
aMyNames[12] = "Dr. Ralph Kimball"
aMyNames[13] = "Miss Lucy Jones"
 
*-- placeholder for tokenized name, passed by reference into split_name
DIMENSION aParts[1]
 
FOR x = 1 TO 13
  cLastFirst = split_name(aMyNames[x], @aParts)
  ? cLastFirst
  ? "firstname: " + aParts[FIRSTNAME] + CHR(9)
  ?? "lastname: " + aParts[SURNAME]
NEXT

Here’s the code in function “split_name”

FUNCTION split_name
LPARAMETERS tcName , taParts
 
*-- variables and arrays I will use
LOCAL cPart AS Character
LOCAL x , nTotParts AS Integer
LOCAL lIsSuffix , lIsPrefix AS Logical
LOCAL ARRAY aTemp[1]
EXTERNAL ARRAY taParts
 
*-- get the data from tcName into an array for easy looping
tcName = STRTRAN(UPPER(ALLT(tcName))," ",CHR(13))
DIMENSION taParts[5]
STORE "" TO taParts
DIMENSION aTemp[1]
FOR x = 1 TO MEMLINES(tcName)
  cPart = ALLTRIM(MLINE(tcName,x))
  IF !EMPTY(cPart)
    aTemp[ALEN(aTemp,1)] = cPart
    IF x < MEMLINES(tcName)
      DIMENSION aTemp[ALEN(aTemp,1)+1]
    ENDIF
  ENDIF
NEXT
 
*-- now loop through temp array and put each name segment into a smart little box
nTotParts = ALEN(aTemp,1)
FOR x = 1 TO nTotParts
  IF VARTYPE(aTemp[x])!="C"
    LOOP
  ENDIF
  *-- get rid of any periods used in the part, and convert to uppercase
  cPart = STRTRAN(ALLTRIM(UPPER(aTemp[x])),".","")
  IF nTotParts = 1
    *-- Madonna? gonzomaximus? Treat single names as a lastname
    taParts[SURNAME] = cPart
  ELSE
    *-- Note: A data profile on a name file I parsed a few years back revealed
    *-- the following common name prefixes. Beef this list up if you have others!
    lIsPrefix = INLIST(cPart,"MR","MRS","MS","MISS","DR","PROF","SIR",;
                  "MASTER","REV","REVERAND","FATHER","SISTER","BROTHER",;
                  "BR","SIS","ATTORNEY","COL","COLONOL","REP","PRES")
    IF lIsPrefix
      taParts[PREFIX] = cPart
      LOOP
    ENDIF
    *-- Note: The same profile mentioned above revealed the following suffixes
    lIsSuffix = INLIST(cPart,"PHD","II","III","IV","V","JR","SR","ESQ","CPA","CEA","DD","MD","JD")
    IF lIsSuffix
      taParts[SUFFIX] = cPart
      LOOP
    ENDIF
    IF x = nTotParts
      IF EMPTY(taParts[SURNAME])
        taParts[SURNAME] = cPart
      ELSE
        IF EMPTY(taParts[FIRSTNAME])
          taParts[FIRSTNAME] = cPart
        ELSE
          taParts[MIDDLENAME] = cPart
        ENDIF
      ENDIF
    ELSE
      IF EMPTY(taParts[FIRSTNAME])
        taParts[FIRSTNAME] = cPart
      ELSE
        IF EMPTY(aParts[MIDDLENAME])
          taParts[MIDDLENAME] = cPart
        ELSE
          taParts[SURNAME] = cPart
        ENDIF
      ENDIF
    ENDIF
  ENDIF
NEXT
 
RETURN ALLTRIM(ALLTRIM(taParts[SURNAME]) + ", " +;
         ALLTRIM(taParts[FIRSTNAME]) + " " + ;
         ALLTRIM(taParts[MIDDLENAME]) )
ENDFUNC

So there you have it. The code above looks at the tcName variable and attempts to dump its parts into the taParts array. Some defined constants exist to help ease the “where the heck am I in this array” confusions (a practice I use for stuff like this all the time). As an added bonus, the code returns the name in last / first middle order, which is usually a good format for display purposes.

Tags: , , ,

1 Comment

VFP Workshop

Jul 16

Posted by Tod McKenna in Events and Trainings

The Central Kentucky Computer Society is offering a workshop on FoxPro. Has anyone ever attended any of these workshops? They seem to be one of the only groups offering this these days.

Tags: ,

1 Comment

VFP Boot Camp in Kansas City this August

Jul 12

Posted by Tod McKenna in Events and Trainings

I’m just the messenger!

The next VFP Boot Camp will be in Kansas City, Missouri, August 20-22, 2007

Registration ends on August 1, 2007 for the highly-acclaimed VFP Boot Camp. This event delivers three jam-packed days of solid, hands-on learning. From the fundamentals of VFP development through the very latest VFP 9.0 features, this boot camp is designed to quickly get your VFP skills up to speed. Attendees will receive a 500 page manual in both print and electronic format.

For more information visit http://visionpace.com/developereducation.html or email info@visionpace.com. To register call 888-904-7900.

Tags: ,

No Comments

Conforming Temperature in FoxPro (some conversions)

Jul 11

Posted by Tod McKenna in Software Development

Conforming data is an essential part of data integration. Conforming data takes on many shapes and may include updating data types and lengths, attribute names and conventions, data atomicity, universal conversions, etc. The idea is that, in all circumstances, the attribute means (and therefore acts) the same across all entities in the database.

For example, let’s look at temperature. Business requirements (and perhaps your region of operation) will dictate the format temperature should be stored in. When integrating data, chances are great that you’ll have different temperature formats (Celsius and Fahrenheit) coming from different sources. In order to have a conformed database, you create a rule that states that all temperatures are to be stored in X format. I like Celsius, personally, so I convert all Fahrenheit temperatures to Celsius:

nCelsius = (nFahrenheit  - 32) * (5/9)

If you disagree, then go the other way:

nFahrenheit  = (9/5) * nCelsius + 32

Like Kelvin? (You must be a scientist!)

nKelvin = nCelsius + 273.16

There are virtually hundreds of these types of conforming decisions to be made in a typical large integration project. Build yourself a toolbox of common conversions, and apply them to your data as needed.

Tags: , ,

No Comments

Getting Started with Data Profiling

Jul 10

Posted by Tod McKenna in Data Management

In my last post I said I would publish some code that you could use to get started profiling your FoxPro tables. I’ve included the following sample here, as well as in the Download Section for you to use. This example is written as a procedure, but you should consider converting this to objects. You can create patterns and really cut down on the amount of work you do. Although this code is generic, you may in fact need to write modified versions for certain tables in certain environments. OO techniques lend themselves to this nicely.

Anyway, here is how you would call the function listed below:

LOCAL lcSkipList, lcDirectory, lcTableName,;
     lcResultAlias  AS String 
 
lcDirectory = "C:Worktables_to_profile"
lcTableName = 'some_table'
lcResultAlias = 'some_resultset'
 
*-- enter the columns, separated by comma, to skip
lcSkipList = '' 
 
*-- run the data profile
profile_table(lcTableName, lcDirectory, lcResultAlias, lcSkipList)

The following function, ‘profile_table’, is as follows (please note, you will need my get_median function from here):

FUNCTION profile_table
  LPARAMETERS tcTableName, tcDirectory, tcAltName, tcSkipList
 
  LOCAL nFldCount AS Integer
 
  tcAltName = EVL(tcAltName,'profiled')
  tcSkipList = EVL(tcSkipList,'')
  lcCursorName = 'db_' + tcAltName 
 
  TRY
    IF USED(tcAltName)
      USE IN (tcAltName)
    ENDIF
    USE (ADDBS(tcDirectory)+tcTableName) IN 0 ALIAS (tcAltName )
    nFldCount = AFIELDS(aColumns,tcAltName)
  CATCH
    nFldCount = -1
  ENDTRY
 
  IF nFldCount > 0
    IF USED(lcCursorName )
      USE IN (lcCursorName )
    ENDIF
 
    CREATE CURSOR (lcCursorName)(;
			table_name varchar(45)	,;
			fld_name   varchar(45)	,;
			fld_type   char(1)		,;
			fld_size   integer      ,;
			fld_prec   integer      ,;
			fld_null   logical      ,;
			range_low  varchar(60)	,;
			range_high varchar(60)	,;
			domain     memo         ,;
			uniq_cnt   integer      ,;
			empty_cnt  integer      ,;
			full_cnt   integer      ,;
			dele_cnt   integer      ,;
			null_cnt   integer      ,;
			most_freq  varchar(60)  ,;
			data_mean  numeric(20,4),;
			data_var   numeric(20,4),;
			data_std   numeric(20,4),;
			median     numeric(20,4),;
			location   varchar(200) )
 
    FOR n = 1 TO nFldCount 
 
        *-- afield properties
        m.table_name = tcTableName
        m.fld_name = aColumns[n,1]
        m.fld_type = aColumns[n,2]
        m.fld_size = aColumns[n,3]
        m.fld_prec = aColumns[n,4]
        m.fld_null = aColumns[n,5]
 
        ? tcTableName + ' Field ' + m.fld_name
        ? DATETIME()
 
        IF ALLTRIM(LOWER(m.fld_name)) $ ALLTRIM(LOWER(tcSkipList))
          ?? ' skipped'
          LOOP
        ENDIF
 
        m.location = tcDirectory
 
        lcSQL = "SELECT " + m.fld_name + " AS prof_fld FROM " + ;
                        tcAltName + " INTO CURSOR crTmpProf"
        &lcSQL 
 
        *-- note to self, need to test to see if using SQL-Select is more efficient
        SELECT crTmpProf
        CALCULATE MIN(prof_fld) TO m.range_low
        CALCULATE MAX(prof_fld) TO m.range_high
        CALCULATE CNT() FOR EMPTY(prof_fld) TO m.empty_cnt
        CALCULATE CNT() FOR !EMPTY(prof_fld) TO m.full_cnt
        CALCULATE CNT() FOR DELETED() TO m.dele_cnt
        CALCULATE CNT() FOR ISNULL(prof_fld) TO m.null_cnt 
 
        *-- numbers only!
        IF INLIST(m.fld_type,"N","I","Y","B","F")
          CALCULATE AVG(prof_fld) TO m.data_mean
          CALCULATE STD(prof_fld) TO m.data_std
          CALCULATE VAR(prof_fld) TO m.data_var
          m.median = get_median(2,"crTmpProf","prof_fld")
        ELSE
          STORE -1 TO m.data_mean , m.data_std , m.data_var, m.median
        ENDIF
 
        *-- distinct list and domain
        SELECT prof_fld, COUNT(prof_fld) as freq ;
            FROM crTmpProf GROUP BY prof_fld ;
            ORDER BY freq DESC INTO CURSOR crTmpDistProf
 
        SELECT crTmpDistProf
        LOCATE
        m.uniq_cnt = _TALLY
        m.most_freq = crTmpDistProf.prof_fld
        m.domain = ''
        SCAN
          m.domain = m.domain + TRANSFORM(crTmpDistProf.prof_fld) + CHR(13)
        ENDSCAN
 
        SELECT (lcCursorName)
        APPEND BLANK
        GATHER MEMVAR MEMO
 
        ? REPLICATE('+',12)
 
    NEXT
  ENDIF
ENDFUNC

So there you have it. Now you have a table that lists each attribute, with additional, highly useful information. You can use this metadata to conduct index analysis, check for misspellings, verify that lookup codes exist in external tables, verify that numerical data hasn’t deviated too far, etc. This type of metadata is essential to have before you begin any quality or integration project!

Data profiling is a hefty subject — one in which I promise to address again in the near future.

Tags: , , , ,

5 Comments