Our collective wisdom so far... Getting started with joined databases!
Understanding relational (or "Joined") databases is a big area, but is vital to fully utilizing a program such as Approach. It can be a frustrating time with many concepts to learn, as well as all the ins and outs of Approach. DO NOT START by launching into some major project that you expect to be fully functional tomorrow. Start small, using some test data, and test each function before you go onto the next. Read all you can, and spend time carefully planning what databases you need and how they should be joined. Once you have the basic structure of your database set up you can begin entering or importing you actual data. But as I said, start by do some reading. Some books are recommended earlier in the FAQ, but don't forget the Approach manual either. It briefly takes you though the process of setting up joined databases. Looking at the example databases that come with Approach will also help you understand how the different data in different databases is linked together to provide many facilities that are not possible in flat databases.
Help me design my database!
(joins, repeating panels, what data in which database etc...) Apart from simple 'card file' type databases, every piece of major database software you can think works with relational databases; Approach, Paradox, Access etc... In order to use any of these packages efficiently, including Approach, you first need to understand what a relational database is, and how you would divide your data into one. If you don't understand this then you are going to have difficulty using many features of Approach, or any other database software. Questions concerning database design are quite common on the Approach Users Mailing List. However, strictly speaking most of these questions and answers have little or nothing to do with Approach. What the people asking these question lack not so much an understanding of how Approach works, but rather an understanding of what a relational database is and how they work. And as it turns out, I am not going to attempt to explain this! Instead, what I suggest you do is this: Allocate some time and perhaps money, to do some reading or perhaps a short course in relational databases. Some books are suggested above. The Approach manual includes some information, but most people seem to need a more in-depth look at it before they really grasp what is happening. The better this ground work the easier and quicker you will be able to build an efficient database. I won't take all that long, and the benefits are huge. Also have a careful took at the example databases supplied with Approach. Go into design and check out the structure of the database; the design of the various views; and how each aspect works. Its a good idea to also become familiar with all the features of Approach so that you can be aware of what is it ~ and isn't ~ capable of. In particular, getting to know macros and how to loop them is is very handy (see articles 'Looping macros' and 'Conditional form navigation ( tabs )' in this FAQ) As you are doing these things keep some notes about how you might structure your database. When you are ready begin establishing your database one bit at a time with a small amount of test data. When it is fully functioning, or at least developed enough to do all the major functions, import your data, and begin using it!
Designing and problem solving techniques
Try this: Rather than working with you main database, try setting up and testing out things or trying to solve problems on smaller practice databases. This way you minimize the possibility of you loosing valuable data, or disabling your main database when you really need to use it. For example, if your having trouble with an import or export, set up a very simple flat (i.e. no joins) database and view form. Enter some junk data into a couple of records and try exporting it as a text file. If this works, try importing it back in again. When you can do this, expand this test database so that it has two databases that are joined, and the data from both database is displayed on the one form. Then export some data. Add a repeating panel. Export some more. Import some back in. At every stage do only one thing at a time and test it. This way you will easily identify at which point you are getting into trouble. Once you have managed all of this on your test database you should be able to solve the problems on you main database.
(Including sequentially numbering a set of records and making global changes to a set of records) This FAQ demonstrates macros implemented in Approach V3, 96 or 97 or any of the "Millennium Edition 9.x" releases. Macros were implemented differently in prior versions of Approach, so if you used version 1 or 2, you will need to spend some time in the help files / manual of Approach to accomplish a similar result. Broadly speaking, a looping macro (which may consist of several individual macros working together) does the following: * performs a particular operation or operations
* moves to the next record
* runs the macro again from the top This sequence of steps means that the operation is performed on every record in the current found set. Conditions can be added to the operations so that they are only performed under certain conditions. For example, if you wanted to add a 6 to all of the phone numbers starting with a 5, then your macro would include the statement: SET db.phonenumber to If(Left(db.phonenumber,1)='5', Combine('6',
db.phonenumber),db.phonenumber) (where "phonenumber" is a text field) Often it is necessary to have a macro that precedes the looping macro to set up the conditions that the loop needs for its correct operation. (I'll call this a Control macro). This may do things such as: * Go to the required view
* Give the user a message that the operation has completed. The last operation of the looping macro always runs the looping macro again (NOT THE CONTROL MACRO) This is demonstrated below, but first... To carry information from one record to the next you need to use variable fields. For instance, if you want to sequentially number a set of records you need to know what number the previous record was in order to set the number of the current record. The following example demonstrates both the use of a Control Macro and a Variable to sequentially number a set of records: 1) Create a numeric field 'VarIDNumber' to hold the sequential number, and a variable field 'VarID' to use in calculating the sequential numbers. 2) Create a looping macro 'SequentialLoop'. This macro should do the following: * SET VarIDNumber to VarID
* SET VarID to VarID + 1
* ENTER (to save the updated record)
* RECORDS Next (note this command will exit this macro when it runs out of records in the found set)
* RUN SequentialLoop For this macro to work properly it needs the Control Macro created in Step 3) 3) Create a macro 'Sequential numbering' to be the Control Macro. It should do the following: * VIEW switch to the required view (optional)
* FIND the required found set (optional)
* SET VarID to the required number for the first record
* RUN the looping macro 'SequentialLoop' and return to the next statement
* MESSAGE "The records have been numbered successfully." (optional) To execute the looping macro, run the 'Sequential Numbering' macro. Note: In V2.1 you need to use three macros (it does exactly the same things as shown above, but it just takes 3 macros to do it. In v2.1 it is easy to end up with loop macros containing several macros): Macro 1:
Set the variable field to 1
Run Macro 2 Macro 2:
Set the numeric field to variable field
Menu Options: Next Record
Run Macro 3 Macro 3:
Set the variable field to variable field + 1
Run Macro 2 Also see 'Record numbering' in this FAQ.
Record numbering, including auto-incrementing serial numbers
It is often useful to have a unique identifier for each record in a database -- like a customer number or order number, for example. Or to use in joins where other unique fields are not available. For example, you would not want to use a telephone number in a join because the number could change over time. So it would appear useful to just use the record numbers Approach assigns as you add records. You know, those numbers you see in the status bar at the bottom of the Approach screen? But this is not the case. Why? * The record numbers are not part of the database and are not permanently assigned to particular records.
* When you sort the records, the record that is #1 changes to the one that is "relatively" the first one in the current database.
* When a record is deleted that record's number is not also deleted.
* The record numbers are not part of your data, and you do not have access to the record numbers in calculated fields or finds etc. Not even from a script! If you want each record to have a fixed reference number that never changes as long as the record continues to exist, then you need to create a numeric 'serial number' field which automatically increments each time a new record is created. To do this, just create a numeric field using Field Definition. Then select the desired starting number and increment value in 'Options'. If you have existing records with no number in this field, you will want to add a unique number to them using the technique described elsewhere in this FAQ in "Looping Macros". Unfortunately, the auto-incrementing serial numbering options on numeric fields are not accessible from LotusScript, which means that you cannot read or change the next serial number from a script. In other words, if you add a record using a ResultSet in LotusScript, the serial number will not be entered or incremented. In this situation you have two options: 1) you can write your script so it doesn't use a ResultSet and instead adds and updates the record using a view. 2) you can create and use your own auto-incrementing field instead (details of how to do this can be found at http://www.xpertss.com, under 'Maintain auto-serial numbers when creating records with a ResultSet object' in the 'FAQ's - Tips and Techniques' folder).
Using Boolean fields
Boolean fields can contain a true or false value, or can be "empty" (ie contain a "null" value). Therefore if you want all records to have either a true or false value in the Boolean field then you need to specify a default value in Field Definition, and update any existing records using a macro or script, or FillField. Alternatively, when you need to search for false items, search for all of the items with do not contain a true value. This search will return all records in which the Boolean field contains either a false or null value. You can't use a Boolean field in a join, but a way of getting around this is to simply use a numeric field that either contains a '1' or a '0', so use that instead.
If you are reinstalling Approach to recover from a "one of the library files damaged" error or an unexplained GPF, you may need to uninstall Approach first. When reinstalling Approach it does not replace a file unless it really thinks it needs to. So if a file is corrupt but the installation program does not detect that it is corrupt, then the file may not be replaced - and your problem will remain unless you uninstall it first.
What is Approach? (Is it truly relational?)
This article assumes you are familiar with relational database terminology. If you are not, then all you need to know is that Approach is a database application! Approach is a RDBMS (Relational Database Management System) or 'client-side file server' application. It does not have a native database format but operates as a front end to many different data formats (dBase IV by default). It will also operate as a fully functioning front end/client to Client/Server database servers such as Oracle, including the ability to take advantage of Stored Procedures, Triggers, and the like. Approach is more relationally compliant than many other desktop RDBMS's in that it partially supports cascaded updates and deletes, as well as referential integrity constraints.
How does Approach compare to other DB's?
The following is an excellent article comparing six major database packages, including Approach and Access. http://www8.zdnet.com/products/content/cshp/1804/288515.html Articles about how Approach ranks against other databases, as well as handy hints, and how to optimize your productivity with Approach and SmartSuite, are available by searching the following web sites: http://www.pcworld.com http://www.reviewsource.com http://www.pccomputing.com The following is a collection of comments made by different subscribers comparing Approach and Access: Approach is oriented toward the non-programmer both in ease-of-use areas and in terminology. You can get a lot done with just Approach's design tools and a few simple macros, whereas in Access you must get into "basic" programming fairly quickly. Defining fields is an example of the programmerese in Access - it uses technical terms instead of the more simple "numeric 10.2" or "date fixed" terms in Approach. I also like the idea that my data is stored in commonly used formats like dBaseIV as that opens up the data to use with dBase shareware and reporting packages, if I need them. Access stores all your data and views in one file - you lose it and you have "lost the farm". --- As far as Approach vs Access, both have advantages and disadvantages. I find Approach easier to get started with. It also handles data in a safer manner. Your data tables remain separate from your front-end file (the .apr file) which makes them more secure, easier to save, and available for other programs. (The last could also be a disadvantage under some circumstances.) Access has much better third-party support - you'll find 20 or 30 Access titles in Borders or Barnes & Noble, and (maybe) one on Approach. And IBM (the owner of Lotus) does not have the
same commitment to desk-top computing and software that Microsoft has. Because your tables are not part of your various .apr files, you can start all over again with new .apr files, and safely destroy the old ones when you want to. You may have noticed that you create calculated fields in Approach - for display in forms and worksheets, and summaries in reports. The calculated fields are in the .apr files, so you might want to write down any neat ones you have developed for use in new .apr files. You can create and use multiple data-entry forms with any single table or any group of tables in one .apr file, and turn around and use some of the same tables, with other tables, in a separate .apr file. For example, the "customer" table can be in the sales-contact database, and also in the Accounts Receivable database, in such a way that the sales people would not see the money records, and the bookkeepers would not see the future sale projections. (Revenue and projections being in
separate, related tables.) You can copy views within a .apr file. The only way I have found to copy a view between .apr files is to import the entire source file, and delete the parts I don't want. In doing this I also have to reset the table joins and connections. --- When I evaluated Approach v Access the advice I got was that Approach was easier to learn and much faster to develop in. You can develop a larger app in Access. Approach has limits like 50 joined tables in an apr whereas I think (from memory) Access can handle 100. Anyhow, most Approach users would say that once you get above 20 joins in an apr you should split the app over multiple apr's for performance reasons. For our Company's circumstances Approach seemed the way to go and has turned out a big success. --- I use both Approach and Access. I tend to agree with those that feel that Approach offers a more friendly user interface. I have not come across any tool yet that can compare to Approach in terms of quickly designing and rolling out an app. On the other hand, I have found that I am beginning to push the limits of what I can do with Approach. I was forced recently to develop a billing application in Access, because I just could not get Approach to do what was needed without gobbling up all available memory or generating gpf's. I qualify this by pointing out that I do my Approach development in v3.02 of Approach (16 bit). I cannot speak to the relative merits of Approach 97. Overall, though, I still prefer to use Approach whenever possible. On your second question, there is no "correct" number of .apr files to underlying tables. I find that I use multiple APRs with particular tables, each tailored to the needs of a particular group of users. This flexibility is, IMHO, one of Approach's best features. --- Approach vs Delphi 4?: Comparing the two products is like comparing apples and oranges. Delphi is a very good product if you like Pascal. It's not the easiest product to learn. Each time you perfect a level of understanding it, they come up with something new and better. If you write application for sale then it's one of the good products beside Topspeed. If you write database management programs, you'll find Approach to be one of the best tools... For you and me Approach is better because it takes us maybe two days to knock out a database, in Delphi or Topspeed think of two weeks to two months or more.
http://www-3.ibm.com/software/lotus/support/approach/support.html. It includes a downloads FAQ, 'Technotes', Product Information and 'White Papers'. 'Technotes' are technical documents which address particular problems people have had with Approach. It is a good idea to search here first if Approach is giving you an error message. 'White Papers' are "Detailed investigations into how products work, often including coding tips and techniques."
The following books have been recommended in posts to the Approach Users Mailing List: * The manual that come with Approach. Always a good place to start! * Approach 97 for Windows for Dummies by Deborah S. Ray, Eric J. Ray / Paperback / Published 1997. This appears to be the only book that is specifically about v97. However, you may want to check out some of the v96 books first, as they cover most of what you will need to know and the 'dummies' style isn't for everybody. * Mastering Lotus Smart Suite 97 for Windows 95 by Sandra E. Eddy, includes 'reasonable' coverage of v97 apparently. * Teach Yourself Lotus SmartSuite in 24 Hours by Faithe Wempen and published by published by SAMS. Described by one person as a "Wonderful book, .... broken down into 24 lessons." * Using Lotus Approach 96 Special Edition (Que Books) (Described as being particularly good for New Users) NOTE: If you are looking for a v97 book, the v96 books are still worth getting as the differences are minor and when you stumble across a difference you should be able to get the extra information you need from the Approach HELP files. * Using Lotus Approach 3 by Plotkin (Que Books) * Mastering Lotus Approach 96 for Windows 95 by James Powell (Sybex / Lotus Books, ISBN: 0-7821-1773-2) (This is the most highly recommended book, however, some subscribers have commented that IF YOU ALREADY OWN THE EARLIER VERSION (for v3.0*, see above) then check it out carefully before you by this one. They expressed strong disappointment that it has only been minimally from the earlier version, and major aspects of Approach 96 are not covered adequately. It therefore may not be worth the expense.) * Mastering Lotus Approach by James Powell (Sybex / Lotus Books) (This is the most highly recommended book for v3.0*) * Building Databases with Approach 3 by Elaine Marmel ("...seems to be an extended, well documented, tutorial on building a database that is usable in a business environment.") * Approach 3.0 for Dummies * Practical Approach version 3.0 by Siegel (MIS Press) * Introduction to Databases" by James T. Townsend (recommend for business uses who are learning and updated designing databases. It is about databases in general rather than how to use Approach.)