Welcome to

Alpha Five Made Easy The Basics and More for Version 10 Special Alpha Software Blog Edition Installment No. 1 Introduction, Table of Contents, Databases and Tables*

Getting Started This book is designed to be used in conjunction with databases that may be downloaded free of charge at http://www.libertymanuals.com/fix-it-here.html. The databases need be installed only once for the entire series. If you reinstall them, any work you have done previously will be overwritten. Please be sure to read the Introduction, including “Getting Started” on page x, for instructions on installing the databases and other important information. REFERENCES:

The copy herein is an actual representation of the book, which has been divided into sections for insertion into this special edition. Throughout, there are references to other pages in the book. Some are “backwards” references and are included in this reprint. Others send the reader to sections not yet covered and will not be available until that section is reprinted. All references are to the book page, not the Acrobat file page. The book page number is located at the bottom of each page. PERMISSIONS:

•This book is copyrighted, therefore you are not permitted to make any changes to the document. •You are permitted to print the pages. EDITIONS

This book is available in Hard Copy and Electronic Download Edition. Both are available at www.libertymanuals.com. •The Hard Copy includes a free copy of the Electronic Download Edition so you can get started right

away. •The Electronic Download Edition contains Bookmarks for each section in each chapter and the Acrobat pages have been syncronized with the book pages.

*. Reprinted with permission.

.

ABC Seminars

The Basics and More for Version 10 Step-by-step instructions and useful tips for users of all levels

C

D

En cl os ed

Susan Hussey Bush

Expand your Alpha Five applications with the Alpha Five Functions Handbook, Functions and Expressions Made Easy

Alpha Five’s Functions allow the development of database applications without programming. In this handbook, they are explained simply, in layman’s terms. This manual was born of the author’s many years of frustration while searching for the right function for the job. Character, dabte, numeric, summary or filter—you’ll find and use them easily in this logically designed manual.

Alpha Five Web Applications Made Easy, The Basics and More for Version 10.5 Make the most of your software — your copy of Alpha Five Version 10 comes with the ability to design Web Applications. And this is the book that will show you how to create your own.

Alpha Five: Xbasic for Everyone by Dr. Peter Wayne Learn Xbasic, Alpha Five’s programming language. Hard copies now available at www.LibertyManuals.com.

Available at www.LibertyManuals.com or call 1-732-842-3000

ALPHA FIVE MADE EASY The Basics and More for Version 10 by Susan Hussey Bush

Liberty Manuals Company 130 East River Road Rumson, New Jersey USA www.LibertyManuals.com

Alpha Five Made Easy, The Basics and More for Version 10 Published by Liberty Manuals Co. 130 East River Road Rumson, NJ 07760-0453 www. LibertyManuals.com Copyright © 2010, Susan Hussey Bush. All rights reserved. No part of this book, including interior design and cover design, may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording, or otherwise) without the prior written permission of the publisher. ISBN 978-0-9820955-1-5 Printed in the United States of America. 10 9 8 7 6 5 4 3 2 1 First Printing. Distributed by Liberty Manuals Company. This manual has been designed for the use of authorized owners of Alpha Four and Alpha Five software. Alpha Four© and Alpha Five© are the registered trademarks of Alpha Software Inc. Liberty Manuals Company is a separate company and has no connection with Alpha Software Inc. Every effort has been made by the publisher and the author in preparing this book. No warranty is made as to the accuracy or completeness of the contents of this book. Liberty Manuals Company assumes no responsibility or liability for any errors or inaccuracies that may appear in this documentation. It is important to remember that artwork or images in this document may be protected by copyright law. Unauthorized use of such material could be a violation of the rights of the copyright owner. Permission to use any part of this document must be obtained from Liberty Manuals Company. The Liberty Manuals logo is the property of Liberty Manuals Company. “Get Outta DOS” and “GET INTO ALPHA FIVE!” seminars are the property of Susan H. Bush and Frances M. Peake. Alpha Five© and Alpha Software are the property of Alpha Software, Inc. Microsoft©, Windows©, MsDos©, Excel©, Works© and Access© are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Adobe Acrobat©, Acrobat Reader© and Adobe Framemaker© are the property of Adobe Corporation. Alpha Five Mailer© and AlphaMailer© are the trademarks of JangoMail. All other trademarks are the property of their respective owners. For information on purchasing the books in the Alpha Five series, contact: Liberty Manuals Company, PO Box 453, Rumson, NJ 07760, USA. Tel: (732) 842-3000. Fax: (732) 741-5820. Email and Website: [email protected]; www.LibertyManuals.com.

Frances M. Peake, professional database developer, has this to say about Alpha Five Made Easy… Susan Bush and I met at the Alpha Five Developer’s Conference in December of 2000. I was the professional developer. Susan used Alpha Five in her business. After a short conversation with Susan, I realized that she was much more than an end-user. She has made a study of Alpha Five over the years, pushing the product to its limits to serve her business needs. Shortly after the conference, Susan came to our South Florida Database User Group meeting. She offered to give a presentation on a multi-step import procedure she had devised in Alpha Five. She impressed the group with detailed handouts and screen shots. Then she hosted the next few meetings while I was out of town. When I returned to the User Group meetings, Susan pitched the idea of doing a seminar to help Alpha Four* users make the transition to Alpha Five thus our joint ventures, “Get Outta DOS” and then “Get into Alpha Five!” seminars were born. We agreed that, with all the preparation it would require, the seminars would not be big money makers. So I asked, “What would you want to get out of it?” She stated very emphatically, “I want to do an Alpha Five book.” I can’t remember whether I said this out loud, but my first thought was “Are you out of your mind?” But I knew it was true, Susan was going to write her Alpha Five book. The book that you hold in your hands is not simply the product of Susan’s experience as an advanced-level Alpha Five user. It is a product of her vision and seemingly boundless energy and enthusiasm. It is also a product of many, many hours of research, writing, editing and above all, attention to detail.

Like so many of us in the Alpha Five community, Susan saw a need for additional training and support materials for Alpha Five. Any of us who have ever contemplated actually writing such a book and imagined ourselves as the author of, “Step 1. Right-click the mouse…Step 2. Choose Properties…” can only begin to appreciate what Susan has accomplished here. This book can be used as both a tutorial and reference manual. Susan has achieved a healthy and practical balance between tutorial and reference by offering short, compartmentalized exercises. The book and disk that accompanies it are designed so that you can start at any chapter. For example, if you want to jump right into designing forms, you will not be lost if you have not followed the lessons from Chapter 1. You will find that every chapter is chock-full of valuable information for Alpha Five users of all levels. The value of Susan’s experience using Alpha Five in a real-world business environment cannot be overstated. It is evident throughout the book in the plain-English explanations of each Alpha Five feature and in the examples she uses. You will appreciate the generous use of screen shots to guide you through the exercises. Best of all are the tips and hints she provides to help shorten the learning curve so that you can get the most out of Alpha Five. On behalf of Alpha Five users and enthusiasts everywhere, thank you Susan, for writing this book! — Frances M. Peake,† Co-owner, Proctor & Peake, Inc. www.proctorandpeake.com

*. Alpha Four is a relational database program developed by Alpha Software, Inc. It uses the MS DOS platform. †. Frances Peake is the author of Alpha Four to Alpha Five, The Transition, soon to be available at www.proctorandpeake.com and www.libertymanuals.com

About the author

From her first computer experience in 1984 - a used Apple I presented by her father after he upgraded to an Apple II - Susan Hussey Bush has been hooked on this amazing (and sometimes frustrating) tool of the modern world. The 1985 purchase of the state of the art IBM XT with an astounding 10 mg hard drive opened the door to database development. That first flat database, Nutshell, showed her that one did not have to be a programmer to develop applications. Later, she began the search for a relational database that did not require programming skills. Alpha Four, a DOS product, met those requirements. When Alpha Five was released, she moved to the Windows version. After expending considerable effort learning the program and designing on her laptop, it was stolen. The only copy of the database was gone and a serious setback resulted. The good news is that by the time she started over, Alpha Five version 3, with sig-

nificant improvements over the earlier versions, had been released. The development of a database for her government bidding company, Liberty Food Marketers Co., then began in earnest—this time with backups! Today, that application contains more than 60 tables and 75 sets. As a non-programmer, Susan has set up 100 saved operations to import, reformat and export data. She has since sold the business to her son, Dave, who continues to use Alpha Five to stay current with the demands of government bidding and to offer customers outstanding service. Susan has a Bachelor of Fine Arts in Architectural Design. She brings that training to her database design, focusing on well organized, attractive forms and reports. Alpha Five allows her to create database applications reflecting her belief that software should be user-friendly. She is excited about adding Version 10 features to her databases. She lives with her husband, Richard, in Flat Rock, North Carolina. August, 2009 was a difficult month because Susan lost two siblings, who died of unrelated illnesses. Happiness comes in the form of Susan and Richard’s blended family of seven children and nine grandchildren, who are spread across the country. Mike, Liz and Walter Hussey live in the Florida Keys. JR Hussey and his wife, Carin Young, live in Mill Valley, CA. In Rumson, New Jersey, Susan’s hometown, Dave Hussey keeps busy with Liberty Food Marketers Co. Rick Bush’s daughters, Taylor, Jordan and Lindsey live in Pea Ridge, Arkansas. He and his oldest daughter, Hailey, live in Neosho, MO, where Richard grew up. Ross and Mariola Bush and daughter, Clara, live in Rolla, Missouri where Mariola is a Civil Engineer and Ross is an attorney. Residing in Fort Smith, Arkansas are Missy, her husband, Jeff Brown and son Jake. Kathleen and Sands Orris and their sons, William and Foster make their home in Cumming, Georgia.

This book is dedicated to my husband, Richard Bush , and my sister and brother, Cynthia Maria Fowler and John Alden Rathbun In Memoriam Cynthia Maria Fowler 1939–2009

Left: Cindy, Cathy and Robert with our parents, Walter and Libby Rathbun.

Right: Robert all grown up! Elizabeth, Cathy, Cindy and Chuck welcome in 2004.

Cindy’s life centered around her three children, Elizabeth, Cathy and Chuck, her grandson, Robert, and her church.

John Alden Rathbun 1953 – 2009

John receives a “Captain’s Word is Law” plaque from our father, Walter Rathbun.

Left: Agwbe, the 46’ steel ketch on which John and his family sailed around the world.

Above: John and his wife, Dominique. Left: Their son, Colin, also a sailor.

At the age of 19, John bought his first sailboat and the sea became his home. Devoted to his wife, Dominique, and son, Colin, he accomplished what many only dream.

LESSON 1.

Notes:

viii

ALPHA FIVE MADE EASY The Basics and More for Version 10

Introduction Alpha Five - easy and powerful What a good time I’ve had writing this book! Larger screen shots and almost a total rewrite, it could be titled Alpha Five Made Easier than Ever! We hope you find it as exciting as we do. Bonus Pages Every edition becomes larger and larger because the folks at Alpha Software keep giving us more and more features. The main book is restricted by the physical size of the binding, but that hasn’t stopped us from giving our all. We’ve added a PDF file with three more chapters!* Here’s what’s in the Main

Book:

•Chapter 1: Databases and Tables •Chapter 2: Field Rules and Data Entry •Chapter 3: Sets and Mapped Tables •Chapter 4: Forms and Browses •Chapter 5: Reports, Letters and Labels •Chapter 6: Operations •Chapter 7: Buttons, Hyperlinks and Bubbles •Chapter 8: Finding, Sorting and Filtering Records •Chapter 9: Checkboxes, Radio Buttons, Listboxes and Tree Controls

•Chapter 10: Graphics in the database •Chapter 11: SQL and Alpha Five •Chapter 12: Action Scripting, Xdialog and Custom Menus

•Chapter 13: Handy Things to Know

Here’s what’s in the Bonus

Pages:†

•Chapter 14: Color and Style •Chapter 15: Email and Fax with Alpha Five. •Chapter 16: Alpha Four to Alpha Five, The Transition. Alpha Five has always been in the forefront of database products because it allows both non-programmers and programmers to develop sophisticated applications. You will find Genies‡ everywhere. Action Scripting is easy to learn and creates code in the background that is available for further editing, if desired. Whether you are an Alpha devotee or a new user, Alpha Five will continue to amaze you with its ease of use. Both novice and expert alike will love its features! Francie’s back! Unfortunately, we had to drop Frances Peake’s chapter on making the transition from Alpha Four to Alpha Five last time, due to space considerations. We’ve had requests for it and know there are still many out there who have yet to change from their old reliable DOS friend to the Windows version, so we have included it in the Bonus Pages.

What’s in Alpha Five Made Easy? Mastering Alpha Five is truly made easy with this manual. Designed as a tutorial with step-by-step instructions, you will have, in just a short time, a basic understanding of the program and will soon be developing your own custom application. We started the Alpha Five Made Easy series with our first book, based on Version 4.5, followed by revisions for each new version up to this latest one. Our hard copy edition is produced in full color with a durable spiral binding for convenience in learning and future reference. Copies may be purchased at www.libertymanuals.com. A PDF download is also available.

*. Bound, color copy available at www.LibertyManuals.com †. Once you have installed the companion files, you’ll find them at c:\A5_Ver10Book\A5ME_Elec_Edition\BonusPages.pdf. ‡. Sometimes known as Wizards in other programs.

ix

LESSON 1. INTRODUCTION Getting Started

A message to the small business community Welcome to an exciting way to control your information. The database is a wonderful tool, yet, unfortunately, many small business owners believe they must know how to write code in order to create database applications. Alpha Five has been our solution for many years. You can design a simple database or a complicated one. And you can do it without any knowledge of programming. There is a programing language for those who want to take advantage of it, but so much can be done with the other Alpha Five tools that you will be amazed. If you do want to continue your study by adding Xbasic to your knowledge base, we now offer hard copies of Dr. Peter Wayne’s book, Xbasic for Everyone. I am a business owner, not a programmer, yet I designed the Alpha Five databases which have been the foundation of my businesses for many years. You’ve heard it said repeatedly, “If I can do it, you can do it.” And it is true. I did. You can. Good Luck—and have fun! —Susan H. Bush President, Liberty Manuals Company

Acknowledgments As always, the assistance of my son and fulfilment manager, David Hussey has been invaluable. Dave handles the printing, assembly and shipping of our books. Barry Ehrman has again proofed the exercises so that your learning experience will be as trouble free as possible. Barry’s day job is professional photography. You can check out his excellent work at www.barrystudios.com. I wish to again express my gratitude to Frances M. Peake for her contributions. Over the years since she first worked in Tech Support at Alpha Software, Inc., many users have benefitted from Francie’s knowledge. She was the leader of the Miami Database User’s group for over ten years. Together, we presented “GET INTO ALPHA FIVE!” seminars for those wishing to learn to use Alpha Five and Alpha Four users wishing to take their applications from DOS to Windows. Be sure to visit her web site for more tips — and I can’t recommend a better developer for both desktop and web applications. Her web address is www.proctorandpeake.com.

As we go to press As I complete this book, an earthquake has just devastated Haiti and our North Carolina snow that fell the week before Christmas has yet to melt completely a month later.

x

The rare and extended freezing temperatures that we experienced have been felt in many parts of the nation and abroad. The Winter Olympics in Vancouver are a bright spot on the horizon. We continue to pray for our brave soldiers in Iraq and Afghanistan and await their return home. SHB. January, 2010

Getting Started About ALPHA FIVE MADE EASY ALPHA FIVE MADE EASY, The Basics and More for Version 10, was developed to aid beginning and intermediate users in learning the basic concepts necessary to use Alpha Five, but it doesn’t stop there. Persons experienced in the use of Alpha Five will find many useful tips because the book includes instructions on using the new features in Version 10. The lessons are designed as step-by-step instructions for each section. While each section may be used independently, some of the terminology builds sequentially from lesson to lesson. For example, the Toolbox is described in detail in Chapter 4. Subsequent lessons expect the user to understand its function. This manual does not cover all the features available in Alpha Five. We invite the user to explore the program by using the genies and the help file. The beauty of the software is that it is so user friendly. Once you get started, you will be amazed at the level of sophistication you are able to achieve. The information in this book is current to Version 10. Books on earlier versions of Alpha Five are available at www.LibertyManuals.com.

What you need to know You will need to have a working knowledge of Microsoft Windows and its standard commands.

The equipment you will need to have You will need to have the Alpha Five Version 10 program installed on your computer. Information on purchasing the program can be found at www.alphasoftware.com. You may also contact Alpha Software, Inc. by E-mail at ([email protected]) or by phone at 781-2294500 during regular business hours, Eastern Time. For further information on contacting Alpha Software, Inc., see page 483.

ALPHA FIVE MADE EASY The Basics and More for Version 10

Footnotes

Tips on searching the Help files

In some instances, the footnote for a given page will appear at the bottom of the following page. Location of footnotes is set by my excellent book writing software program (Adobe Framemaker) and I have no control over where it puts them!

Throughout the book, we have provided locations of Alpha Five help topics* so that you can find additional information on a given subject. You can access them from the file on your local computer and from the web files at alphasoftware.com.

1. Open Alpha Five and choose Help > Help Source.

•Alpha Five Help > Index tab: Enter Image File Reference > Adding an Image. (The final reference, Adding an Image, is the page that I found helpful.)

The options are: • Web Help and Local Help. Our references are to the Local Help version because its format remains constant and we like it better when working in the desktop. We work with the Contents, Index and Search tabs visible in a frame to the left of the topic page.

• Search Tab: When all else fails, we go to the Search tab and list the reference that way. WHAT’S NEW HELP

2. At the above menu, scroll down to:

• What’s new in Version 8 describes the enhancements for that edition.

•

•The top left button toggles between Show and Hide. If the tabs are hidden, click Show. • Contents Tab: Most of the help references in this book come from the Contents tab as seen in the following example. • A5 Help > User Guide > Databases > Creating Tables. • Index Tab: Another quick way to find information is to use the Index tab and type in a topic. When necessary, we have listed some of the references this way because we can’t tell exactly where the page can be found at the Contents tab. An example is:

What’s new in Versions 9 & 10 contain the help files for those versions. •At this writing, these files have not be integrated into the main help file. For example, Super Controls were developed in V9 and new ones were added in V10. They are only described in these What’s new files. •You may find it worth the time, ink and paper to print them out and note the contents for future reference.

•

The good news is that there are also videos. We have noted several in the instructions. Look for this video camera icon.

*. The Alpha Five Help files may be edited at any time. Our entries are current to the time of their entry in this book.

xi

LESSON 1. INTRODUCTION Using Alpha Five with a remote database

To quote or not to quote Within Alpha Five, quotes have a very specific use. To avoid confusion in the instructions, we have not used quotes around text to be entered, but have bolded the font instead. For example: Type My name is Susan.

About the screen shots There are more screen shots in this book than ever before, and they are LARGER than before! I have several readers to thank for that. Two in particular come to mind. One who was more than a little cranky and one who couldn’t have been nicer. The second even told me about a screen shot capturing tool (Snagit by Techsmith) that I can no longer live without. Somewhere in the middle of this edition, I learned that I could make screen shots appear next to the copy, rather than only below it. I’ve used Adobe Framemaker since the very first edition of these books, but I just didn’t know how to present decent sized images without wasting paper. When the light bulb came on — the feature was there all the time — I went back and revised the earlier work, too. I’m sure you’ll agree that the results have been worth the extra time it has taken to get this book into your hands. Of course, you are the ultimate judge, so if you find any that don’t work, please give me the page and I’ll see what I can do for the next edition.

Making the transition from Alpha Four to Alpha Five As stated earlier, this edition of Alpha Five Made Easy includes Frances Peake’s excellent explanation on the transition from Alpha Four† to Alpha Five. Look in the Bonus Pages.

Installing the ALPHA FIVE MADE EASY lesson files Note: The Alpha Five program is necessary to open the files. See above for information on purchasing the program. All Windows Platforms

Insert the ALPHA FIVE MADE EASY, The Basics and More for Version 10 companion CD-ROM in your CD drive. • In most cases, the installation will begin automatically. If that is not the case, the following instructions will show you how to begin the installation: A. Begin

at the Windows Desktop. Follow instructions below for your Windows platform.

Using Alpha Five with a remote database One of the very impressive things about Alpha Five is that it allows users of SQL* and other database file types to connect Alpha Five to their information. The data remains in its original format, but now Alpha Five’s legendary easy-to-use form design and other fabulous features are available for everyone! Chapter 11 tells all about it. For the most part, learning Alpha Five is the same for both SQL and DBF users, so the DBF companion files included with this book are appropriate learning tools for all.

Windows Vista & Windows 7

We regret that at this time we do not have access to Vista or Windows 7. You can, however, follow the basic instructions for Windows XP. We hope the only difference is that things may be in slightly different places. Windows XP 1.

Choose Start > Control Panel.

2.

Double click ADD OR REMOVE PROGRAMS.

3.

Click ADD NEW PROGRAMS (left pane).

4.

Add a program from CD-ROM or floppy disk: (Click CD or FLOPPY) (Click NEXT)

•Windows searches available drives for the install.exe file. •Continue at Step C below. Windows 2000 1.

Choose Start > Settings > Control Panel.

*. SQL = Structured Query Language. This is the format used by Microsoft Access and some other databases, as opposed to the DBF file format used by Alpha Five. †. Alpha Four is the DOS version of the database.

xii

ALPHA FIVE MADE EASY The Basics and More for Version 10

2.

Double click ADD/REMOVE PROGRAMS.

3.

Click ADD NEW PROGRAMS (left pane).

4.

Add a program from CD-ROM or floppy disk: (Click CD or FLOPPY) (Click NEXT)

•Windows searches available drives for the install.exe file. •Continue at Step C below. Windows 98

The databases include both practice exercises and finished examples. • Beginners, will find the practice exercises the best way to learn Alpha Five. Just follow the step-by-step instructions. • Experienced users can follow the step-by-step instructions to learn new techniques or use the finished examples to quickly sharpen their skills.

1.

Choose Start > Settings > Control Panel.

2.

Double click on Add/Remove Programs.

Naming convention

3.

Click Install.

4.

Click Next.

The included Alpha Five databases contain sample and working tables, reports, etc. When the exercise calls for you to create or open a file there is usually a corresponding completed example.

•Continue at Step C below. B. Windows

reports the command line for the installation program.

• If the install.exe file does not appear in the “Open” window, •Click Browse. •Navigate to the drive containing the CD. •Double click on Install.exe. C. Windows then reports the command line for the installa-

tion program in the “Open” window. 5.

Click Finish.

•Message reads: This will install the Alpha Five Made Easy files to your hard drive.

6.

Click OK.

7.

Unzip to folder: c:\A5_Ver10Book. (Click Unzip)

•Note: The Operations in Chapters 6 and 7 are set to locate files placed in the above location. If you move the files in a different location, you will need to make adjustments to the Import Operation.*

8.

Files unzipped successfully. (Click OK)

• The exercises will call for you to create a file beginning with “My,” such as MyClientInfo. • The exercises will call for you to open an existing a file ending with “Practice,” such as StylesheetPractice. • The completed examples do not have the “My” prefix or “Practice,” such as ClientInfo or Stylesheet. Troubleshooting tip: If you are having difficulty with an exercise, you can open the completed example to see the finished example in action.

Remember the Bonus Pages! They are on the CD or in the Electronic Download Edition and will be loaded when you install the companion files. In most cases they can be found at:

c:\A5_Ver10Book\A5ME_Elec_Edition\ BonusPages.pdf

•The Readme file opens. 9.

After reading the Readme, close it and the Control Panel.

Database files There are five databases included with this book. • ABC_v10_Lessons is the foundation for most of the exercises. • NorthwindCopy is for remote database users and is used in Chapter 11, “SQL and Alpha Five.” • A4_Data, A5_Apps and A5_Trans are for the transition from Alpha Four to Alpha Five.

*. See “Caution!” on page 272.

xiii

LESSON 1. INTRODUCTION Support

Support While every effort has been made to provide readers with a trouble free and accurate learning experience, some errors are bound to occur.

xiv

The support section of the website will publish corrections as they are discovered. We suggest you visit it periodically to see if there are modifications. And please let us know if you find something. Your fellow readers will appreciate it!

ALPHA FIVE MADE EASY The Basics and More for Version 10

Table of Contents Introduction

1 Databases and Tables

2 Field Rules and Data Entry

Alpha Five - easy and powerful ............................................................................................ix What’s in Alpha Five Made Easy? ix A message to the small business community ix Acknowledgments x Getting Started........................................................................................................................x Using Alpha Five with a remote database............................................................................xii Making the transition from Alpha Four to Alpha Five ........................................................xii Installing the ALPHA FIVE MADE EASY lesson files......................................................xii Database files xiii Naming convention xiii Remember the Bonus Pages! xiii Support ................................................................................................................................xiv Creating a new database .........................................................................................................3 Understanding Tables and Fields ...........................................................................................4 Creating a new table 6 Closing the database 9 Opening multiple databases 9 Understanding the Alpha Five Control Panel.........................................................................9 Taking a tour 10 Tables/Sets tab 10 Layouts tabs 11 Operations tab 11 Changing the table structure.................................................................................................11 Warning message 12 Planning table design 12 Naming your database and its objects ..................................................................................12 Using long names 12 Avoiding spaces 13 Reserved words 13 Selecting names 13 Using a naming convention 13 Opening an existing database ...............................................................................................14 File in list 14 File not in list 14 Edit list 14 Sample Databases 14 Understanding Field Rules ...................................................................................................19 Table level 19 Form layout level 19 Creating table level field rules..............................................................................................19 Understanding the menus 20 Viewing the field list 21 Finding fields in a large table 21 Defining Field Rules ............................................................................................................21 Understanding Field Rules settings 21 Understanding the Field Types tab 22

xv

TABLE OF CONTENTS

Extended field types 22 Default descriptive name 23 Field description 23 Defining Auto Increment fields 23 Setting a Default Value 23 Setting a Skip expression 24 Logical statements 24 Changing the case of fields 24 Understanding Masks 25 Conditional templates and masks 25 Mask formatting symbols 26 Pre-formatted masks 26 Custom masks 27 Defining a default value 27 Enter Date Field 28 Creating a calculation 28 Understanding Functions and Expressions 29 Memo fields 29 Time fields 29 Image and Image File Reference fields 29 Saving and closing Field Rules 29 Error messages 30 Field Rules errors utility 30 Testing with the default browse 30 Moving between Restructure Table and Field Rules 31 Defining a Lookup Table .....................................................................................................31 Understanding Source and Fill 33 Display 33 Width 33 Condition 33 Choosing the type of lookup box 34 Separate window 34 Drop Down List Box 34 Creating a separate window lookup 35 Adding and editing records 35 Choosing the lookup form or browse 35 Temporary browse 35 Custom browse 35 Defining the pop-up behavior 35 Position 35 Creating a drop down list box lookup 36 Entering, changing and deleting data ...................................................................................37 Opening the default browse 37 Tables/Sets tab 37 Using the separate window lookup 37 Entering dates 38 Entering numbers 39 Entering character fields 39 Entering a date for the current year 39 Using the drop down list box lookup 39 Resize drop down list box 39 Add new entry 39 Change existing entry 40 Using spell check 40

xvi

ALPHA FIVE MADE EASY The Basics and More for Version 10

Current record 40 Edit user dictionary 40 Using spell check in memo fields 41 Speed up your entries with Speed Typing 41 All character and memo fields in table 41 Specific fields in table 42 Canceling an entry 42 Deleting records 42 One record 42 Multiple records 42 Saving records in a database 43 Changing data in a record 43 Renewing Field Rules 43 Calculated fields 43 Other fields 43 Restoring prior versions 43

3 Sets and Mapped Tables

Understanding Sets...............................................................................................................47 Connecting the tables 47 Understanding One-to-One links 47 Understanding One-to-Many links 47 Relating table design and set design 47 Creating a Set .......................................................................................................................49 Creating a One-to-One Link 49 Creating a One-to-Many Link 51 Understanding Referential Integrity 51 Cascade changes/deletes 51 Prevent changes 52 Child records 52 When to use Referential Integrity 52 Linking on multiple fields 53 Viewing a one-to-many link without referential integrity 54 Editing sets ...........................................................................................................................54 Seeing Referential Integrity in action...................................................................................55 Using Field Rules with sets ..................................................................................................56 Understanding Mapped Tables.............................................................................................56 Field Rules 56 Exporting 57 Advanced users 57 Creating a Mapped Table 57 Tables tab 57 Records to include 58 Fields tab 58 Understanding field properties 60 Making fields updateable 60 One at a time 60 By default 61 Global updates 61 Update status 61 Viewing the mapped table 62 Changing the display 62 Reposition column 62 Combine multiple fields into a single column 62 Viewing the Xbasic 63

xvii

TABLE OF CONTENTS

Editing an existing Mapped Table 64 Adding a computed column 64 Exporting to Excel 65

4 Forms and Browses

xviii

Overview ..............................................................................................................................68 Understanding form design ..................................................................................................69 Creating a default form 69 Stylesheet Gallery 70 Creating a Custom Form 71 View Xbasic code 72 The design Tools 72 Understanding the Top Menu 73 Roll- Ups 75 Understanding the forms toolbar 77 Design decision 79 Working with floating toolbars 79 Anchors 81 Stretch field height 83 Docking and undocking the floating toolbars 83 Using Dynamic Guidelines 84 Using the Right Click Menu 84 Properties 84 No spell check 85 Events 85 Using the Rulers Menu 85 Vertical alignment and width of fields 85 Horizontal alignment and height of fields 86 Move group of fields 86 Understanding the Task Panes .............................................................................................86 Using the views 87 More quick launch tricks 88 Form controls to Super Controls—and back 89 Restore default layout 89 Using the Toolbox 89 Views 89 Using the tools 90 Text 92 Text styles 92 Frame 92 Lines 93 Using the DragDrop List 93 Drop Fields genie 94 Controls across 94 Controls down 94 Group / Ungroup objects 94 Right Click Menu 94 Finding fields on the form 95 Finding fields in the DragDrop list 96 Sort order 96 Collapsing tables 96 Using Browse Controls 96 Open Form 96 Print/Preview Layout 97 Run an Operation 97

ALPHA FIVE MADE EASY The Basics and More for Version 10

Generic action 97 Image 97 Text 97 Using the Interactive Window 97 Testing with the custom form 97 Test spell check 98 Restoring to a prior version 98 Understanding the Properties Pane & Object Properties dialog...........................................99 Using the Properties Pane 100 Selecting the object 100 Object properties 101 Builder button 101 No spell check 101 Dynamic properties 101 Form properties 102 Using the Object Properties dialog 102 Creating a tabbed form .......................................................................................................103 Set up 103 Change background 103 Graphic backdrop 104 Resizing the form 104 Form width and height 104 Snap to Grid 105 Placing a title on the form 105 Creating a text object 105 Sizing the text object 106 Changing the object properties 106 Renaming an object 106 Rotating the text 107 Viewing the finished text object 107 Creating a Tabbed Object 107 Changing the appearance of a Tabbed Object 108 Changing the page colors and names 108 Transparent background 108 Adding a new tab page 108 Deleting a tab page 109 Selected tab label 109 Resizing a tabbed object 109 Changing the order of tabbed pages 110 Understanding Browses......................................................................................................111 Creating an Embedded Browse ..........................................................................................111 Understanding Embedded Browse properties 113 Browse object 113 Column properties 114 Customizing the browse properties 114 Property Pane views 114 Sizing the browse 115 Adjusting the columns 116 Resizing columns 116 Adding columns 116 Removing columns 116 Column order 117 Ordering the form’s records 117 Smart Fields 118

xix

TABLE OF CONTENTS

Making a browse read-only 119 Changing colors 119 Selected records 119 Splitter color 119 Sorting by column title 119 Testing the changes 120 Restrictions 120 Browse splitter 120 Column title sort 120 Scroll bar navigator 121 Selected color 121 Changing the column titles 121 Using the Undo and Redo commands 121 Testing the Undo command 121 Placing One–to–One fields on the form .............................................................................122 Moving objects 122 Dynamic guidelines 122 Snap to grid 122 Micro moves 123 Objects on tab pane 123 Set position 123 Placing fields and their titles on the form 123 Aligning objects 124 Equalizing vertical spacing 124 Adjusting field sizes 124 Moving objects in small increments 125 Equalizing horizontal spacing 125 Adding more fields with titles 125 Changing label names 125 Ungrouping objects 126 Copying attributes 126 Save, Save, Save! 126 Copy all to selected 127 Copy all to same type 127 Copy some to some 127 Viewing the form edits 128 Adding a record navigator ..................................................................................................129 Moving all objects on a tab pane 129 Bubble Help 129 Tick style 130 Logical record number 130 Editing the navigator control 130 Placing One-to-Many fields on a form...............................................................................131 Reviewing the set structure 131 Adding another embedded browse 131 Position and resize 131 Adding dynamic references................................................................................................132 Creating a dynamic header 132 Using fields as dynamic references 134 Making the fields read only 134 Linking field 134 Tweaking the layout 135 How everything works so far 135 Dynamic header 135

xx

ALPHA FIVE MADE EASY The Basics and More for Version 10

Dynamic fields 135 Read Only fields 135 Entering data into the browse 135 Saving form changes 136 Viewing more reference objects 136 Creating form level field rules............................................................................................136 Understanding the difference between rules and properties 137 Field Rules 137 Field properties 137 Crossover 137 Choosing the right option 137 Table level rules 137 Properties Pane & Field Properties 137 Form level rules 137 Case conversion 138 Decimals & Width 138 Locating the rules and properties options 138 Creating form level rules 140 Understanding the form rules editor 140 Menus 140 Edits 140 The table rules! 140 Single shot 140 Creating a form level lookup 141 Display order 141 Capitalization 142 Understanding the Expression Builder...............................................................................143 Taking a tour 144 The elements 144 Select fields 144 Select values 145 Function Finder 145 Calculated fields 145 Variables 145 System fields 146 Object properties 146 Library 146 Finding the right function 146 Reference 146 Function Finder 147 Search 147 Category 147 Invalid or incomplete 148 Getting A5 Help 148 Using the library 148 Current or global 149 Description 149 Sample text 149 Add to library 149 Delete or rename 149 Sort 150 New entry 150 Small edits 150 Templates 150

xxi

TABLE OF CONTENTS

Using the EB keyboard 150 Show / no show 150 Arithmetic operators 150 Logical operators 151 True & False 151 Comparison operators 151 Using the toolbar 152 Entering expressions 152 Functions 152 Quotes around text 152 Creating calculated fields ...................................................................................................153 Creating a character expression 153 Using the ALLTRIM function 154 Result 154 Spaces 154 Add elements 154 Saving the expression 155 Invalid expression 155 Using a calculated field 155 Modifying calculated field objects 155 Transparent background 156 Creating a simple mathematical calculation 156 Shortcut 156 Field names 157 Column names 157 Combining field types in an expression 157 Match field types 157 Convert date 158 Syntax 158 Adjust field 158 Editing calculated fields 158 Refining the form ...............................................................................................................159 Using Rich Text objects 160 Text vs. Rich Text 160 Field vs. object 160 Adjustments 161 Copying and pasting objects from another form 161 Group objects 162 Copy objects 162 Position group 162 Changing the tab order 162 Naming the form 163 Creating Smart Fields 164 Calendar, Clock 164 Calculator, E-mail and URL Fields 164 Adding a header and a footer 166 Note about screen resolution 166 Header 166 Footer 166 Shorten form 166 Keep form size 166 Using a Web form on the desktop ......................................................................................167 Understanding Stand-Alone Browses ................................................................................168 Opening the Alpha Sports database 168

xxii

ALPHA FIVE MADE EASY The Basics and More for Version 10

Alpha Sports control panel 168 Copying a browse 169 Modifying the appearance of a browse 169 Stylesheets 169 Alternate row colors 169 Modifying the appearance of a column 170 No properties pane 170 Creating conditional color 170 Default color 171 Conditional color 171 Equation order 171 Row colors 171 Changing the vertical slider bubble help 172 Order records 172 Key value 172 Column sort 172 Reviewing browse properties. 173 Putting a button, a smart field and an ellipsis in a browse .................................................173 Putting a button into a browse cell 173 Button or hyperlink 174 Expression 174 Adding a smart field to your browse 175 Email smart button 175 URL smart button 175 Showing longer field contents 176 Ellipses 176 Using the Ad Hoc browse ..................................................................................................176 Filter expression 177

5 Reports, Letters and Labels

Overview ............................................................................................................................180 Learning Report basics.......................................................................................................181 Powerful editor 181 Creating a Quick Report.....................................................................................................181 Trouble shooting 185 Fields 185 Change field order 185 Move field 185 Remove field 185 Groups 185 Order 185 Summary 185 Options 186 Properties Pane 186 Testing the design of the report 186 Understanding the preview tabs 187 Navigation 187 Unsaved edits 187 Save edits 187 Understanding the preview toolbar 187 Pages 187 Print 187 View 187 Saving reports as PDF, HTML, Rich & Plain Text and Excel files 188 Save as formats 188

xxiii

TABLE OF CONTENTS

189 Save first! 189 Send email attachment 189 Color it! 190 Post it! 190 Excel note 190 Using the panes 191 Navigation 191 Properties 191 Toolbox 191 DragDrop List 191 Using the toolbar 191 Understanding the top menu 191 View 191 Report 191 Examining the report editor sections..................................................................................192 Active section 193 Understanding the Report section 193 Header 193 Footers 193 Understanding Group Section(s) 194 Header 194 Size to fit 194 Footer 194 Recap 195 Understanding the Detail Section 195 Viewing report sections 196 Adding and removing sections 196 Groups 197 Restoring to a prior version 197 Aligning and moving objects .............................................................................................197 Understanding the basics 197 Settings 198 Align 2 objects 198 Size 2 objects 198 Aligning a group of objects 198 Labels and fields 199 Moving a group of objects 199 Undoing a move 199 Hiding a section 199 Closing up the space 200 Customizing reports ...........................................................................................................200 Password 201 Modifying the Report Header.............................................................................................201 Creating a cover page 201 Enlarge section 201 Remove lines 202 Insert page break 202 Analyzing a calculated field 202 Type conversion 203 Save for later 203 Using the DMY function 203 Finishing the cover page 203 Change header color 203

xxiv

ALPHA FIVE MADE EASY The Basics and More for Version 10

Align & center objects 203 Move all objects 204 Add characters 204 Modifying the Group Header .............................................................................................204 Understanding the IF function 205 If statement 205 Workspace Hints 206 Date functions 206 Library entry 206 Putting a border around a field 207 Changing font for several objects at once 207 Viewing the report so far 207 Modifying the Detail Section .............................................................................................208 Resizing the fields 208 Move fields 208 Move labels 209 Move line rule 209 Replacing an existing field with a new field 209 Create field 209 Swap fields 209 Add characters 210 Creating simple numeric calculations 210 Amt due 210 Using a calculated field in an expression 210 Balance due 210 Make labels 211 Removing blank pages and overflow 211 Refining height and position of objects 211 Labels 212 Formatting field objects 212 Alignment 212 Word wrap 212 Width 212 Decimals 212 Number display 213 Coloring alternate rows 213 RECNO() 213 MRECNO() 213 MOD 213 Draw frame 214 Frame genie 214 2-color band 214 1-color band 214 Modifying the Group Footer ..............................................................................................215 Understanding summary fields 215 Group properties 215 Label 216 Examining a group summary field 216 Writing a group summary expression 216 Result 217 Using the Drop Fields genie to create a summary 217 Format numbers 218 Editing a group summary label expression 218 ALLTRIM 218

xxv

TABLE OF CONTENTS

Creating a new group summary label expression 218 Make new field & label 219 Fine tune 219 Putting a box around a field with a certain value 219 Write style expression 219 Modifying the Report Footer..............................................................................................220 Understanding grand total summary fields 221 Using the Calculated Fields dialog 221 Reorder 221 Quick design 222 Name convention 222 Caution 222 Not valid 222 Valid 222 Drag Drop List 223 Change fields 223 Finish section 223 Modifying the page footer ..................................................................................................223 Copying objects from one report to another 223 System fields 224 Copy section 224 Transfer note 224 Pasting the objects 224 Delete objects 224 Paste section 224 Refining your report 224 Enhancing reports...............................................................................................................225 Creating a sub-report 225 Size 226 Filter 226 Edit design 226 Edit settings 226 Save 226 Placement 227 Improving PDF, HTML & Rich Text report formats 227 Save settings 227 Adobe Acrobat PDF File 227 Dynamic HTML File 228 Rich Text file 228 Plain text file 228 Excel 228 Watermarking a PDF report 229 Viewing reports on forms 229 Add toolbar 230 Design mode view 231 Sizing 231 Edit report 231 Filter 231 Print 231 Edit supercontrol 231 Important things to know about reports ............................................................................232 Creating a Custom Report 232 Blank report 232 Understanding properties in reports 233

xxvi

ALPHA FIVE MADE EASY The Basics and More for Version 10

Understanding regions 234 Shrink band 234 Understanding how data fits on the page 234 Overflow 234 Group display 234 Adding columns 235 Troubleshooting 235 Adding or removing the detail section 235 Adding headers & footers 236 Adding and ordering groups 236 Filtering and Ordering reports 237 Filter 237 Order 238 Printing records 238 Current record 239 Importing a layout 239 Forms 239 Browses 239 Creating Letters ..................................................................................................................240 Genie 241 Important things to know about letters 243 Filter 243 More on filtering records for layouts 243 Creating Labels and Envelopes ..........................................................................................245

6 Operations

Understanding Operations..................................................................................................249 Examining the types of Operations 249 Acts on records: 249 Changes fields and/or text 250 Creates a new table 250 Understanding the Operations Toolbar 250 Understanding the Top Menu 251 Importing overview ............................................................................................................252 Classic 252 Alpha DAO 252 ADO 252 Importing Excel files with AlphaDAO ..............................................................................253 Understanding the AlphaDAO Import Genie 253 Selected vs. all 254 Understanding table overwrite 254 Viewing the Excel file. 254 Choosing the import method 255 Establishing the connection 255 Defining the selected object properties 255 Choosing the columns to import 256 Defining the default settings for all objects 256 Running the import 256 Viewing the table 256 Importing multiple Excel pages 257 Importing an ASCII text file ..............................................................................................257 File types 257 Marking records .................................................................................................................260 Using the CONTAINS function 260 Marking and unmarking records manually 261

xxvii

TABLE OF CONTENTS

Creating an Update operation.............................................................................................262 Defining a character string 262 Searching for and replacing a character string 263 Removing the filter in the browse 264 Adding records to a table....................................................................................................265 troubleshooting 267 Deleting records from the table 267 Excluding records from the Append 268 Using Logical operators 268 Marking duplicate records..................................................................................................269 Using the Quick Sort 271 Copying an operation .........................................................................................................271 Deleting duplicate records 271 Deleted records 272 Putting the operations on a button ......................................................................................272

7 Buttons, Hyperlinks and Bubbles

xxviii

What’s ahead in this chapter ..............................................................................................277 Printing a report..................................................................................................................277 Using Action Scripting 278 Testing a button 279 Enhancing the appearance of a button 279 Bitmap / Text options 279 Quick pick 280 Bubble Help 280 Adjusting button placement on the form 280 Customizing a fly-over effect 281 Understanding button events 281 Setting multiple actions for a button ..................................................................................283 Reorder 285 Sound 286 Editing the script 286 Completing the operations 286 Commenting .......................................................................................................................287 Temporarily stopping an action 287 Adding custom graphics for your buttons ..........................................................................288 Embedded vs. reference 288 Putting the graphic on a button 289 Password protecting a button .............................................................................................289 Remove button password 290 Creating A-Z buttons for a directory ..................................................................................290 How it works 290 Button views 291 Hyperlinks 291 Understanding the dialog 291 Alphabet buttons 291 Button appearance 292 Button, Hover, Active, Disabled 292 Defining a horizontal button bar with all letters 292 Stylesheet 292 Creating a vertical bar that uses a range 293 Show more 294 Creating a hyperlink on a form...........................................................................................294 Event 295 Using the button genie to get hyperlink code 295

ALPHA FIVE MADE EASY The Basics and More for Version 10

Converting Action Script code to Xbasic 295 Copying a hyperlink 296 Creating a hyperlink on a report.........................................................................................297 Setting up the links 297 Design homework 297 Making the field look like a hyperlink 297 Choosing the linking layout 298 Matching criteria 298 Using the hyperlink 299 Viewing your changes in the report 299 Single page report 299 Multi-page report 299 Showing more with bubble help.........................................................................................300 Text only 300 Text, fields & images 300 Photos 300 Open file 300 Creating user instructions 300 Unformatted text 300 Formatted text 300 Giving the user extra information 301 Text and fields 301 Time delay 301 Fields and Images 301 Enlarging a photograph 302 Linking to another file 302 Hyperlink opens file 302 Viewing the Bubble Help 302 Time lag 303 Photos 303 Data entry 303

8 Finding, Sorting and Filtering Records

Overview ............................................................................................................................306 Tools for finding, sorting and filtering records ..................................................................307 Sorting and filtering with sets 307 Cross-level queries 307 Testing the results 307 Finding records...................................................................................................................308 Browses 308 Forms 308 Find by Key 308 Basics 308 Sets 309 Record selector 309 Find By Key toolbar 309 Roll-Ups 310 Preferences 310 Quick close 310 Find text 310 Basics 310 Sets 310 Find by Form 311 Basics 311 Sets 311

xxix

TABLE OF CONTENTS

Drop Down Browse search 311 Sets 312 Basics 312 Navigation behavior 313 Unique 313 Filter 313 Columns 314 Lookup 314 Quick Filter 315 Cascading DD Browse 315 Edit DD Browse 315 Browse tips 316 Sorting records ...................................................................................................................316 Sort by column title 316 Sets 316 Sort by Key 317 Sets 317 Multi-field Sort 317 Sets 317 Browses & forms 317 Understanding Indexes 317 Trouble shooting 318 Increasing performance 318 Creating indexes 318 Opening indexes 318 Sorting in reverse order 318 Find by Key 318 Reports 318 Genies 319 Filtering records .................................................................................................................319 Quick Filter 319 Sets 319 Multi-field Quick Filter 320 Sets 320 Multi-Select Filter 321 Sets 321 Query Genie .......................................................................................................................321 Sets 321 Filtering with “and” & “or” 322 Status Bar 323 Viewing the expression 324 Editing the expression 324 Saving the query 325 Editing the saved query 325 Query operations toolbar 325 Attaching a layout to the query 325 Query by Form ...................................................................................................................326 Sets 326 Forms toolbar 326 Toolbar 327 More buttons 327 Using the QBF Helper 327 Editing existing data 328 Add or replace 328

xxx

ALPHA FIVE MADE EASY The Basics and More for Version 10

Quick entry 328 Viewing the expression 328 Setting the order criteria 329 Change order 329 Record number 329 Saving a search 329 Browse view 329 Beginning a new search 330 Opening saved QBF queries 330 Select Index / Query 330 Using sorts, indexes, filters and queries .............................................................................331 Active 331 Saved 331 Using the primary index or query dialog 331 Indexes/Query lists 331 Saved queries 331 Fields 332 Finding and editing saved queries 332 Using saved queries to filter reports 332 Putting the saved query on a button 332 Query by Expression ..........................................................................................................333 Sets 333 Understanding Arguments..................................................................................................333 Logic 333 Databases 333 Using an Argument to filter a report 334 Dialog box 334 Help window 334 Report defined 334 Creating the arguments 334 Argument properties 335 Pick lists 336 Creating the filter expression 337 Understanding arguments in expressions 338 Argument function 338 Viewing the completed expressions 339 Case 340 Adding the full date 340 Finishing the report design 341 Viewing the report 341

9 Data entry made easy

Overview ............................................................................................................................344 Understanding data entry options.......................................................................................345 Form or browse 345 Field rules 345 Data controls 346 Capitalization 346 Using Field Rules to create pick lists .................................................................................346 Drop down table lookups 346 Creating a Simple List 346 Field values 347 Columns 347 Display 347 Exceptions 347

xxxi

TABLE OF CONTENTS

Reorder list 347 Other field rules modifications 347 Creating a Cascading List 348 List 348 Form view 348 Understanding Data Controls .............................................................................................348 You gotta know about Choices! 349 Learning how they work 349 Answering “yes” or “no” 349 Check box & Two-state button 349 Using short list controls 350 Radio & Multi-state buttons, List box 350 Abbreviated entries 350 Using a spin control 350 List box 350 Using longer list controls 350 Combo box & List box 350 Using controls for lists with sub-categories 351 Tree and Drop Down Tree controls 351 Using table records in controls 351 Columns 351 Using the advanced controls 352 Creating Data Controls .......................................................................................................352 Part 1: Creating the data control object on a form 353 From within the field properties box 353 From the toolbox 353 Part 2: Entering choices for each type 353 Creating “Yes” and “No” responses 353 Control types 353 Labels 354 Setting up lists 354 Control types 354 Enter values 354 Alphabetizing 355 Setting up lists with sub-categories 355 Control type 355 Showing table records 355 Control types 355 Record List controls 355 Combo and list boxes 356 Data entry made faster 356

10 Graphics in the database

xxxii

Graphics bring your database to life…...............................................................................358 Tomorrow is here with Alpha Five ....................................................................................359 Understanding the terms 359 Images 359 Icons 359 Understanding field and file types 359 Field types 359 File types 359 Using the graphic design features 360 Basic controls 360 Advanced controls 360 Static and dynamic control types 361

ALPHA FIVE MADE EASY The Basics and More for Version 10

Static 361 Dynamic 361 Understanding the HTML Editor 361 HTML Memo fields 361 Full HTML Editor 361 WYSIWYG & Source & Xbasic 362 Using image fields in forms and browses ..........................................................................362 Sizing the file: JPG, BMP, etc. 362 Image fields 362 HTML editors 362 Pixels 362 Adding an image field to a form 363 Appearance 363 Enter / Change Record 363 JPEG vs. JPG 363 Using an HTML Memo field 364 Open a file 365 Table & URL 365 Creating an HTML window on a form...............................................................................365 Editing the HTML 367 WYSIWYG or Source 368 Sizing the form 368 Adding Icons to browses ....................................................................................................369 Adding the graphics to the database 369 Creating the Icon column 370 Expression column 370 Unbound file type 371 Adding a condition manually 372 Editing images and/or conditions 373 Show code 373 Conditions 373 Changing the (genie) entries 373 Conditions & images 374 Changing manual entries 374 Change condition 374 Change images 374 Getting more information from the Internet.......................................................................375 Understanding the pre-defined templates 375 Using the Yahoo Maps template 375 Type and mapping 375 Preview web page 376 Connecting the forms 376 Using Google Search 377 Using Google Image Search 378 Linking to a URL 378 Creating a new template 378 Adding a clock and a calendar ...........................................................................................379 Entering and changing dates with the calendar & clock 379 Date Field 379 Short Time Field 379 Time Field 380 Entering and changing dates by typing 380 Using the Calendar and Clock Supercontrols 380 Change date/time with supercontrol 381

xxxiii

TABLE OF CONTENTS

11 SQL and Alpha Five

xxxiv

SQL Filter Builders ............................................................................................................384 What is SQL? .....................................................................................................................385 Using SQL databases with Alpha Five...............................................................................385 Understanding Active– and Passive–Link Tables 385 Active-Link 386 Passive-Link 386 SQL to DBF 386 Using the built-in drivers 386 Supported drivers 386 Creating a new database .....................................................................................................387 Adding an Active-Link table to the database .....................................................................387 Single table 387 Multiple tables 387 Understanding the Connection String 387 Named 387 Ad Hoc 388 Creating a new Named Connection String 388 Passwords 388 Multiple saved connections 389 Adding a single active-link table 389 Columns 390 Custom SQL statement 390 Where Clause 390 Arguments 390 Changing field definitions 390 More options 391 Adding multiple tables 392 Views & tables 392 Editing table definitions 393 Update View tables 393 Primary Key 393 Additional options 394 Editing Field Rules 394 Refreshing the table definitions 394 Converting connection strings 394 Moving from SQL to DBF 395 Using Active-Link tables....................................................................................................395 Understanding queries 395 Client-side 395 Server-side 396 Automatic 396 Who chooses? 396 Using the client-side query genie 397 Saved queries 397 Using the server-side query genie 397 Builders & Buttons 398 Filter Builders 398 Order tab 399 Preview tab 400 Run saved query 400 Delete, rename, duplicate 400 Setting query preferences when adding/editing table 400 User privilege 400 Understanding record locking ............................................................................................401

ALPHA FIVE MADE EASY The Basics and More for Version 10

Abandon changes 401 Examine conflicts 401 Refreshing the view of the record updates 402 Using Passive-Link tables ..................................................................................................403 Named Data Source 403 Edit structure 404 Queries 404 Reviewing the DataSource definition 404 Editing the DataSource definition 405 Refreshing the data 405 Editing Field Rules 406 Moving from SQL to DBF 406 More help references ..........................................................................................................406

12 Action Scripting, Xdialog, Custom Menus and Toolbars

Giving and receiving ..........................................................................................................411 Action Scripting 411 Custom menus and toolbars 411 Wait Dialogs 411 Xbasic 411 Xdialog 411 Zoom, zoom with Action Scripting ...................................................................................411 Editing an event 413 Enhancing your forms 414 Copying an Action Script 414 Creating custom menus and toolbars..................................................................................415 Changing the right click menu for a form 415 Set context 416 Hot key 417 Form settings 417 Field menus 418 Trouble shooting 418 Print preview 418 Creating a custom toolbar 418 More options 419 Print preview 419 Creating a dialog box that shows script progress ...............................................................420 Showing percentage of completion 422 Enhancing the directory......................................................................................................423 Form elements 423 The basic elements 424 Browse object properties 424 Object names 424 Column properties 424 Introducing Variables 425 Naming convention 425 Events 426 Placing the objects on the form 426 Frame 426 Combo Box 426 Embedded Browse 427 Browse column 427 Alphabet bar 427 Adding the code 428 Combo box On Change Event 428

xxxv

TABLE OF CONTENTS

Form On Change event 428 Testing the form 429 Troubleshooting 429 Object Names 429 Variable 429 Code 429 Creating a filtered dialog ....................................................................................................429 List types 430 Creating and storing scripts 430 Creating the client list 431 Script genie 431 List view genie 432 Preview 433 Creating the registrations section 435 Linking field 435 Arguments 435 Filter / Where Clause 436 Adding a search box 437 Argument 437 Filter/Where Clause 438 Trouble shooting 438 Running the script at the Code tab 439 Applying the script to a form 439 Using a grid component as a desktop form ........................................................................440 Web Projects Control Panel 440 Setting up the Grid Component with Xdialog 441 Styles 443 Custom backgrounds 443 Preview 443

13 Handy Things to Know

xxxvi

Overview ............................................................................................................................447 Compatibility with Windows Vista & Windows 7 ............................................................447 Database maintenance ........................................................................................................447 Updating Indexes 447 Alpha Five software updates 448 Backing up the database 448 Creating backup settings 448 Using One Step Backup 449 Create backup schedule 450 Restoring the database 450 Compacting the database 450 E-mailing a database 451 Getting rid of the $$$$$$$$$$$$ 451 Deleting temporary files 451 Packing Tables 451 Resetting Auto Increment values 452 Alpha Five file types ..........................................................................................................452 Database 452 Script Library 452 Tables 452 Data Dictionary 453 Sets 453 Data dictionary 453 Customizing your Alpha Five startup ................................................................................453

ALPHA FIVE MADE EASY The Basics and More for Version 10

Creating a desktop icon 453 Naming the title and start bars 453 Creating a startup form 454 Hiding the Control Panel 455 Showing the Control Panel 455 What to do if you’re locked out 455 Database operations............................................................................................................456 Defining data entry mode 456 System default 456 Individual form or browse 456 Defining date entry defaults 457 Date Years 457 Month/Year 457 Importing and Exporting files 457 Import 457 Export 457 Transfer data to Excel or Word 457 Microsoft Excel 457 Microsoft Word 458 Return to A5 458 Naming conventions 458 ODBC connection 458 Opening Alpha Five 458 Turning the welcome screen on and off 459 Editing the Recent Database List 459 Setting up security 460 Passwords 460 Encryption 460 Object Level Security 460 Turning XP Themes on or off 460 Understanding Aliases 461 Image Files on multiple computers 461 Tables .................................................................................................................................462 Sharing Tables 462 Adding a Table 462 Deleting a table 462 Effect on sets 462 Dropping a table 463 Moving or Copying a table 463 Emptying the table 463 Reusing a favorite table 463 Creating a Template 463 Records...............................................................................................................................464 Deleting and undeleting records from a table 464 Deleting a single record 464 Deleting multiple records 464 Undeleting records 464 Deleting duplicate records 465 Copying records from one table to another 465 Removing certain records from a table 466 Fields ..................................................................................................................................466 Field widths 466 Table structure 467 Layouts 467

xxxvii

TABLE OF CONTENTS

Changing decimals 467 Trouble shooting 467 Memo Fields 467 Field Rules..........................................................................................................................467 Apply changes 467 Restore prior 467 Layouts — Browses, Forms, Reports, Letters, Labels .......................................................468 Restoring and managing layout and field rules backups 468 Restore prior version 468 File structure 468 Clean single layout 468 Clean multiple files 468 Recent edits 468 Sending and receiving layouts 469 Sending 469 Receiving 469 Print and/or send records with current layout 469 Print button 469 Quick print a browse 470 Viewing structure information 470 Ungrouping the DragDrop list 470 Copying a layout 470 Deleting a layout 472 Changing the form window style 472 Customizing the form title bar 473 System menu 473 Setting up a menu 473 Finding the source of the layout 474 Understanding the Trace Window 474 Warning message 474 Trace events 474 Understanding Unbound fields 475 Functions and Expressions .................................................................................................475 Help with Functions 475 Expression Builder 475 Genies 475 Books 476 Entering text in expressions 476 Error messages 476 IF statements 476 Character 477 Numeric 477 Mixed field types 477 Conditional color and attributes 477 Nested IF statements 477 ISBLANK AND ISNOTBLANK 477 LOOKUP Function 478 Understanding Xbasic terminology....................................................................................478 Scripts 478 Code 478 Code Editor 478 Code tab on the Control Panel 478 Interactive window 478 Script Recorder 478

xxxviii

ALPHA FIVE MADE EASY The Basics and More for Version 10

Background Xbasic 479 Action Scripting 479 Events 479 Genies 479 Xdialog 479 Assuring successful script writing 479 Commenting 479 Exploring the Alpha Five reference library........................................................................479 Learning Functions and Expressions 479 Learning Xbasic 480 Learning Action Scripting 480 Learning Xdialog 480 Learning to write web applications 481 Help! ...................................................................................................................................481 Alpha Software Website 481 The Alpha Community 481 Message Board 482 Add-Ins 482 Books 482 Videos 482 Documentation and technical articles 483 Technical Support 483 Pro services 483 Blog 483 Software updates 483 Alpha Software, Inc. 483 Tips and Tricks 483 Frances Peake 483

Full Index ..........................................................................................................................485

Bonus Pages!

14 Color and Style

Locating color and pattern options.....................................................................................503 Using object properties to select color and patterns...........................................................503 Choosing colors 504 System colors tab 504 More colors 505 Custom palette 505 Custom colors tab 506 Recent colors tab 506 Selecting a fill style 506 Solid & Transparent & Translucent 506 Gradient Horizontal & Vertical 507 Gradient Radial 507 Pattern & Percent 507 Adding borders and shadows 508 Using the properties pane to select color and pattern 508 Percentages 508

xxxix

TABLE OF CONTENTS

Transparent button 509 Conditional color 509 Understanding Styles and Stylesheets ................................................................................509 Forms 509 Browses 509 Reports 510 Creating and applying stylesheets ......................................................................................510 Background 511 Field object 511 Text object 511 Button object 511 Saving the form and creating the stylesheet 511 Applying a stylesheet to an existing form 512 Trouble shooting 512 Updating stylesheets 512 Deleting stylesheets 513 Setting the default stylesheet 513 Understanding Styles..........................................................................................................513 Using an object to create a new style 513 Updating an existing style 514 Choosing an existing style 515 Managing styles 515 Embedded Browse 516 Type In field 517 New styles 517 Deleting styles 518

15 E-mail and Fax with Alpha Five

xl

Communicating with Alpha Five .......................................................................................520 Templates 520 E-mail clients 520 E-mail Database 520 Mass e-mails 520 Fax 520 Creating an E-mail Template .............................................................................................520 Choosing the e-mail client 521 Mail Merge 521 Creating the message 522 Insert command (Conditional) 522 Spell check 523 Preview message 524 Using an e-mail template 524 Button runs template 524 Personal note 526 Editing a saved template 526 Using a Smart Field to run a template 527 Understanding the E-mail client options ............................................................................527 Alpha Five e-mail client 528 Default e-mail client 528 Entering the settings 528 Find your e-mail account settings 528 Profiles 529 Gmail 530 Error messages 530 Testing send e-mail for both clients 530

ALPHA FIVE MADE EASY The Basics and More for Version 10

Single profile 530 Multiple profiles 530 Reading e-mail with the A5 e-mail client 531 Receive test 531 Download messages 531 Understanding folders 531 Creating a new folder 532 Move a single message 532 Move a group of similar messages 532 Read e-mail button 533 Creating a smart button for e-mail fields. 533 Default e-mail client 534 Alpha Five e-mail client 534 Sending mass e-mails with AlphaMailer............................................................................535 Requirements 535 Understanding how AlphaMailer works 536 Tables 536 List maintenance 536 Opening an account 536 Instruction downloads 537 Reviewing the Alpha Five Mailer tabs 537 Understanding the passwords 539 Alpha Mailer account User Name & Password 539 Local Password 539 Finding your AlphaMailer account password 539 Changing your AlphaMailer account password 540 Setting up your account on another computer 540 Setting up the tables 541 Creating a list 541 Exporting the data to AlphaMailer 543 Profiles 546 Composing a new message 548 From e-mail address 548 From display name 549 Personalizing the subject and/or message 549 Green Box 549 Syntax 549 Writing the e-mail 549 Subject line 549 Text or Html 549 Inserting hyperlinks 550 Inserting an image 551 Inserting an “unsubscribe” link 553 Enlarging the window 553 Saving your composition 553 Plain text message 554 Click tracking 554 Open tracking 555 Embed images 555 Do not send duplicates 555 Other options 555 Spam check 555 Render 555 Delivery test 556

xli

TABLE OF CONTENTS

Sending the mass mailing 556 Hyperlink 557 Administrator e-mail 557 Viewing the reports 557 Stored data 558 Understanding bounces 559 Mailing bounces 559 All bounces 559 Editing an existing message 559 Saved as file 559 Adding a logo and company info 559 Upload Image 559 Updating Lists 560 Looking at more features 560 Automated messages 560 Website tracking 561 Viewing the fee schedule 561 Contacting Support 561 Sending a Fax .....................................................................................................................561 Windows Fax Service 561 Personal configuration info 562 Fax configuration info 562 Cover Pages 562 Sending a Windows fax 563 Faxing an Alpha Five Report 563 More about cover pages 563 Busy line 564 Changing a fax status 564 Completed fax 564

16 Alpha Four to Alpha Five: The Transition

xlii

Frances M. Peake ...............................................................................................................569 Overview ............................................................................................................................569 Windows databases ............................................................................................................569 Standard elements 570 Alpha Five terminology......................................................................................................570 Opening an existing database .............................................................................................571 File in list 572 File not in list 572 A quick tour around Alpha Five.........................................................................................573 Viewing the Alpha Five Control Panel 573 The Tables/Sets tab 574 Table and Set icons 575 The Layouts tabs 576 The Operations tab 577 Alpha Five file types ..........................................................................................................577 Database file types 578 The Database 578 The Script Library 578 Table file types 578 Data Dictionary 578 Set file types 578 Data Dictionary 578 Naming Alpha Five database objects 578 Using long names 578

ALPHA FIVE MADE EASY The Basics and More for Version 10

Avoiding spaces 578 Reserved words 579 Selecting names 579 Creating an Alpha Five database and pulling your Alpha Four data into it .......................579 Creating a new Alpha Five database 580 New folder 581 Copying your Alpha Four files 581 Adding tables to your Alpha Five database 583 Using the Conversion Tools ...............................................................................................584 Shortcuts for moving calculations and expressions into Alpha Five 586 The 3- Step Plan .................................................................................................................589 Strategies for developing, testing and making the final data conversion 589 Emptying the Alpha Five table 589 Appending current Alpha Four records 590 Indexes and their new role in Alpha Five...........................................................................592 Required indexes are created automatically 592 Sort and Find by Key 593

xliii

TABLE OF CONTENTS

Notes:

xliv

Chapter 1.

Databases and Tables

From paper to power. Choose your file type - DBF or SQL. Alpha Five is the database for non-programmers and experts alike. Its ease of operation allows even those previously inexperienced to convert paper to electronic power. Its Xbasic programming language sends developers on a quick, easy path to further customizing.

CHAPTER 1. DATABASES AND TABLES

Notes:

2

ALPHA FIVE MADE EASY The Basics and More for Version 10

Creating a new database • In this exercise, we will create a new database and enter a table into that database. VERY IMPORTANT!

Be sure to put each database in its own folder. Many files will be created and you don’t want them mixed up with the Alpha Five program files.

1.

Start Alpha Five Version 10 from the

Start menu or the program icon. •There are 2 tabs on the opening screen.* •News & Updates •Databases & Tasks

2. Tab: News & Updates: This screen is actually a web page.† It reports new information from Alpha Software and gives helpful links. If your software is out of date, you will get a warning in a red box with a link to download the latest patch.‡ •You can also access the download files from within Alpha Five: From the Top Menu, choose Help > Download patches.

3. Tab: Databases & Tasks. Click Create a new, empty database. •If you don’t see the above screen, open Alpha Five; choose File > New Database.

4. Click OK at the following screen. 5. At the Create New Database window, navigate to the c:\A5_Ver10Book\ folder. 6. Create and open a new folder** with the following name:

c:\A5_Ver10Book_MyNewDB.

*. This screen is sometimes referred to as the Welcome Screen. †. As in many other instances, this screen is dynamic, meaning it may change from time to time depending on your most recent update. ‡. Updating always follows the same procedure: 1. At the Alpha website, choose the appropriate file for your software edition. 2. Download. 3. Before running, close Alpha Five—not always required, but a good practice. 4. Follow instructions. **.Putting the database in its own folder is very important.

3

CHAPTER 1. DATABASES AND TABLES Understanding Tables and Fields

a. To

make a new folder, click the Create New Folder button.

b. Over

type New Folder with the new name (step 6 above) (Press ENTER)

c. Double

click the folder to open.

7. File Name: Enter the name of the database.

A5_Basics 8. Click Save. •The database is created and the Alpha Five Control Panel appears.

Understanding Tables and Fields PLEASE TAKE A MOMENT TO READ THIS

Tables are composed of 15 types of fields. •Character •Numeric •Date •Logical •Time •ShortTime •ExponentNumeric •Memo •Bitmap •OLE •Rich Text Memo •JPeg Image •Image File Reference •HTML Memo •UUID

CHARACTER

The most common field type, it holds alphanumeric data. Character fields may contain letters and numbers. When you are planning to store numbers in a field, consider the following: •Character fields can show leading 0’s. [For example, Zip Code= 07760] Numeric fields can not.

4

ALPHA FIVE MADE EASY The Basics and More for Version 10

•Character fields can have both letters and numbers [C 0556]. Numeric fields can not. •Character fields can have other symbols: [Phone = (732) 842-3000] Numeric fields can not. For some character fields, the width is arbitrary, such as first name, company, city, etc. Others, such as phone numbers and zip codes are determined by the complete number of characters they will hold. For example, in the next lesson, we will use field rules to set up phone numbers to be viewed as (732) 842-3000 (14 characters) and zip code as 07760-0453 (10 characters). NUMERIC

Fields on which you might want to perform calculations. Numeric fields accept only numbers and the decimal point. •When planning the width of a numeric field, include the decimal point, decimal places and one extra space for a minus sign, even if you do not plan to enter negative numbers. For example, if the largest number you plan to store in a field is 9999.99, make the field width 8 with 2 decimal places. •In the Form and Report editors, you will be able to format numbers to display dollar signs ($500.00) and commas (400,000.00).You will be able to show fewer decimals on reports, forms, etc. than created in Structure Table, but not more. •For example, a field structured at 4 decimals could read 400.9099 on a report. It could also be set to 2 decimals at the report level, in which case it would round to 400.91.*

DATE

LOGICAL TIME

This field type only accepts valid dates. It is aware of the calendar, including leap years. Automatically set to a width of 8. See “Enter Date Field” on page 28 and “Using Rich Text objects” on page 160. Contains one character, which is used to represent a TRUE or FALSE value. Shows date and time. This field type only accepts valid time format. Automatically set to a width of 17. See “Using Rich Text objects” on page 160. •11/30/2005 12:00 00 pm

SHORT TIME

This field type only accepts valid time format. Automatically set to a width of 9. See “Using Rich Text objects” on page 160. •12:00 00 pm

EXPONENT NUMERIC MEMO

A fixed precision decimal number. A 10-character width is automatically created. In actual use, the field expands. The only limit to the number of characters is available disk space. •Formatting of characters not available. (see Rich Text Memo below.)

BITMAP

Stores bitmap (.BMP) pictures as data in the table.

OLE

Used to link a document, such as a spreadsheet or graph, using the OLE method.

*. The ROUND function is also available to round decimals to a certain number in calculations. See A5 Help > Index Tab > Keyword: Round. We’ll talk lots more about functions as we work through the book.

5

CHAPTER 1. DATABASES AND TABLES Understanding Tables and Fields

RICH TEXT MEMO

This is the second type of memo field available. It, too, is automatically set to a width of 10. It differs from the Memo Field in the following manner: •Paragraph formatting is available. •Character formatting, such as bold, underline, color, etc., is available. •Fields can be inserted.

JPEG IMAGE

Stores JPEG (.jpg) pictures as data in the table. JPEG files are much smaller than bitmaps. Bitmaps may also be stored in JPEG fields. When bitmaps are stored here, they are automatically converted to JPEG files.

IMAGE FILE REFERENCE

This field type is also called a “linked image field” because the image is not stored directly in the database. Only the reference is stored. Since graphics files can be quite large, using links can result in a significantly smaller database. To learn how to enter references, go to A5 Help > User Guide >Index tab > Enter: Image File Reference Control (Form) > Choose: Placing on a form.

HTML MEMO

This is the third type of memo field available. It, too, is automatically set to a width of 10. It allows access to the HTML* Editor where web pages can be created and stored in the database, using either WYSIWYG† or HTML.

UUID

This field returns a UUID (Universally Unique Identifier), similar to a GUID (Globally Unique ID). It is intended for advanced SQL users. An example might be {fj56co90-et23-r5i6-564w-rr45-rk34rbl5}.‡

HINT!

If you plan to move your database from one drive to another, it is a good idea to set up Aliases in order to maintain links. (See “Understanding Aliases” on page 461.) In planning tables, keep the following in mind: •The width set for a field governs the size of the table because space is reserved even if the field is empty or partially filled.

GO TO ALPHA 5 HELP

In this series of lessons, we will use the following field types: Character, Numeric, Date, Logical, Memo, Rich Text Memo, JPEG Image and HTML Memo. To learn about the other types and more about fields in general, see A5 Help > Alpha Five User Guide > Databases > Creating Tables. (For more information on using the help file, see “Tips on searching the Help files” on page x.)

Creating a new table 1. On the Alpha Five Control Panel, click the Tables/Sets tab. 2. Click the New button. PLEASE TAKE A MOMENT TO READ THIS

There are several methods for creating a table. • The first three, Design mode, Text mode and by Example are intended for Alpha Five users.

*. HTML: Hypertext Markup Language. This is the code for writing web pages. †. WYSIWYG: What You See Is What You Get. Even the uninitiated can create web pages by entering text and/or pictures in the HTML Editor. The code is created in the background and can be seen at the Source tab. ‡. Alpha Five also has an easy-to-use feature that provides a unique ID for each table. (See “Defining Auto Increment fields” on page 23.)

6

ALPHA FIVE MADE EASY The Basics and More for Version 10

• The next two, Passive-Link and Active-Link, are for remote database users.* • Mapped Tables are discussed in “Understanding Mapped Tables” on page 56. on page 56. • A Set is a combination of tables and is discussed in Chapter 3. • Create using Genie (at the bottom of the list) has some interesting pre-designed tables for certain pursuits such as Artists, Home Inventory, Invoice Header and Line Items. You may want to look at the selection to see if it could be useful to you. • Click the Preview button to see the fields in the table. In addition, the “New” button accesses two methods for importing text into tables. •Import from SQL Database. •Import from ASCII File. We will use the classic method, Design mode because it best illustrates the principles of table design. GO TO ALPHA 5 HELP

Once table design is understood, you may wish to experiment with the other styles. To see them illustrated, go to A5 Help > User Guide > Databases > Creating Tables > Create a New Table, Create a New Table from Sample Data and Create a New Table by Example.

3. Choose New Table (Design mode) from the drop down list. •Be sure the Tables/Sets tab is active. •The Create Table window appears: •This is where fields are created.

4. Click in the Type field and open the selection box to see the choices. HINT!

Since the field types all begin with a different letter, pressing the first letter will quickly insert the type. For example, pressing C will select Character.

WINDOWS HINT!

For quick keyboard access to drop down lists such as the list of field types, use ALT + DOWN ARROW. This will work in all Windows applications. • Next, we will set up the table.

5. Click in the first entry box of the Name column. 6. Enter the fields as follows: •Press TAB to navigate between cells. NOTE

Notice that Alpha Five replaces the spaces in field names with underscores. That is, First Name becomes First_Name. The case is also set by the program, so first name will also become First_Name after you save the table.

*. See Chapter 11, “SQL and Alpha Five” on page 383.

7

CHAPTER 1. DATABASES AND TABLES Understanding Tables and Fields

The last column, Indexed?, is discussed on page 317. No entry is necessary at this time. Width

Decimal

Client ID

Name

Character

Field Type

5

0

Enter Date

Date

8

0

First Name

Character

12

0

Middle Initial

Character

2

0

Last Name

Character

20

0

Company

Character

35

0

Address 1

Character

25

0

Address 2

Character

25

0

City

Character

25

0

State

Character

2

0

Zip Code

Character

10

0

Phone

Character

14

0

Fax

Character

14

0

E-mail

Character

30

0

Website

Character

40

0

Notes

Memo

10

0

7. Save the table: • There are three ways to save in Alpha Five. All are common Windows commands: a. Choose

BASIC SKILL: *CTRL + S

File > Save.

b. Press

CTRL + S.

c. Click

the Save button at the upper left of the screen.

We prefer CTRL + S because it’s faster and will use this method in our lesson instructions. You may prefer to use a different method. •The Specify Table Name window opens. The left window lists the tables in the current database. This will help you in locating a unique name for your new table. Since this is the first table in the new database, the left window is empty. •By default, the table will be saved in the same folder as the database.

*. Basic Skills are preferred methods and will be used throughout this book.

8

ALPHA FIVE MADE EASY The Basics and More for Version 10

8. Type in the file name: Clients. (Click OK) •After saving, you are returned to the Restructure Table window.

9. Click the X in the upper right of the dialog to exit. •The newly created table appears in the list. •This table has only 3 types of fields: Character, Date and Memo. •To see other types of fields and further explore the Control Panel, we will close this database and open the ABC_Seminars database, the basis for most of the lessons in this book.

Closing the database You can close the database in several ways. The first two of the following methods also close Alpha Five. The third keeps Alpha Five open, but closes the current database and opens a different one. •Click the EXIT button when you are at the Alpha Five Control Panel. •Click the X in the upper right corner of the Alpha Five window. •Switch to another database as we will do in the next exercise.

Opening multiple databases You can have more than one Alpha Five database open at one time by opening another instance at the Windows Start Menu or by clicking on the program icon on the desktop.

Understanding the Alpha Five Control Panel PLEASE TAKE A MOMENT TO READ THIS

The Alpha Five Control Panel is its main menu and the heart of Alpha Five design. It lists the Tables, Sets, Forms, Browses, Reports, Letters, Labels,* Operations and Codes that have been created for the database.† • This exercise continues from the previous one. The A5_Basics database is still open. We will now switch to another Alpha Five database and the first will close automatically. • You can also open this database directly from the Alpha Five opening screen.

1. Top Menu: Choose File > Open Database. 2. Navigate to c:\A5_Ver10Book\ABC_V10_Lessons\ABC Seminars (alpha5 Document). 3. Click Open. •The database opens at the ABC Seminars menu. *. Envelopes may also be made at the Labels tab. †. Depending on the version of Alpha Five that you are using, additional tab(s) may be shown. This manual covers only the tabs mentioned above. All tabs are covered in this book

9

CHAPTER 1. DATABASES AND TABLES Understanding the Alpha Five Control Panel

Taking a tour* 4.

Click on the Control Panel tab in the Window Bar at the bottom of the screen to bring the Control Panel to the front. 5. On the Alpha Five Control Panel, choose the Tables/Sets tab. When you install the Alpha Five program, the database objects will appear as large icons, as shown in the following illustration. We recommend changing the view so that you can see more objects and get more information about them.†

6. Click Detail View, the last graphic to the right (in orange at left). •The full name of the path and the date last modified is now shown next to the name of the Table or Set. CONTROL PANEL TABS

•The tabs are: Tables/Sets, Forms, Browses, Reports, Letters, Labels, Operations and Code.

If you do not see these tabs on the Alpha Five Control Panel a. Right-click

on any tab.

b. Be sure that the Layouts is unchecked. c. Choose the tabs to match the list above.

TABLES/SETS TAB

When developing a database, you can use the Tables/Sets tab as a take-off point for most design tasks. In this section, we will use the right-click menus frequently. You can also access Alpha Five’s features by choosing from the menu bar and toolbars at the top of the Alpha Five program window. • The tables are identified by a single box icon. (The triple box icons are for sets, which will be discussed in a later chapter.) Following the instructions below, exam-

*. Excerpted from Alpha Four to Alpha Five: The Transition, by Frances M. Peake, 2008. Available as a PDF download at www.proctorandpeake.com and www.libertymanuals.com. †. Depending on how Alpha Five was last opened, your screen may show a different view.

10

ALPHA FIVE MADE EASY The Basics and More for Version 10

ine each table closely and review the fields; we will be referring to them often. The tables in this database use numeric, date, logical and rich text fields.

The layouts tabs are: Forms, Browses, Reports, Letters and Labels.

LAYOUTS TABS

• Use these tabs to: •Create and save new layouts •Edit, view and delete existing layouts •Preview and print layouts Operations perform tasks on groups of fields, records and tables and can be saved for future use.

OPERATIONS TAB

Acts on:

Action

Fields:

Convert case of fields (For batch operation of imported or changed records. Most case conversion is done in Field Rules (see page 24).

Records:

Append records from one table to another Convert case of fields Copy records to a new table Cross-tabulate records Delete, mark or unmark records Delete, mark or unmark duplicate records Export records Import records Intersect or subtract records by comparing two tables Post records from one table to another Query, search and replace records Summarize records to a new table Update records

Tables:

Join tables

Changing the table structure You can add and remove fields and reorganize their positions. You can also change the size of a field, but be careful about making it shorter, because you could loose existing data. CAUTION

These tables are the basis of most of the exercises in this book. Do not save any changes unless instructed to do so.*

1. Tables/Set tab: Right click on the clients table.

*. The tables can be restored to their original state by reinstalling the files from the CD or download file.

11

CHAPTER 1. DATABASES AND TABLES Naming your database and its objects

•The right click menu opens. BASIC SKILL: RIGHT-CLICK MENUS

Alpha Five uses Right Click Menus extensively. When you’re looking for something, just right click—it might be right in front of you!

2. Choose Edit Structure... from the options. •The Restructure Table dialog opens.

WARNING MESSAGE

After you make changes, you will get a warning about changing the table structure. If you are confident about your modifications, click Yes accept the alert.

3. Don’t make any changes, just close the Restructure card by clicking the X in the upper right corner of the window.

Planning table design It is important to note that disc space is reserved for each field in the table, whether the field is filled fully, partially or not at all. While not the issue it once was because database files are relatively small in relation to today’s large hard drives, it still makes sense to make fields large enough to hold the data, but not overly large. Don’t create an elephant when a mouse will do.

Naming your database and its objects* PLEASE TAKE A MOMENT TO READ THIS

USING LONG NAMES

There are certain considerations in naming the database and the tables, sets, forms, etc., of which it is composed. Frances M. Peake has outlined the best procedure, based on her many years as a professional database developer. Use long names - but not too long. •Make names long enough to be helpful, but not too long. If you use a lot of long names, you risk running out of space when defining a calculated field expression, for example.

*. Excerpted from Alpha Four to Alpha Five: The Transition, by Frances M. Peake, op cit.

12

ALPHA FIVE MADE EASY The Basics and More for Version 10

•Also, space for displaying fields and file names is limited on some of Alpha Five’s windows, dialog boxes and genies. If you use very long names, you may not be able to see the entire name on the screen. AVOIDING SPACES

Avoid spaces and special characters •Avoid using spaces and characters other than A-Z, 0-9 and _ (the underscore) in names. There are a few places in Alpha Five that seem to have difficulty interpreting spaces and special characters. Further, it is easier to refer to objects in Xbasic when they do not have spaces.

RESERVED WORDS

Avoid using names that are already used by Alpha Five, such as file, field, or email. •Alpha Five will rename these illegal object names for you on form and report layouts, but Xbasic may have difficulty interpreting them correctly. Instead of file, field, or email, use more distinct names such as file_num, field_of_study and cust_email.

SELECTING NAMES

Be descriptive when choosing the names. •When naming layouts (forms, reports) and saved operations (queries, updates), be descriptive and refer to the table or set it is based on. For example, name the data entry form for clients “Client” or “ClientEntry”, not simply “DataEntry.”

Incorrect – One of these forms is for Clients and the other is for Registration, but how would you know?

Correct – At a glance, you will know on which table the form is based.

• Alpha Five scripts refer to database objects by name only. This is another reason to differentiate names of objects of the same type.

Using a naming convention Use a naming convention that will keep similar objects organized alphabetically. Adopt a formal one or devise your own at the beginning of the development process and be consistant. A widely accepted naming convention for databases is the Leszynski Naming Convention or Hungarian Notation. A web search will direct you to numerous articles on this subject. You can adopt—or adapt—one of these or devise your own at the beginning of the development process.

13

CHAPTER 1. DATABASES AND TABLES Opening an existing database

Opening an existing database PLEASE TAKE A MOMENT TO READ THIS

Follow this procedure any time you want to open an existing database when (A) Alpha Five is closed, (B) if the Databases/Tasks screen does not appear or (C) if Alpha Five is already open.

A. When Alpha Five is closed. 1. Start Alpha Five from the Start menu or the program icon. 2. Tab: Databases & Tasks:

FILE IN LIST

• If you have previously opened the existing database, it should appear on the list.

3. Highlight a selection (red box above) to view its path (below the Recent Database list window). FILE NOT IN LIST

• To open a database that is not on the list: a. Click

Open an existing database.

b. Choose c. At

More Databases (blue box above). (Click OK)

the Open Database window, navigate to the desired folder.

d. Click

on the file with the tiny Alpha Five logo next to it •File type: alpha5 document.

•If you have put each database in its own folder there will be only one such file. •The next time you open Alpha Five, the database should appear on the Databases/Tasks list. •The ones most recently used will be at the top of the list. EDIT LIST

You can clean up the list if it gets long and you want fewer recent databases to show. a. Click

SAMPLE DATABASES

Edit List (green box above).

Also listed are a group of databases that are shipped with Alpha Five. These are excellent learning tools. On most computers, they may be found in the c:\Program Files\A5V10\Samples folder.

B. When Alpha Five is closed and there is no Databases/ Tasks screen:*

14

ALPHA FIVE MADE EASY The Basics and More for Version 10

•Begin at step 1 following. (If a database is already open, this method opens a 2nd instance of Alpha Five.)

C. When Alpha Five is already open. •Start at step 2 following. (If a database is already open, this method closes the first and opens the second in its place.)

1. Start Alpha Five from the Start menu or the program icon. 2. Choose File > Open Database. 3. Navigate to the database as directed in the lesson. (Click Open). You may close the database or leave it open if you are continuing to the next chapter.

Congratulations! You now have the skills necessary to create a database and the tables of which it is composed. Summary

New databases are defined at the Databases & Tasks tab of the Welcome Screen or by choosing File > New Database when the program is open. Putting each database in its own folder is critical because many files are created for each and it would be chaos to have them mixed in with the program files or those of another database. An existing database is opened at the Databases & Tasks tab or at File > Open Database from within the program. The Control Panel is the hub of the program. A database is composed of tables. Tables are composed of 15 types of fields. Tables are created by clicking the New button on the Alpha Five Control panel. Tables are edited by right clicking on the table and selecting Edit Structure. • Functions and Expressions covered in this section: •ROUND: Rounds calculation to the specified decimal point (see footnote on p. 5).

*. The developer is given the option to keep the Databases/Tasks screen from appearing. To turn them on or off, go to “Turning the welcome screen on and off” on page 459.

15

CHAPTER 1. DATABASES AND TABLES

Notes:

16

The Basics and More for Version 10

     

How to design your database without code. How to enhance your application with images. How to create fantastic forms and rockin’ reports. How to create dynamite dialog boxes. How to use Alpha Five with your SQL database. And more, more, MORE!

This is the book you’ve been looking for—The easy way to learn the most user-friendly database application program and out there! New or experienced, you will find many tips for a successful application.

Frances M. Peake, professional database designer and co-owner of Proctor and Peake, Inc., enthusiastically endorses Alpha Five Made Easy and answers a question about Alpha Five. “The plain-English explanations of each Alpha Five feature in this book show Susan's experience applying the program in a real-world business environment. Readers will find every chapter full of valuable information, regardless of their experience level. On behalf of Alpha Five users and enthusiasts everywhere, thank you Susan, for writing this book!” “With regards to Alpha Five, we are long time Q&A users and have been looking for a replacement that is easier than Access, dBase, etc., but has the power to write strong applications without a lot of coding. Is Alpha Five the product we are looking for? I would appreciate your opinion.” — RADeV, Jr. “You indicate that you are looking for “the power to write strong applications without a lot of coding.” In that case, yes, Alpha Five is a better choice than Access. I develop applications in both and I tell you that you can go so much farther in Alpha Five without having to do programming. In fact, you may not have to do any coding at all. Alpha Five offers a lot of commands and properties that simply do not exist in Access, where you would have to write your own procedures and functions to do the same thing. “Even when you need to automate a process, Alpha Five has a number of alternatives to writing code, such as “Action Scripting” that lets you perform the steps you want to automate and generates the code for you. There are, of course, some things that you might dream up that will require writing code in Alpha Five.” — FMP

The equipment you will need to have to use this book:  You will need to have the Alpha Five program installed on your computer. Information on purchasing the program may be found at www.alphasoftware.com.±  Alpha Five Version 10 contains full design features for both Desktop and Web Applications and SQL users. See www. AlphaSoftware.com for details.  A companion CD containing the working databases for the book is included with this manual.

± Liberty Manuals Company is a separate company from Alpha Software Inc. BOOKSHELF CATEGORY: ALPHA FIVE/DATABASE/SOFTWARE Liberty Manuals are published by Liberty Manuals Company, PO Box 453, Rumson, NJ 07760. 1-732-842-3000

LIBERTY MANUALS

USA $69.95 www.LibertyManuals.com