Out of the box Clarion code performs a lookup in another file to get a value, usually what is known as a “System ID” in generic terms. This value is numeric and always unique, more likely than not an auto-incrementing value.
A good data design links two files by a common ID. However, this is horrible for a user interface since “Vendor 48” is meaningless. The user interface should use the vendor name, since all of the vendor’s customers know them by name. A vendor ID is also completely arbitrary. Its only meaning is inside a database.
A link between a product and its vendor is required. The database understands the unique identifying number, but not users. How does a developer resolve these two conflicting points of view? The database must have these IDs, so the lesser of two evils is to force the ID numbers on a user.
The Solution in Edit Form Procedures
A good interface is to work the user’s strength – the vendor name. The vendor name is on all the invoices, letters, emails, etc. The idea is to tie a Product to a Vendor by name, but use the Vendor ID in the Vendor and Product file. In other words, the database is happy and the user is happy.
Make the Product update procedure in Clarion the way you usually do. If you defined the Product file in the dictionary correctly, you would have instructed it to not populate the Vendor ID field:
This prevents any Clarion wizard putting this field in any procedure they create. Instead, you populate a local variable that represents the Vendor name, using one of the string types. This is what you populate on the update form and where the auto-complete magic happens.
First, you need to do two things:
- Make sure the window has the AutoDisplay attribute turned on.
- In the local vendor name variable, turn on the IMM attribute.
If either of the above items are missing, auto-complete will not work as expected. Adding the IMM attribute give you a new embed point, NewSelection as well as making the ABC method TakeNewSelection available.
In TakeNewSelection() add a variable as shown below:
OffSet is used in the code for the auto-complete. This code may be placed in the control’s NewSelection embed which places it after the parent call in TakeNewSelection() method:
What this code does is that for every keystroke entered; perform a lookup for a Vendor, by Vendor Name. It will always get the closest match, which may not always be the vendor you want. If you have two or more vendors with similar names, it will find the first based on the value entered so far.
If you have two vendors with similar names for example, “Brown” and “Brownstone”, entering “B” will find “Brown” and place it in the vendor field. Note that your cursor position does not change. If you are looking for “Brownstone”, then you need to enter “rowns” from the point of the cursor to have “Brownstone” placed in the entry for you.
All you need to do to accept the value is press tab to advance to the next entry. This triggers the control’s Accepted event. This is where you need to get the Vendor ID from the vendor file and ensure it is in the Product’s vendor ID field:
Once you press the Save or OK button, the Product record is written to disk.
Since the entry for the vendor is a local variable, you need a way to populate it with the name of the Vendor when you open the Product update window. One line of code is all you need in ThisWindow.Init():
The above was done by adding a formula of Procedure Setup class:
The Solution in Edit-In-Place Lists
Sometimes, a record structure is small, so an edit form makes little sense. Too many windows open on the screen and you need to keep things simple. That is just good interface design. Clarion has the ability to edit a browse list. No form opens to add, change or delete data. You do editing directly inside a list control.
While we are at it, let’s make the data design a bit more complicated. In an invoicing system, the items are not always known by their description, more their part numbers. That is how you would order a part for your car, since there could be many types of carburetors and distributor caps. Descriptions are vague, duplicated many times so not too practical to narrow down precisely what you want. However, part numbers are unique, and these part numbers tend to be alphanumeric. The chances of finding exactly which part you need are good.
When you populate a browse control template on a window, the ABC templates derive a default name for the browse object, something like BRW1. The next one (if there are more than one) would be BRW2 and so on. Technically, this will compile and work. However, BRW1 is quite meaningless. How does that name communicate you are working with the invoice detail file? Instead, I rename the default classes to better reflect what it is I’m working on. This just makes your life easier when you start adding embed code:
The above it not required for edit in place, just makes it easier. The next step is to add the edit control template, which places three buttons labeled Insert, Change and Delete. The Action dialog for them is as follows:
Since edit in place is the method to edit data for this file, the update procedure entry is left blank. Each column in the list box is really its own class, with methods. With the simple EIP dialogs or the more advanced dialogs (set up in the global classes for the application), you do get new embed spots. The default behavior for EIP is functional and works out of the box. But auto-complete is not part of the base design. This is the embeds for this single column in the Invoice Detail list box:
This is the code for the first embed, the Init() method:
Like our edit form in the first part, the IMM attribute must be turned on. SELF.FEQ is a property of the class defined as “whatever the field equate label is”. The code also forces all entries to be upper case. If you used the advanced dialogs, neither of the above is needed since you can turn on these properties there.
The templates generate a QUEUE structure based on your list definition. They also generate a property in the InvDetList class; Q which is a reference to the generated QUEUE structure. Unlike the edit form, EIP classes don’t generate a TakeNewSelection method, but there is a TakeEvent() method. Declare the Offset variable as shown:
Now for the code to perform the autocomplete. Its similar but different:
This time, a CASE EVENT() structure is required. In the case of EVENT:NewSelection, write the contents of the control to its USE variable. Compute the current OffSet as before. But unlike the entry control, the code needs to determine what is in the QUEUE field, InvDetList.Q.IND:ItemNumber. A lookup by ItemNumber if then performed and compare the results of the lookup against what is entered. If the lookup fails or what is entered does not match, then allow what is entered to remain. Otherwise, get the Item Number as stuff it in the QUEUE variable, then compute the next OffSet in case the user types in another character. If the OffSet is zero, then clear out everything.
Once the item number (part number) fills that the user likes, then tab to the next column to fill out the quantity and other columns (if needed).
This shows two ways to perform an auto-complete, without compromising data integrity and without exposing meaningless ID numbers to a user. I can think of another way to perform an auto-complete, but this time using a drop down list. That is quite useful especially in cases where many similar lookup values may exist. The Clarion Handy Tools provides a code template that does just that. So those with the CHT toolkit have this ability.