I’ve written an extension library on top of the West Wind wwBusiness classes. You can read the original post on my work here.  In my enhanced library, which I’ve named wwBusinessPro!, one of the things I’ve added is a lookup method that will handle String ID lookup values in addition to the native handling of Integer PK’s. The base wwBusiness library only works with Integer PK lookups, and I needed an enhancement to work with String lookups to get started with wwBusiness in my app.

When using my wwBusinessPro class, the main method that you call to do a lookup is the Get() method. You will call this method even when doing a Integer PK lookup because it will expose all the other extensions I’ve added to the library (more on that later).

So, the Get() method accepts one parameter which can be an Integer or a String.

    oJob.Get(123) && An Integer PK lookup

     – Or –

    oJob.Get(‘123’) && A String lookup

Now, on each Business Object class that you create, you need to specify the following properties to allow any kind of lookup to work. (This is how the base wwBusiness functions, so this is not a new feature in wwBusinessPro!, except that I have extended it to work with String values too). The cPKField setting is required for all cases, and cLookupField is only required for String-based key values, in which case you must specify the same column names for both properties.

cPKField (This property is required. Choose either and Integer Pk column or a String key value column)

For Integer PK’s set this value to the name of the Integer PK column in the table. (basic wwBusiness feature)

For String PK’s set this value to the name of the String key value column in the table. (new feature)

Note: this key value is what will be used to locate your record when saving changes to existing records. For String key values, be sure you do not use Set Exact Off or else you may update the wrong record based on a partial string match.

cLookupField (optional)

Leave blank if you do not have a String value lookup field. (This is an extension property that I added to handle String lookups.)

For example, in an oJob class, you would set the cLookupField to the name of the column that has the string lookup key in it. For my Job table, the cLookupField property is set to ‘job_num’ which is a string column. Now it knows to look for the string value ‘123’ in the ‘job_num’ column. I also have Integer PK’s on that same table, so I also set the cPKField property to ‘ipkey’. Now my processing can also be done by the PK Integer value, if I know it. So I can now do lookups either by Strings or Integer PKs.

How it works:

The Get() method will determine what kind of lookup you are trying to do based on the data type of the value you pass in (integer or string) and will make the correct method call to other methods to do the actual lookup, all of which eventually drill down into the base wwBusiness class to do the lookup. Just pass in a string or an integer and it will happen like magic. You can then check the oJob.lFound property to determine if the requested record was found.

Here’s the code for the Get() method:

More about string lookups:

For string lookups, if you need more flexibility on the cLookupField, you can bypass the Get() method and use a lower-level lookup that accepts an alternate lookup column. It’s called LoadByRef() and accepts a string lookup value and a column name in which to perform the lookup. Like this:

oJob.LoadByRef(‘53443’, ‘po_num’) && Load a job by the po number.

And, to take it one step further, there is one more way you can further control the lookup, and that is by setting a property called cAdditionalFilter. Rather than accepting it as a parameter into the LoadByRef() method, it works as a property that you set before calling the method. This powerful feature allows, for example, find the first record with Status=”A” for Customer “DEN001”. You will just have to be creative and explore all the ways this method can be used. For DBF mode, the base wwBusiness method uses the LOCATE command, so the lookup is from the top down, so it will find the first record. For Sql Server mode, you can even include an Order By clause at the end of the cAddtionalFilter to all effectively working from the bottom up.

Example:

    oJob.cAdditonalFilter = ‘Status = [A]’
    oJob.LoadByRef(‘DEN001’, ‘cust_num’)

You can use any complex expression you want to in the cAdditionalFilter property:

    oJob.cAdditonalFilter = ‘Status = [A] and Amount>1000’

Remember, in my methods, I am still eventually using the base wwBusiness methods to do the work, it’s just that I have built a wrapper around it to make it easier to use.

Here’s the method code for LoadByRef()