FileManger Primer – Adding Lookups

FileManger Primer – Adding Lookups

We have a number of videos, articles, example projects, you name it covering the FileManager Classes. What I will be attempting to do with this series of blog articles is breaking down some of the features of the Filemanager Classes include bite size chunks. This is intended for folks that have already made it past the basics of creating you record and manager classes.

We are using the wxDemov25 project for this series of articles, you can get the project from my dropbox or you can download it from our public SCM. Info on our public SCM can be found here. You will also need a backup of the HFSQL database which you can also get from my dropbox.

Our database for this demo is a fairly simplified Order entry system with 4 tables: Customer, Product, OrderHeader and OrderDetail. The record and manager classes have already been created and the connection setup code for the database is already in the project code. I can do a tutorial on that in the future if I get enough request, but like I said we have lots of videos, etc. on that already.

So todays lesson is performing a lookup, “FileManager Style”. We start with a browse that is already using the Manager Class OrderHeaderManager. Two lines of code in window global to define an instance of the OrderHeaderManager class and to populate it.

And a table control use a variable as the source, our manager instance. And the stored value is the entire record. Again this is all stuff covered in lots of our videos, articles etc. The part that requires the most understand is that the stored value is an entire record instance, not a single variable, which lets us do all kinds of handy things.

At this point we have a browse showing the Order #, Customer #, Order Date and Total Amount of the order.

Most of our users don’t have every customer number memorized and I suspect neither to do yours. So we would like to show Customer name instead of number.

The normal way to do that will your table is file based would be to create a Multi-File link column. And everywhere you want to show Customer Name instead of Customer Number you would need to do the same, or something similar to fetch the Customer record. Not a big deal but it does lead to a little bit of code here, there, everywhere. And every line of code is an opportunity for error.

The “FileManager” way of doing this is code it once in the class and then use it everywhere. This is the most basic/standard thing we do with the FileManager classes, and we use it all the time.

Step 1 – Include Customer Name in the OrderHeader record class

I simply opened the Customer record class, copied the CompanyName declaration, and pasted it into my OrderHeader record class. I like to place a comment “From Other Tables” so I know where my real record declaration ends, which comes in handy down the road when you add a field to OrderHeader, etc.

Step 2 – Retrieve Customer Name as part of FetchData in the OrderHeader manager class

The first thing you will notice is FetchData doesn’t “exist” in your OrderHeaderManager class. Well technically it does, because it inherits all the methods from the wxManagerClass. So by declaring the exact same method in our class, we are able to override the base method from wxManagerClass.

First I use JoinFieldList to add to the Select statement that the FileManager class is going to build. Notice the preceding comma, since there is already part of a select statement being built we need to include the comma for the SQL to be formed correctly.

Pro Tip: Notice I used the as CompanyName SQL syntax. Technically I don’t need to use that since the fields in our record class will be populated by FetchData based on matching names. I prefer to do it for clarities sake, but also because I often change the name for various reason. For example in many databases the variable name might just be name. Now suppose I want to include both Customer and Shipper Name in my OrderHeader record. Well I simple name the fields as CustomerName and ShipperName in the OrderHeader record class, then in the FetchData it would be something like Customer.Name as CustomerName,Shipper.Name as ShipperName.

The next line of code add the Outer Join Statement so that Customer is part of our SQL statement. Nothing fancy here and if you speak “SQL” its pretty straightforward. If you don’t I suggest doing yourself a favor and going through a few online tutorials there are hundreds of free ones online.

Pro Tip: Some of the folks starting with the classes seem to get hung up on how the SQL is built in the class or for that matter how any of the methods in the class work. Remember the wxManagerClass is 100% code, so you are able to look at it for reference but even more importantly, you can set a debug even and step through the code and see exactly what it is doing. As you can see in the screenshot below, I set a debug point in the wxManagerClass method and can review exactly how the resulting SQL statement was assembled.

The file line of code (Line 11) calls the base method from the wxManagerClass to it executes as normal. Depending on where we place this line of code we could execute custom code before the base method as we have here, after the base method by calling the ancestor first, or even completely override the base method by removing the ancestor call all together.

Step 3 – Retrieve Customer Name as part of LoadSecondaryFields in the OrderHeader manager class

I have a mantra that most of my mentoring clients can attest to… “FetchData is for browses, and LoadSecondaryFields is for forms”. What it means is that there are two places that you need to add code when you add lookup fields to your record class. FetchData is for record sets, when you load a group of records. LoadSecondarFields is for individual records, when you do a ManagerClass.RefreshByID(#), LoadSecondaryFields is called as part of that process, so that you can add code to populate any lookups, etc.

First those of you that are just venturing out from Hxxxx commands will take comfort in seeing that we can and do use Hxxxx comands in the FileManager classes. In fact the whole point of the classes is that they are just a fancy array manager on top of the code you would normal use. Almost all the code you would normally write for standard HF file access exist somewhere in the FileManager as well.

What we are doing there is if the CustomerID (in the OrderHeader) record has a value then we use it to lookup the customer record. If the record is found we populate the CompanyName field in the OrderHeader record, if it isn’t found, or didn’t have a value then we set it to an empty string. At this point in the wxManagerClass rec has already been populated with the correct OrderHeader record, how do you know this? Well Uncle Pete said so 🙂 or you could step through the Manager Class code yourself and see it!

Pro Tip: One downside to coding in this method is that Rec is dynamically declared which means that intellisense doesn’t help us with field names. Typing rec. doesn’t popup a list of valid fields, which means you have to be sure to get your field names correct, and you won’t know if you didn’t until you hit the line of code at runtime, there will not be a compile time error. I have a work around for that I use, especially if I have quite a bit of code in LoadSecondaryFields. I add a line of code at the top like so:

Notice the comment, it is very important that you comment this line out before running/testing your application as it won’t work right with it in place, since it will clear the prepopulated record. But it does allow intellisense to help you at design time.

Step 4 – Add the new field to the browse

This really doesn’t count as a programming step does it 🙂 As you see now our CompanyName field is available to be linked so we can link it on our browse. Easy Peasy!

And our browse has definitely gotten more useful

One thought on “FileManger Primer – Adding Lookups

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s