by Finian Lennon
This article has two parts. The first is a description of how I set up a system to configure our commercial application for multi-user access in multiple computing environments. The second part deals with the download that accompanies this article. The download contains a slightly modified version of the Invoice Application that ships with Alpha Five. My modifications include the addition of a user logon with password protected access, an example of automatic setup of a user configuration and a working module to merge data from the Invoice Application with MS Word.
OMS Comp. markets a practice management software package developed in Alpha Five. It is widely used by oral surgeons all over the US in practices large and small. It runs in a wide variety of configurations: stand-alone, on LANs (peer-to-peer and with dedicated servers); multi-office practices with self-managed WANs (wide-area network) and, most recently in an ASP (application service provider) model hosted on our own server.
It didn't start out this way, of course. Our early installations tended to be of the more traditional variety: standalone systems and/or smaller LANs were the norm. Managing the setup and configuration of such systems was straightforward and the peer-to-peer LAN setup allowed us to come up with some quick and dirty solutions for problems as they arose. An example of just such a solution was described in my earlier article on the topic of merging Alpha Five data with MS Word. As part of that setup I needed to give a user exclusive access to a file that I did not want to be shared with other users, I came up with the expedient of using the local hard drive for some file storage, thus violating the keep all your files in one folder precept that we frequently articulate in connection with Alpha Five data files management.
Our choice for managing WAN-based installations has been, from the start, to run Alpha over Citrix Metaframe and Windows NT Terminal server. This is frequently referred to as Thin Client computing, because unlike the traditional client-server model, all of the processing is handled by the server, and only keystrokes and screen updates are sent over the network to the client device. The client device can be any of the following: the modern equivalent of a dumb terminal; a PC; or even a MAC, if running over the internet. The point is, all the essential processing takes place on the server. It became readily apparent that in this situation my expedient use of a local hard drive for the storage of Alpha tables was a non-starter since there might be no local hard drive to use for storage at all. As we dealt with these issues, it became clear that I needed to develop a way so that, no matter what the type of installation, our design and files layout was flexible enough to accommodate it.
All configuration systems depend on stored values which determine how data and objects in the application are manipulated. Among other methods, these values can be stored in simple text (like ini files), in the Windows registry or as in our case, in A5 tables. When an application is started it checks the stored values in some pre-determined sequence and (usually) initializes global variables containing or based on those values. Again, in our case, with the advent of our own hosted server, it seemed clear that the solution MUST place the configuration files on the server in the main Alpha Five data folder.
Ultimately we arrived at a setup involving 4 levels of configuration: (1) Application-wide, (2) User Level, (3) Workstation Level and (4) Appointment Scheduler Configuration. When the application starts, it checks values in the Application-wide defaults table and, depending on the values it finds, processes the user and workstation defaults. The Appointment Scheduler configuration is pretty much self-contained.
Our solution permits the storage of default configuration values for each user AND each workstation (where there are workstations) in the system. Why do we need to configure both users and workstations? Well, we come back to the setup of our Word Merge process for an answer to that. Suppose there are two workstations, A and B. On Workstation A, MS Word is installed in C:\Program Files\Microsoft Office\Office and on Workstation B some consultant got the idea of installing it in C:\Office. (This actually happens.) How do we set up the Alpha Application so that it can tell the difference? The simplest and most effective way is to setup a Workstation configuration. When you do, you know that no matter which user logs on to that computer, Alpha Five will always be able to find MS Word and start it when needed.
Even though each of our clients is individually set up by OMS Comp. personnel, I needed to keep the installation process as simple as possible. Ultimately I chose this method because Alpha Five provides such outstanding tools for storing and manipulating tables and files and I thought it would be the most flexible and the easiest to modify post-installation and enhance over time.
As you read the following, remember that your configuration needs will vary depending on the complexity of your application. Little of the following is relevant to the standalone, single user system. However, if you think that some day your company (or your client) will grow to where multi-user considerations will become important, its never too soon to start planning for that eventuality. As I said before, our needs have evolved as our application has grown. Running the application on a WAN is different from running the application standalone or on a LAN. Some of our installations are mixed WAN and LAN. Our setup needs to be able to take all these situations into account.
The setup concept is simple. Each user's information is stored in a table located in a sub-folder that has the same name as the user's logon name. (If you employ the user logon name for a purpose such as this be sure to enforce a field rule that the value in the user field must be unique!) Each workstation's information is stored in a table located in a sub-folder that is the same as the computer's network name. The folders (Users and Workstation respectively) which hold the user and workstation information are first level sub-folders of the main application folder. Storing configuration information like this makes it easy to use the Windows Explorer to check which users and workstations have been configured without having to open the application, something that can be helpful to our technical support staff. It also ensures portability of the application.
MAIN APPLICATION FOLDER | |___________ USERS | | | |_____John | | | |_____Mary | | | | |____________ Workstation | | |_____WORK1 | |_____WORK2
The Users sub-folder contains a one record table called App_user_defaults.dbf and the Workstation folder contains a similar table called App_Workstation_defaults.dbf. These tables are set up so that they contain enough fields to hold present and possible future configuration items. The fields can be any mix of field types and sizes suitable for your needs. In our case, these tables each have about 10 fields but, if necessary, more fields can be added. The tables contain common default values in each field. In many cases there is no need to change these values.
Each time the application starts and a user logs on with a valid password the system checks for the existence of a user folder of the same name. If the folder does not exist, the system creates the folder and copies the default user table into the folder. (See Script 2). If the user options need to be changed, the user can open the default form for the table (see Script 3). The same procedures apply to the Workstation values. I'll repeat that, since it is the crux of the matter: the table and any files in the general Users folder are replicated into each user's named folder. The source tables are templates only. No entries are made in these tables, except to make an entry of a value that will be common to all users in the system. These tables may or may not be added to the database definition. Because of the way the tables are accessed, (see the attached scripts) using a combination of absolute/relative path references, there is no requirement for the tables to be part of the database.
A few words on the topic of application paths, application portability and the "Keep all you files in one folder" precept would seem to be in order here. In my previous article on Merging Your Alpha Five Data with a Word Processor I described why the precept was important to good database management and also how and why I was willing to violate this precept in the cause of solving a real world problem. It might seem, at first glance, that I am still violating the precept by placing the configuration file in the \application\user\username folder which is not, after all the main database folder. OK, I admit it, I suppose I am. However, this violation is not as problematic. It doesn't matter what the top level folder containing the main application tables is called, we can use an Alpha function to determine that. The configuration folder(s) are part of the tree structure below the main database folder which makes it a snap to manipulate their names with Xbasic. Above all, it's a lot easier to tell a user moving the application from one location to another that if you copy all files and sub-folders to the new location everything will work just as it did before.
It's probably true that goals of this configuration setup could be handled with a single table in the application's main data folder in which each user's defaults are stored in a series of records. These records could be filtered and viewed based on the user logon name. I thought about doing this but rejected it for several subjective reasons and one reason unique to our application. I've already alluded to some of the subjective reasons; ease-of-management and training were the most compelling for me. The objective reason had to do with our very powerful Word Merge capability. By creating user name based folders, I was able now to easily allow every user to have her or his own set of set of primary merge documents, an enhancement our users had been requesting for some time. This change required a minimum of code changes to the Word Merge module which, needless to say, had an allure all of its own.
In many (even most) instances, storing Workstation default information will be unnecessary. In our case, again because of our Word Merge capability, we need to know the location of MS Word on each workstation so the program can start it automatically. In a few instances, Word may be installed in a different location from one computer to another in a given office. Being able to address the workstation default values gives us an easy way to deal with this from within Alpha Five.
The application-wide defaults are stored in a table (appropriately enough) called Application_Defaults.dbf located in the main database folder.
The appointment scheduler configuration has its own subset of configuration tables and forms. It is highly customizable. Users can choose among different default appointment lengths, display options, and, among other elements, customize browse and print colors.
The following scripts are based on our working scripts.. I have edited them to remove proprietary information and unnecessary detail. Hopefully the essence remains!
This is the script that runs when the user hits enter and the user name and password are evaluated:
this.disable()
dim password as c
dim global schedlength as n
dim global Your_name as c
dim global Work_Name as c
dim global app_path as c
dim global word_location as c
dim global first_merge_done as L
dim global XXXXXX as L
dim global officenumber as c
dim username as c
dim shared station_path as L
dim shared merge_done as L
dim global schedchange as L
your_name=parentform:name.value
bare_path=:A5.Get_Master_Path()
if len(alltrim(bare_path))=0 then 'the database is not shadowed
app_path=a_db_current_path
goto test1
else
app_path=bare_path+chr(92)
end if
'============
'Password checking code removed here. Assuming valid password script
'continues and main menu form is opened
'Examples of this code can be found in the working scripts of the
'accompanying download file
'============
'check the type of installation
tn=table.open("Application_Defaults")
tn.fetch_first()
instype=ut(tn.installtype)
if instype="XXXXXX" then
XXXXXX=.T.
else
XXXXXX=.F.
end if
tn.close()
'============
'check status of machine and user folders
'if they don't exist create them
'otherwise continue logon
'============
station_path=.F
script_play("app_workstation_defaults")
See below for this script
if station_path=.F.
ui_msg_box("AN ERROR OCCURRED", \
"DURING THE SETUP OF THE WORKSTATION DEFAULTS."+Chr(13)+Chr(13)+"Please \
contact the network
administrator to resolve this issue.",16)
:A5.Close()
end
end if
merge_done=.F.
script_play("app_user_defaults")
if merge_done=.F.
ui_msg_box("AN ERROR OCCURRED", "DURING THE SETUP OF THE USER
DEFAULTS."+Chr(13)+Chr(13)+"Please contact the network administrator
to resolve this issue.",16)
:A5.Close()
end
end if
'============
'initialize scheduler colors
script_play("A_AppSetColors1")
'============
'initialize default appointment length and check for change
schedlength=lookupn("F","1","appt_time_default","a_app_defaults","id")
schedchange=lookupl("F","1","convert","a_app_defaults","id")
'============
'initialize default scheduler browse
dim global brwse as c 'declare the variable for the scheduler browse display
'============
'initialize officenumber
officenumber=parentform:office.value
'============
this.enable()
END
This is script app_workstation_defaults
dim global app_path as c
dim global your_name as c
dim global work_name as c 'name of workstation
dim global word_location as c
dim global netoms as L
dim shared station_path as L
dim workname as c
This function is available at www.learnalpha.com
workname=GetMachineName()
station_path=.F.
if alltrim(workname)="" then
workname=UT(ui_get_text("WORKSTATION IDENTIFICATION FAILED",\
"TYPE YOUR WORKSTATION NAME","","nnnnnnnnnnnnnnnnnnn"))
end if
'declare machine name globally
work_name=workname
old_path=dir_get()
work_path=app_path+"WorkStation"+Chr(92)+work_name
on error goto foldernotthere
dir_new=dir_put(work_path) 'causes error if folder doesn't exist
on error goto 0
station_path=.T.
dirold=dir_put(old_path)
goto folderthere
foldernotthere:
on error goto 0
statusbar.set_text("SETTING UP DEFAULTS FOR THE FIRST TIME FOR \
WORKSTATION "+UPPER(WORK_NAME))
ui_msg_box("PLEASE CLICK OK", "to set up defaults
for:"+Chr(13)+Chr(13)+"WORKSTATION: "+upper(work_name))
dim file_names[100] as c
dirnew=dir_create(work_path)
filespath=app_path+"WorkStation"
filesdir=dir_put(filespath)
files=filefind.first("*.*")
i=1
while .not. files.eof()
file_names(i)=files.name() 'get name of file
isfolder=files.Is_Directory()
filename=alltrim(file_names(i))
if isfolder=.T. then
goto nextfile
end if
systemfilename=filespath+Chr(92)+filename
workfilename=work_path+Chr(92)+filename
file_copy(systemfilename,workfilename)
nextfile:
files.next()
i=i+1
end while
'dirold=dir_put(old_path) 'this can cause an error under Citrix
worktable=work_path+Chr(92)+"App_Work_Defaults.dbf"
t=table.open(worktable)
t.fetch_first()
t.change_begin()
t.machine=work_name
t.change_end(.T.)
t.close()
station_path=.T.
ui_msg_box("INITIAL WORKSTATION SETUP", "COMPLETED!")
goto folderthere
folderthere:
'restore program path and populate word_location variable
worktable=work_path+Chr(92)+"App_Work_Defaults.dbf"
t=table.open(worktable)
t.fetch_first()
word_location=alltrim(t.word_path)
' t.close()
if alltrim(word_location)="" then
ui_msg_box("THE LOCATION OF MS WORD", "HAS NOT BEEN \
ENTERED."+Chr(13)+Chr(13)+"You will not be able to use the\
PowerMerge feature until this entry is made.")
end if
end
Now that the user and workstation tables are set up, use these scripts to access the default form for the tables:
dim global your_name as c
dim global app_path as c
dim username as c
username=alltrim(your_name)
user_path=app_path+"users"+Chr(92)+Username
userfile=app_path+user_path+Chr(92)+"app_user_defaults"
f=Form.View("Default Form for app_user_defaults@"+userfile+"")
'This launches the default form for the setup table for each workstation in
'the system
dim global your_name as c
dim global app_path as c
dim global work_name as c
'============
password checking omitted here
'============
workname=work_name
workfile=app_path+"workstation"+chr(92)+workname+Chr(92)+"app_work_defaults"
f=Form.View("Default Form for
app_work_defaults@"+workfile+"")
In PART 2 of the article, I deal with the specifics of setting up the accompanying download file which contains working code to set up and manage default user folders/values as well as getting the Word merge module to work.
In order to illustrate the ideas I have been talking about in Part 1, I wanted to be able to provide a working sample for readers to be able to use. For that purpose, I have modified the sample Invoice application that ships with Alpha Five. These modifications are more in the way of additions than changes. A table to force a user logon has been added; code has been added to create a configuration table for each user of the system and a main menu option was added to permit that user to modify her or his own options. These processes are based on variations of the scripts described in Part 1. The only table change I made was to the Invoice_Header table where I violated relational data management principles by adding an Invoicetotal field. The invoicetotal field is filled using an OnSaveRecord event in the Invoice entry form. I did this to permit the inclusion of the invoice total information in a merged letter directly rather than calculating it each time a merge is run.
Let me get my feelings about MS Word off my chest now and, having done so, make no further allusions to them later. Ubiquitous though it may be, I hate MS Word and everything about it. I have tried to like it, but I have never started any project that involved MS Word that did not, at some point, have me screaming with frustration. However, like the rest of the world, I am forced to use this dog of a product because it has become the "standard". As far as I am concerned it is the standard for everything that is bad about software design, which is why EVERY word of text in this article was typed in (a) Notepad, (b) the A5 Code Editor or (C) WordPerfect for Windows.
Such difficulties as anyone attempting to set up this merge facility are likely to encounter are overwhelmingly likely to be the fault of MS Word and not A5. As you work through the process of trying to get your merge files and macros properly set up, re-read this paragraph. There, I feel a little better already.
From here on I will assume that your desire to merge A5 data with MS Word bespeaks some familiarity with MS Word structures and practices and the purpose of such of such things as template (*.dot) files. All the MS Word files here were last edited by Word 2000, so opening them with Word 97 or earlier may or may not work without modifications.
MERGING YOUR ALPHA FIVE DATA WITH MS WORD
What happens when you select the merge option button in the attached demo application? The button, by the way, is positioned right on the Invoice Entry form. The process draws its data from the invoice set, which includes a one to many relationship with the Invoice_Items table.
Step 1: A5 creates and exports a single record text file called WORDMERGE.TXT and places it in the user's default folder. Because of the way the set is structured in this application, the exported record contains all the fields from the Invoice_Header, Customer and Invoice_Items tables. To have a comprehensive merge capability for Products and Vendors, it will be necessary to set up a parallel system for the Inventory set.
Step 2.: A5 starts a MS Word session, which loads the selected merge document and runs a MS Word macro on startup.
Step 3: The macro runs the mail merge in the selected merge document with the exported text file into a new catalog. It closes the merge document and does some simple formatting in the catalog to, for example, place invoice items back on one line per item. Thus, when all goes correctly, the user is presented ONLY with the results of the merge, eliminating the possibility of an inexperienced user inadvertently changing the merge document itelf.
So far so good. However, moving the MS Word merge templates, associated macros and text files from one location to another is a painfully tricky process that requires a fair amount of tweaking in MS Word. I'm not going to try to describe my failures to simplify and automate this process, only to give what I hope will be easy to follow instructions that will help you avoid problems and get the process working.
Firstly you must check the security setting in MS Word (Tools. macro, security) and make sure the settings are set to low in order to permit the macros to run.
Second, you must make sure that the merge documents contain the necessary macros and are properly associated with the exported text file (wordmerge.txt). I have saved the macros as part of each documents definition. You can also do this by storing the macros in a purpose-built template file or store the macros in the NORMAL.DOT file.
In the event that somehow (as appears to have been the case in some instances) the macros somehow disappear from or are not visible when the documents are opened, the required macros are saved in this folder in a text file called macros.txt which can, if necessary. be cut/pasted back into the file(s).
In general, the surefire way to make sure the word macros are available to the A5 Word Merge is to complete the following steps before you even open the Invoice application.
The Word macros you need are:
LinkMergeData
LinkMergeDataFirst
Mrge1
Mrge2
Mrge3
their presence or absence in a given document can be checked by clicking
Tools, Macro, Macros
where you will either find the macros listed or not.
(1) Open MS Word and navigate to \User sub-Folder of the folder containing the invoce application files.
(2) Open letter1.doc; it should remember that it it is a merge file. (Note the "Insert Merge Field" and "Insert Word Field" drop down boxes on the left side of the toolbar. This tells you that the document is set up for Mail Merge.Check to see if the macros are present (see above). If the macros are present in this document, then repeat this step for Letter1.doc and Letter2.doc. If the macros are present in all three documents, proceed to step 4.
(3) If the macros are NOT present in any document then:
Use Notepad to open the macros.txt file being careful not to make any changes in the text. Highlight all the text in the file (Shift-Ctrl-End) and copy the selected text to the Windows clipboard (Ctrl-C or Edit/Copy).
In the Word Document Tools, Macro, Macros menu click the "Create" button to open the macro editor. Backspace out the text entered by the program then Ctrl-V or Edit-Paste to place the contents of the clipboard into the macro editor. Click on the X to close the macro editor and save the macros. Save the document. Repeat for documents without macros.
(4) Logon to the A5 Invoice System so that the user folder is created and the necessary files copied to the user folder. Two user names and passwords are available, John/doe and Jane/roe. You can change and add new users in the users table.
(5) From the Invoice Main Menu, Click the "Configure User Options" button and verify that the path entered is the EXACT path to the WINWORD.EXE file. Close the Invoice application.
(6) Restart the Invoice application. Go to the Invoice entry form, select a record
Click "Merge from Invoice"
Select "Edit Merge Document" and select the first document. Note that the program is showing you the documents in your user logon folder. When Word 2000 opens with the selected document, once again click Tools, macro, macros and Run the LinkMergeData macro. Again, nothing happens visibly. Remember to save the document. Close MS Word.
Repeat for the remaining documents. (NB If you get an "Out of Memory" error when starting MS Word, check the path to MS Word. It is almost certainly incorrect.)
To repeat, you must choose the Edit Merge Document option and run the LinkMergeData macro before you use the Quick Merge option, otherwise the merge document may not find the WORDMERGE.TXT data file.
All the documents are now ready for use as merge documents. Likewise any document based (Save As) on one of these documents can, itself, be used for merge purposes. Just remember that the documents need to be in the user folder where the WORDMERGE.TXT file resides.
(7) In the Invoice Form, choose "Merge from Invoice", select "QuickMerge", choose the document, choose the formatting options and voila, you have a merged file.
I hope that you will be able to set up the Word merge without too many difficulties in MS Word. If you persist, I think you will find that the ability to call MS Word in your application like this will be a very useful addition.
As a final word I must tell readers that while I'm delighted to get feedback I am unable to offer additional support for setting up or making modifications to any part of the attached application.
Once again Finian has shared his considerable experience in 2 areas:
|
7/16/00
Don't forget, we need your feedback to make this site better!