Creating a custom report (Part 3) Where’s the data.

As you probably already know, Junxure keeps all of the data inside of a Microsoft SQL server database. This database contains a number of tables, and if you know where to look and how to ask for the data, you can pull anything and everything out of the database for your own custom reports. I will try to show you the tables and the relationships for the main parts of the program. Here is what I will cover.  While I am not going to cover all of the tables below, you will see that the clients tables are prefaced with tblclients so you should be able to figure them out pretty easily

Clients – tblClients
Phones -tblClientPhones
Addresses -tblClientAddresses
Emails – tblClientEmailAdds
Classifications -tblClientClassifications
Keywords – tblClientkeywords

Actions -tblClientActions
Emails – tblClientActionsEmails
Attachments – tblClientActionsEmailAttachments
Documents – tblClientActionsDocuments

Accounts – tblClientDBCamIDs
Owners -ContactAccountXref
Assets – FAS-Assets

Hopefully this will help you find where the data is for your custom reports.

Clients

The main data for the clients is stored in tblClients.  In that table you will find all of the fields that pertain to any contact in your database.  Since Junxure is a product that has been evolving since since 1995, there are a few things that you should not about this table.  There are some fields that we no longer use, those fields were left in the table, because many of our clients have written custom reports that use that table, and if we removed the fields, many of their reports would have been broken.  We decided to leave all of the phone, email and address fields in the table, even though we no longer use them.    All of that data was moved to the appropriate tables as show in the photo below.

If you look in the center of the picture, you will see the clients table.  The Primary key and main identifier for each client is the ID fields.  Each client can have unlimited records in each of the tables that the arrows are pointing to.  The 3 tables highlighted in blue are the tables that hold the phone, email and addresses on the contact info tab in Junxure.  The tables highlighted in Yellow, are the tables that hold the data in the bottom part of the profile tab.

Here is a diagram that shows the relationships between employees and the clients.

Actions

The actions are contained in a main table, and the emails and documents are located in additional tables.  Here is a diagram that shows the relationships for the actions.

tblClientActions -In the diagram below, you can see the white box in the center is the main action table.   It contains the main part of the actions.

tblClientActionKeywords -The pink table is the action keywords table, and you can have many records in this table for each action.

tblClients -The purple table is the clients table, and it is related to the actions via the client ID field.

tblAlerts -The darker yellow table is the alerts, and if an action has a task assigned to it, then an alert is placed into this table.  Once alerts are completed, they are deleted from this table

tblClientActionsFYi -The light yellow table at the top left is where we store the FYI’s for this actions.

tblClientActionsEmails and tblClientActionsEmailAttachments -The Green table are the emails and the email attachments.  Each action can have multiple emails, and each email can have multiple attachments.

tblClientActionsDocuments -Finally the blue table is the table where we store the documents.  We do not actually store the document in the database, but we store the path to the document in the filename fields.

Also on actions you may be interested in seeing what employees are related to the action.  Here is the diagram for that.

Accounts and Assets

Accounts and Assets are a bit harder to comprehend and because of the way Junxure has evolved, the naming on these tables is a bit strange.  One thing to remember is that Junxure, in it’s evolution, now allows multiple clients to own an account, so the accounts table is not really associated with the clients at all, that is where we use the cross reference table.  To help you understand how it is all related, it would help if you think of these 4 entities, and then look at the names of the tables that hold these entities.

Clients  – tblClients

Accounts – tblClientDBCamIds

Owners – ContactsAccountsXREF

Assets – FAS-ASSETS

Looking at the picture below you can see that each assets belongs to an account.  Each account has owners, and each owner is a record on the clients table.

Asset Fields

The field names on the asset form do not match the field names in the tables.  Here is a screen shot that shows what the actual field names are for the asset form.

This should answer many questions you may have about the location of the data and the how the data is related.  In my next post, I will try to show you how you can “Hook” the custom database to the Junxure database using the Junxure Public Object.

Creating a custom report – (Part 1) Tables and Queries

Because of our open architecture, you can easily create custom reports in Junxure.

Every piece of data that is used by Junxure is accessible to you to create custom reports with.  Knowing where this data is and how to use it, is the key to having any kind of report that you can imagine.  You will need a few things before you can get started.

• Some way to access the data -  I will show you how to use MS Access to do that.
• An understanding of data types in databases.  This is important because when you begin to want to limit the data, you need to know what kind of data type you will be filtering on.
• A bit of knowledge on how to access and aggregate that data.  This is basic database knowledge, and while there are many tutorials, book and courses available, I will try to give you the basics.
• A Reporting package that will let you build the reports that you want to build.  Again I will show you how to use MS Access, but you could use Crystal Reports or Sql Server Reporting Services if you like.
• An understanding of data relationships.  What is means when we say “One to One”,  “One to Many” and “Many to Many”.  How to use those relationships to get the appropriate data back.

Let’s get Started.

Since you are going to be using Microsoft Access to be the vehicle where you access your data and create your reports, you must first learn a bit of how to use the Program.   As mentioned before, there are whole books on this subject and entire courses that are taught at colleges and adult education classes.  I would strongly recommend that if you want to become proficient in learning how to do anything you can think of with custom reporting, then you should do what you can to get a thorough understanding of how Access works.  I have worked with many users who already had an understanding of MS Access and were pretty proficient with it, and these users were able to get started producing reports almost immediately.  I will do what I can, but you may want to deleve further into Access with a book or an online tutorial.

Here are a few links to help you find more information.

http://www.officetutorials.com/accesstutorials.htm

http://www.amazon.com/s/ref=nb_ss_gw?url=search-alias%3Daps&field-keywords=msaccess&x=16&y=19

Access is really two programs in one.

Most people do not realize this, but Access is really two programs in one.

It is a Database

Every access file can contain its own database tables.  There can be a virtually unlimited number of tables.  Although it can contain its own data, the file does not have to actually contain the data, because it also has the ability to link out to other data sources.  When it does this, the tables that it linked out to are called “Linked” tables.  For our programming examples, we are going to use Linked table, and we will be Linking out to the data that is stored in the Junxure tables on the SQL Server database.  If you do not want to use your live data, I will also provide a sample MDB file that contains a set of sample data that you can use instead of your live data.

It is a programming Environment

With the exception of the tables, everything else in the Access file is part of the programming environment.  When you think of a file on a computer system, you usually think of a single entity, like a word document, an excel spreadsheet, or a text file.  With Access, the files are a bit different.  They contain many items with them and those items are viewed by opening the file with the Access program.  The types of items are listed below

• Tables
• Queries
• Forms
• Reports
• Macros
• Modules

Tables

These are the items that contain the actual data.  A table is made up of Records and fields.  Most people are familiar with spreadsheets.  When you look at a spreadsheet like the one below you see rows and columns.  In this example, we have these columns

Name

Address

Phone

and we have 3 entries for each column

Ken

Bill

Tom

If you were to translate what you know about spreadsheets into database tables, you would see the following

Columns become Fields, and in a database you need to specify what type of data is stored in these fields.  In a spreadsheet, you can just type whatever you like into a cell, but in a database, you need to determine ahead of time, what kind of data you want to store in that field.  There are many types of data that you can store,  but I will discuss the basics.

• Text- Any type of characters that you can type with your keyboard
• Numbers – Any type of numeric input, but they are even split further
• Integers – Numbers that do not allow decimal points
• Doubles – Numbers that do allow decimal points
• Currency- Numbers that allow 4 decimal points
• Dates

(There are actually more types but for this discussion, you need to understand that some numbers do not allow decimal places)

Rows become Records with each record contains one copy of each field.   The drop down list shows you want kind of data each field can hold.   Here is a screen shot of a table that will hold the same data as the spreadsheet above.  This is the design view of that table, the bottom picture is the data sheet view, where you can see the data.

In the picture below, you will see an additional column.  ID  This column was added by Access and it is a type of Autonumber.  That means each time a record is added to this table, Access will automatically insert the next available number into that field.  Once it is inserted, it can always be used to identify that record.  This is very useful when trying to find a record.  Since it is a number, it can be indexed (Pre sorted) internally to the database and Access will be able to find it very quickly.  If you have 2 records with the same data in every field, the ID field will allow access to be sure to find the proper record.  It is called a primary key and they are very useful.  In Junxure, the clients table has a field called ID and it uniquely identifies each individual contact record.  Once a number is used, it will never be reused, even if you delete the record.

Tables are “NEVER” sorted by any predictable method.  The data can be in the order that you enter it, or any random order.  This is important for you to understand.  You can sort the data however you want using queries.

Too Many Tables

If you look at all of the tables in Junxure you will see that there are over 300 tables and it appears that the data is scattered all over the place.  Your observations are correct. It is scattered all over, but there is a method to the madness. If you are familiar with spreadsheets, you are probably used to viewing data in rows and columns. While this is a valuable way to store data it is very limiting.  You can do pivots and sorts but the amount of reports is fairly limited. That is why a program like Act or Goldmine cannot compare with Junxure when it comes to database flexibility. In a typical spreadsheet where you track accounts you may seem something like this.

Client Address Phone Account1 Account2 Account 3

Ken Golding 123 lucky lane 444-4444 YG66855 FB-55544 KF7706

Bill Smith 234 Bull Market Ln. 4454455 GF06855 000-9985 5467765

This creates a real problem with trying to add the 4^th, 5^th and 6^th account. What if you get a client that has

100 accounts? In a relational database we would split that sheet into 2 tables. Clients and Accounts so you would have something like this

Clients table Accounts Table

ID Name ClientID AccountNumber

1 Ken Golding 1 YG665855

1 FB-55544

1 KF7706

2 Bill Smith 2 GF06855

2 000-9985

2 5467765

Each piece of data goes in its own table and it is related to additional tables.

If you look at table alone, it would be pretty hard to use that data unless you had an exceptional memory

Some things to remember about tables:

• They can have certain fields that populate themselves, for instance the ClientIDs in the above client table
• They can have constraints on the data that is entered. You cannot enter a date into a money field.
• The data in a table is in no particular order, usually it is in the order it was entered but not necessarily.

So how do you view the data in a meaningful way? That is where Queries come into play.

QUERIES

At this point, I am going to introduce a sample database for you to work with.  It has the same tables as Junxure, but you don’t have to worry about messing up any live data.

Download it at this link http://s3.amazonaws.com/Junxurebasics/CustomDemo.mdb

I have included the tables in the database so you can feel free to delete, edit add as you like. All of the data in these tables is local and not “live”

The tables we will be working with are

tblClients The main Client Table

tblClientclassifications A table that has unlimited classifications for each client

tblclientKeywords A table that has unlimited keywords for each client

tblclientActions This is the table that stores actions in Junxure

tblClientDBCamIDs This is the table where we store the account Numbers

ContactsAccountXref This is a table that links accounts to client.

FAS-Assets This is where we store the Asset position information

There are more tables but for the purpose of this demonstration these are the one we are going to use.

If you open any of these tables you will see a lot of fields that you don’t necessarily want to see while you are working with the custom database.

When working with queries you should remember that you are just gathering the data that you are going to include in your report.

Here I will show you how to limit what you see to a subset of the entire table.

Take a look at qryClients. This is a query that I have created in the customdemo.mdb file.

These are the fields that we are going to have available. If we need more fields, we can just drag them to the grid. Notice that I have added a field called Addline and Cityline.

Here I have joined two fields together so that later you will not have to do it in your reports.

This is called an Alias. The syntax for this is Addline:[HomeStreet] & “ “ & [HomeStreet2]

Also notice that we selected Ascending under the last name on the sort row. That will return the records sorted by lastname.

If you switch this from the design view to the data sheet view, this is what you will see.

Now lets take a look at a query that uses this query to show all of the classifications for each client.

In the above image, we have joined the previous query with the classification table. The line with the arrow pointing out tells us to show us every record in the qryclients and only the classifications if they exist. If a client has multiple classifications then they will appear multiple times.

So you can see by using queries you can begin to make sense of this seemingly disconnected data.

Lets make another query that shows actions for clients. In this query I am going to show all actions with the clients name

Here I have joined the two tables to show all of the actions and the client for each action.

I have added the fields that I want to see.

If I switch to data sheet view this is what I see

Now we are beginning to get something that we can use, but notice that the EmpID and ActionAssignedtoID are showing numbers and not the Employees names. If we what the names we will need to add some more joins out the the employees table. Since we have 2 fields that we are looking for we will add the employees table twice. When we do this Access will append a _1 to the second table.  Then we will use the alias to display a field that shows the lastname of the Employee. For the fields with a zero on a blank, no name will show up. The syntax for the new column is Emp:tblEmployees.Lame

Notice that we now have the name of the employee along with the data. Another thing that you can do with queries it so filter the data by adding criteria. I will filter for only actions in the year 2000 and with a type of planning or service.

Here is the example for that

Notice that I have the critera on 2 lines. That is because I want to “OR” the filter.

I am saying between ((1/1/200 and 12/31/200) and Letter) OR ((1/1/200 and 12/31/200) and Service)

This changes the results to a smaller subset of the existing data.

<!–[if !mso]> <! st1\:*{behavior:url(#ieooui) } –>

By adjusting the criteria I could further filter this to only actions assigned to Mary Doe. In fact I could create as many different scenarios as I could imagine. This is the beauty of using queries to query a relational database.

You may be interested in knowing that the graphical interface is only a representation of the action

“Structured Query Language” or Sql that is behind this query. Here is the actual SQL text.

SELECT

LastName,

FirstName,

Date,

Type,

LName AS Emp,

ActionReq,

DateReq,

Note,

tblEmployees_1.LName AS AssignedTo

FROM

((tblClientActions LEFT JOIN qryClients ON tblClientActions.ClientID = qryClients.ID) LEFT JOIN tblEmployees ON tblClientActions.EmpID = tblEmployees.EmpID) LEFT JOIN tblEmployees AS tblEmployees_1 ON tblClientActions.ActionAssignedTo = tblEmployees_1.EmpID

WHERE

(((tblClientActions.Date) Between #1/1/2000# And #12/31/2000#) AND ((tblClientActions.Type)=”Letter”))

OR (((tblClientActions.Date) Between #1/1/2000# And #12/31/2000#) AND ((tblClientActions.Type)=”Service”))

ORDER BY qryClients.LastName, qryClients.FirstName;

The importance of knowing about the SQL text is that you can copy this sql and paste it into another database and access will draw the query assuming that the tables are available. It is also useful if you are using the Forum to ask a question about a query. You could paste the sql from your query and I could load it in my database to see what it is doing.

The SQL keywords are in this query are

Select

From

Where

Order by

In my next post, I will discuss how to take the data from the queries that you build and show you how to make them into a report.