May 2011


In This Newsletter

AVAproject Fusion Series:
No. 3, Data Filters - Focusing On The Data You Need

AVAware Article Featured in DHI Magazine

AVAware Technology Feature:
Database Technology: What is this "SQL" Thing Anyway?

AVAproject Tip: Quickly Create Similar Frame Elevations

Catalog Updates

 U.S. Price Books:

  • Best
  • Ceco
  • Detex
  • Hager
  • Hiawatha
  • Markar
  • Mohawk
  • National Guard
  • Pioneer
  • Roton
  • Sargent
  • Stanley E.D.
  • Von Duprin

 Canadian Price Books:

  • Baillargeon
  • Best
  • Daybar
  • Markar
  • Sargent
  • Schlage E.S.
  • Stanley D.C.
  • Stanley E.D.
  • Von Duprin
  • Yale

AVAproject Fusion Series: No. 3, Data Filters - Focusing On The Data You Need

This is the third in our special series of articles profiling our newest product offering: AVAproject Fusion.

Fusion is about mastering your data. Information from AVAproject or virtually any other Windows-compatible data source can be brought together, manipulated, reported on and reformatted for use in other applications. See your data any way you can imagine it.

In previous months we demonstrated how project files, created in AVAproject, can be brought in to Fusion. We also examined the data can now be viewed in terms of all its complex inter-relationships through the new and enhanced Material List found in AVAproject Fusion. The next major component in this powerful toolbox is the Data Filter.

Data Filters allow you to select portions of the entire data set (the project’s Material List, for example) to be extracted and handled separately from the rest of it. In traditional database parlance, selecting portions of a

database is done by means of executing a “query” against it. This involves constructing a command string in accordance with a very complex and structured language. The language that’s used almost exclusively today is SQL, and quite appropriately that stands for “Structured Query Language”. Rather than forcing users to learn SQL, Fusion offers an innovative and far less difficult new way to accomplish this task.

Fusion employs a revolutionary “near-English” query builder that allows users with absolutely no knowledge of SQL nomenclature to build a multiple argument selection

command. The Filter Builder (depicted below) walks you through the entire process, step by step.

Simply select any column from the AVAproject library along with an operator and the value to compare it against. Comparisons can even be made against other columns. Since the data is being drawn from the entire Combined Information Pool, selections can be made based on data from any worksheet contained in the project. For example, rows from the Material List can be selected based on the contents of data in the Openings Schedule.

The AVAproject Fusion Filter Builder

In Database terminology this is known as a “relational query” – a very complex operation that Fusion makes simple work of for non-programmers. While the selection query is built by essentially pointing and selecting, Fusion does all the difficult programming work in the background. If you happen to be interested in seeing exactly what’s going on behind the scenes, you can click on the “SQL” tab in the Filter Builder to reveal the complex SQL language command that is being composed.

The Filter Builder SQL Tab

The Fusion Navigation Bar including a Filter

When the filters are created, they can be saved as part the project file itself, or stored in a library for use on other projects as well. The filtered data selected are offered as subsets of the Fusion Combined Information Pool on the Navigation Bar.

Data filters are the basis for all functions performed in respect of a database. All reports, forms and tables must first draw their data from project files in its entirety. By using these filters, reports can be generated for all or any specific components of a project. No matter how narrow or unusual the query, it can now be created on demand. Before Fusion, users were at the mercy of the software developers or their I.T. people to create custom reports. This powerful tool now places that ability in the hands of all users.


Many of the concepts discussed in this article are expanded upon in the AVAware publication: "AVAproject Fusion: Theory of Operation". In it, data structures and the interaction between project components are illustrated in much greater detail. This comprehensive document is available in the Downloads section of the AVAware website.

AVAware Article Featured in DHI Magazine

The architectural openings industry's premiere publication, Doors & Hardware Magazine, featured an AVAware article entitled "XML - In Plain English!" in the May 2011 edition of the magazine!

The article explains XML in layman's terms and attempts to clear the confusion about what it is and how it is used in terms of software integration.

Stay tuned for upcoming AVAware features in the DHI Magazine.

AVAware's Monthly Technology Feature

In today’s world, business people have had a seemingly endless torrent of technological lingo to have to learn and cope with. The language of modern business is feathered with three-letter acronyms and strange sounding buzzwords that become standard fare in the boardroom. It’s a cold hard reality that business people that want to thrive in these tech-saturated times have much more to learn than their predecessors ever did. The other cold reality is that understanding these technologies is essential to not only making important decisions but also to avoiding costly mistakes.

In previous issues of AVAwire, we’ve discussed a number of important technological issues from systems integration to application networking. We will continue to dedicate space in this and future issues to focusing on and explaining different aspects of technology as it applies to the business world today. Often time the reality of a given technology product is far different from the way popular culture perceives it to be or how vendors portray it.

Database Technology: What is this "SQL" Thing Anyway?

Never have three little letters have had such a profound impact on the business world since “COD”. As software vendors, we’ve seen countless business owners invest in “SQL databases” and “SQL servers” without ever understanding what these three magic letters really refer to.

If you were to ask a group of people what “SQL” is, you’d get a myriad of answers that are as diverse as they are vague. Some will argue it’s some form of hardware while others in the know will tell you it’s a type of database. Although the latter is closer to the truth, the former isn’t entirely wrong. This is as diverse a term as you’ll ever find.

SQL is actually a language. It stands for: “Structured Query Language”, and was designed expressly for the purpose of issuing commands to a database engine. Like any language, SQL comes in many different dialects. Different database programs use slightly different versions of the SQL language to facilitate their own distinct features. Any database built with such a program is generally referred to as a “SQL database”.


In addition to its interface language however, that term also carries with it a number of assumptions with respect to the structure and capabilities of the database itself. At its most basic level, data in such programs is arranged into “tables”. A table is basically a grid or spreadsheet in which each item of data is referred to as a “row” and each individual “field” is represented by a “column”. Rows are also referred to as “records” and groups of them are called “record sets”. When you gather together a group of tables, what you have is a “database”.



Relations: Not Just for Family anymore


Another older and commonly used term for such databases is “relational”. What this simply means is that records from a given table have “relationships” to the records in others. For example, every business generally has a customer database. In it, there is usually a master table that contains a list of all the customers along with their basic information (phone numbers, addresses, etc.). Along with this is usually a secondary table that contains a list of individual contact names. In order to connect the

people with the business names, a common code or “key” in database parlance is used to create a “relationship” between the two. That same “key” will occur in another table of invoices perhaps and one of checks received. That fact that the database is built upon the practice of inter-related tables makes it “relational”. This label is earned by the method used to organize the data, not the underlying technology.



Server: One Word with Several Meanings


Perhaps the most misunderstood word in the data basers’ dictionary is “server”. Most people understand a “server” to be computer that sits in the back room and “serves” files to users on their computer network. Although this is certainly one definition of “server”, when talking about databases it means something entirely different.


Many software applications, including SQL database programs, are referred to as “client/server” applications. Despite what most people believe, that does not refer to the hardware that it runs on. Although a “client/server” application certainly can run on a network fileserver, it does not necessarily need to. The term “client/server” refers to the architecture of the program itself. All it means is that the program is divided into two major pieces: the “client” application and the “server” application. As the names imply, the client makes requests of the server and server responds. With some systems, both of these can run on the same physical piece of equipment. The beauty of the architecture is that applications designed this way can often run both ways. Users can run smaller database “servers” on the same

machine as their client application, or position their data on a separate server computer when the need arises.


The most common misapplication of this technology occurs when software developers make inappropriate scaling decisions. If a developer makes a poor choice of database system, single-users can be forced to spend money on separate SQL servers when there is no actual need for it. Selecting the proper data storage software solution is every bit as important as the choice of hardware.



The SQL Language: Getting At The Data


After all is said and done, SQL is all about accessing data. In order to extract the desired information from a database, a command written in the language of SQL is sent to the SQL server. This command is referred to as a “query”, and upon receiving it the server performs a process known as “executing a query”. The command to retrieve a set of data records from the database is referred to as a “select query” or a “select string”. This query is simply a SQL sentence that explains to the database engine exactly what data is desired. Although most database systems allow users to enter SQL commands directly if they wish, most often they are created and sent to the server by the application that is using the database.


In practice, it is rare that any business person will actually ever need to learn the SQL language or how to create SQL queries. It is however useful to understand what is going on under the virtual hood.

AVAproject Tip: Quickly Create Similar Frame Elevations

Under normal circumstances, frame elevations within any given project are similar in their appearance and design. This is because once an architect has made a creative style choice for a building, they tend to maintain consistency throughout. In fact, one will often observe similarity between different projects designed by the same architect or architectural firm.

As such, the ability to copy elevations from past projects or to maintain AVAcad libraries can be a real time saver. Even beyond this, frame elevations within a single project can be copied from one opening to another. Elevations can be copied in the exact same manner as any other cell of data on the Openings Schedule. Simply highlight the desired elevation and select “Copy”. To complete the process, highlight the destination cell and select “Paste”. The following is an example of a project in which the same elevation has been copied twice, to the rows below.

One of the most convenient features of AVAproject is the embedded AVAcad facility that it contains. An elevation can be edited by simply highlighting it and clicking on the “AVAcad” icon. An AVAcad editing pane is brought up with the selected elevation loaded and ready.

Once the elevation has been modified to the user’s satisfaction, a single click will save it and instantly update the appropriate cell of the Openings Schedule. Nothing could be more convenient than perhaps having someone do the work for you. Users of AVAproject will know that when an elevation is edited as described, all occurrences of the elevations are automatically updated. So, what does one do if you only want to modify one opening? What if one opening calls for an elevation that is only similar to the others? Does this mean having to draw a different elevation from scratch? Absolutely not! Obviously, since all the occurrences of the elevation are updated when one is edited, one can assume that they are somehow “linked” internally. That assumption would be absolutely correct. The elevations are connected by virtue of the fact that they have the same name. If one wanted to edit only once occurrence of a given elevation, they would simply have to rename it – for that row only. To do so, highlight the desired elevation and select “Rename Elevation” from the context menu.

When the dialog box appears to allow a new name to be entered, you will notice that AVAproject counts how many occurrence of the elevation appear in the entire project. After the new name is entered and if there is more than one occurrence, AVAproject asks if it should apply the new name to all the frames that share the old name or only the one selected.

If the “Selected Row Only” is chosen, then that row’s elevation will be the only one renamed. In that case, AVAproject will create an internal copy of the elevation and assign it the new name. At that point, that elevation is now separate and distinct from the others. Any changes made to this elevation in AVAcad will apply to only this specific row and the others will remain as they are. By using the copy and rename tools in this way, you can create multiple elevations that are similar in style without having to start from scratch each time.

We welcome any questions, comments or suggestions about any topic mentioned in this edition of AVAwire. Please visit our website for more information, or contact us directly at (416) 239-9099.