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.

Hanselman of the East

I am sure that some of our readers will see the title of this post and think that it is a bit strange.  There actually is a story behind it.   A lot of people do not understand the world that computer programmers live in.   For those of you who use Junxure, it is pretty easy to use the program and I hope we have done a pretty good job at hiding the complexity.  If you could peek into our world, you would see that Junxure is actually a blend of technologies, all working together to allow you to run your practice in the most efficient manner.

All of these technologies allow us to do some incredible things, but at the same time they are very complicated and forever evolving.  The challenge for me and our development staff is how do we keep up with the technology and it’s ever changing status.  The problem of keeping up is not unique to the programmers who develop Junxure,  it is a universal problem.  To help developers keep up with the changes, Microsoft has put together a team of people whose main purpose is to spread the news of what is new, what is coming, and how do you use these technologies.  Additionally,  just like we at Junxure listen to our users to find out what is working and what need to be added or changed, they listen and meet with many of their users to do the same thing.

They do this via a variety of ways, and one of them is something that is called “Code Camp”.  This weekend Microsoft, Infragistics and a host of other companies hosted a “Code Camp” and they were able to have it at a local community college.  Orlando Code Camp This Saturday, they put together 50 speakers, and 65 sessions designed to help developers use the technology.  There we sessions on Dot Net Nuke, Sharepoint, Data Services, Sql Programming, Programmer related management and development stuff, The Dotnet Framework, MVC and a whole bunch of other stuff.    You can click the link above if you are really interested in the details.

So if you have gotten this far in the post,  then you are probably asking yourself what the title “Hanselman of the East” has to do with all of this.  We it all goes back to a maintenance man at a theater in Fort Lauderdale.  You see, about 4 or 5 years ago, I went to another event that was hosted by Microsoft,  and this event was at a theater.  I arrived a a bit early and got a seat near the front.  While they were setting up it looked like there where having trouble with the audio visual system and in walks a maintenance man wearing a hard had and a old leather tool belt.

I have to admit, I thought it was a bit funny seeing all of these highly trained Microsoft employees standing around while this maintenance man was called in to make it all work.  He fiddled with some cables  and settings a low and behold, as if a by magic, the Microsoft logo appears on the huge theater screen.  Internally I had a bit of a chuckle thinking how the maintenance man saved the day.  Then they said that they were going to get started.  Much to my surprise, the same maintenance man goes to the front of the room and introduces himself as Russ Fustino, host of the, now world famous Russ’Tool Shed.  Russ gave a fantastic presentation on how to program on what was then the new Visual Studio and some of it’s new features.  Russ is one of the guys who helps us learn all of this new stuff.  His discussion and demonstrations of the tools that are available to developers is top notch.

So again you are probably asking again “What does this have to do with Hanselman of the East”.   Well there is another person who has devoted lot’s of his time to helping developers learn about technology, and his name is Scott Hanselman.  Scott is very well known but mostly hangs out in the West,  but he has a great reputation in this field.   Many people when they think of great leaders in the area of Microsoft development they are can’t help but immediately think of these two guys, along with a slew of many others.

To give you an idea of how many people Russ has helped over the years, I’ll share a story about a session this weekend.  Ryan Morgan was giving a presentation on Jquery, and a lady walked into the room and said she had some extra tickets to Russ’ TV show.   There were about 40-50 people in that room, with many standing in the back of the room because of  Ryan is an awesome presenter and everyone wanted to hear him.  Anyway, when she said she had a few tickets to the show, one poor guy asks “Who is Russ Fustino?”  The whole room went silent and almost in unison whole room cried out “WHO’S RUSS FUSTINO?”  like they were so surprised that someone in this field, would not know who Russ is.  The poor guy then had to listen as people began to say “EVERYONE KNOWS WHO RUSS IS.”  He as probably feeling pretty bad by then, but that shows the impact that Russ has made on the developer community.

Overall it was a great weekend for the Junxure developers.  We were able to fan out and cover a huge variety of topics and the knowledge that we gained will help us to make Junxure, Clientview and Junxure Mobile much better products.

So this is the end of the weekend and the Junxure developers have spent a couple of days reviewing our code, looking at things that we are working on and refining our road map.  We topped it all off with a whole day at Code Camp, and now we are going to be heading back to work, fully charged, loaded with new ideas,  and excited to get started using them.

Hopefully you now have a better appreciation of what we do behind the scenes to keep up to date on all of the current technology.

Creating A Custom Report (Part 2) – Designing the report

Creating custom reports in Junxure (Part 2) The Reports

Recap

In the previous post,  I covered the most basic parts of Access, and described how Access has 2 parts, the database and the programming environment.  Using the first part to hook up to the data, you can then build queries to extract the data that you want.  You can use the queries to limit what fields you see and how that data is “Joined” together to show you want you want.  You can also put filters on the queries so you further limit what is returned from the database.    Here is a link to Part 1

Creating the Report

When you create a query that displays the fields that you want and contains the appropriate filtering, you can look at it in “Datasheet view”, and see what data is returned.   It will look something like this.

This is great for making a simple list of data, but if you want a printable, formatted report, you will need to do a bit more work.  The good news is that you can leverage all of the work you did in creating the query, all you need to do is to create a report that pulls it’s data from your query.

I will show you a step by step creation of a report using the query that we made in the previous blog entry.  In the sample database that I have provided, I have called the query qryDemoActions.

If you need a copy of the custom database you can get it http://s3.amazonaws.com/Junxurebasics/CustomDemo.mdb

Finding the Report Wizard

You will need to open the database container.  There are a couple of ways to accomplish this.   Hit {F11} and it should open up, or you can click on the menu item Window | Database,  (If you do not see Database on the window menu, you will have to click Window | Unhide Window to make it visible).  Once you have the database container open, you will click on the reports tab, and then click New Report.  That will open the starting page of the report builder.

Here there are 6 items that you can select from

• Design View – This will just open a report in design view and you will have to place all content on the report.  Access does nothing for you.
• Report Wizard – This will walk you thru some choices and you will wind up with a basic report,  ready for additional editing.  (This is the one we are going to walk thru.)
• Auto Report: Columnar – This option will create a report with each field entered one on top of another, with the labels going down the left side of the page and the data on the right.
• Auto Report: Tabular – This option will create a report similar to a spreadsheet, but you will be able to edit it further.
• Chart Wizard – If you query had data that was suitable for charting, you could select his and create a chart.
• Label Wizard – This option will allow you to use the Access label wizard and create many different labels.

In our case you will select Report Wizard at the top, then select the query that we built in the previous blog entry.  qryDemoActions.  That tells Access that you want it to create the report automatically for you.  Once you click OK you will then get a screen where you can select the fields that you want in your report.

On this form, on the left side you will see all of the fields in the query.   You can double click on any of the fields to add them to the report.  Once added to the report, they will disappear from the left column and appear in the right column.  In the above screen shot, I have selected all of the fields, so Access will create controls for each field on the report.  I will discuss the controls a bit later in this entry, but you should know that Access will place a text box on the report and when you run the form, the data for that field will appear in the text box.  When you click Next from this screen, you will be prompted for any grouping that you would like on the report.  Grouping is useful when you want to group report records under a particular field.  For instance, I would like to group all of the actions in the qryDemoActions by who they have been assigned to.

By selecting Assigned to, my report will have a group header and the records in the query will be grouped by the person who they have been assigned to.  This will make it easy to see all of the record by each employee.  You can select additional groups if you like, but for this example we are going to stick to one grouping.  When you click next you will be prompted for the sorting.  Again you can select multiple sorting levels.

Here we have chosen to sort by Last name, First name and Date.   This will produce a report that is grouped by the employee the actions are assigned to and then sorted by clients in last name, first name order, then in date order.  You can change the order of any field so it is either ascending or descending order, so if you want the oldest actions at the top of each group, you would select Descending order.   When you click next you will be prompted for the alignment of the fields within the report.  You can try each setting to see how it will look, but other than cosmetic purposes, it does not matter what you select on this screen.  You can also choose between landscape or portrait, along with forcing all of the fields to appear on one page.  If you force all fields to appear on one page, you may have to do some adjusting to make it look right.

When you click next you will be prompted for a format for your report.  Access provides several different formats and you can choose any of them.  You will just have to play around with each one to see what style you prefer.

Finally, once you have selected your style, you are ready to create your report.  When you click finish, you will view your report in design view with the data from your query, and if you were to print it, that is exactly what it would look like.  Let’s click Finish and see what it looks like.

Modifying your Report

If you click on View Design view, you will be taken from the above print preview view into design view of the report.  In design view you will be able to edit the look and feel of the report.

In the above screen shot there are several things that I have pointed out.

• Pink Arrow at top left – This is how you can switch back and forth from design view to print preview.  Click the little arrow and select either design or print preview.
• Red Arrows -  This is the button that toggles the field chooser on and off.  You can drag any field from this list and it will be linked up to the query behind the report.  Notice where the black arrow is pointing to the lastname field.  That is a field that Access created for you, but if you were to drag it to the report again, you would get something that looked just like that.
• Green Arrow – This toggles the toolbox on and off.   There are a number of controls in the toolbox.  The top two icons, highlighted in orange in this screenshot  are the selector and the autowizard tool.  The rest are listed below going across then down.
  • Large Italic Aa This is a label control and it allows you to type any text in it, an that text will just show up on the report.  The top control on the report, that says “Actions By Assigned to Employee for 2000″ is a label control and I have set the text in it to what I wanted.  Changing the reports underlying data does not change the contents of a label control
  • ab|  – This is a textbox control and generally it will display the underlying data if you bind it to the field.  If you look at the properties box for the lastname textbox, you will see that the control source is set to lastname. This tells access that when you run this report, take the data from the lastname and display it in this box.
  • Small box with xyz on top.  This is what you call a group by box.  We are not using it here, but it can be used to hold buttons  or checkboxes along with grouping controls for cosmetic reasons.
  • Toggle button – This can be bound to a yes no field and it will either display a depressed button or a raised button depending on the selection.
  • Radio button – These are usually placed inside of a group by box, and only one can be selected.
  • Check Box – This is usually bound to a yes not field, like I did on the action required field in this report.  If the action had action required, you will see a checked box, otherwise you will see an empty box.
  • Combo Box – This is a drop down control that holds both the value for the field and a list of items that you can choose from.
  • List box – Similar to a drop down, but it is a list of items and the selected item will be highlighted.
  • Button – Not usually used on reports, but it is just a button that you can click.
  • Image control – This is a control that you can bind to an image.  If you wanted a logo on your report you could place an image control and then set the properties for the path to the image.
  • Picture box – Similar to an image control but with some other options.  The image control is the lighter control and will give better performance.
  • The picture with the xyz is a bound object frame and you can use it to display an object, like a word document inside of your report.  Typically you do not use this item and there are a lot of things that you have to setup to make sure it works properly, including having the proper program installed to display the object.
  • Page Break – This will force a page break wherever you place this control.
  • Tab Control – This is a control that give you selectable tabs.  It is useful in a report for showing only certain controls at a certain time.  (This is a more advanced control)
  • Subform / Report  – This is a powerful control, that allows you to place another report inside of an existing report.  For instance, for the assigned employee, you could have a separate report that shows all of the information for that employee.   It would actually run the report for each employee and only show the data for the employee that it is linked to.   For more information, I would google Access Subreports to learn more.
  • Diaganol Line  – This allows you to draw lines on your report.  Notice in our report we have several lines.  The best way to make a line totally flat is to select it and set the height to 0.
  • Square – This allows you to draw squares on your report.
  • Toolbox – This is a link to additional active x controls on your system.
• Black Arrows – This button toggles the property pages for the selected control.  The property pages are where you can set the value of properties on the control.  Properties can affect the behavior and appearance of a control.  Here are some examples.
  • Name – This is what you will refer to this control as, when you are in code or macros.
  • control Source – This tells Access what fields contains the data for this control
  • Decimal Places – Used when the control contains numeric data.  You can accomplish rounding to 2 decimal places by setting this to a 2
  • Can Shrink and Can Grow – Allows the control to either get taller or shorted depending on how much data is in the contro.  If you have a note field that can contain anywhere from no data to paragraphs, then setting this to Can Grow=True will allow it to display all of the data without having blank spaces in records with only a small amount of data.
  • Font Bold Size etc,  changes the size and attributes of the font.
  • Visible – If you can see the control in print or print preview mode.
  • Text Align – Left Center or right justified.
  • Width and height These set the size of the control
  • Others – there are many other properties but this covers the most used ones.
• Green box in lower left – This is a text box that was dropped on the report, and =Now() was added as the control source.  It will cause the report to print the current date and time when the report is printed.
• Blue text box at the lower right.  This is a special code called [Page] and [Pages].  Access is smart enough to know how many pages of data there will be when the report is printed and what page it is on.  Using these special codes you can display that information on  your report

Additional Formatting

Now you should have enough information to get started playing around with the format of your report.   You can try changing the size, alignment etc and see if you can make it look exactly like what you are wanting it to look like.

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.

Effective Task Managment and Tracking

More than a few times, either on the phone, forum or in person,

I have heard our users cry out “We want to assign a task to multiple people ! ”

This has always been a point of contention between different leadership styles.  I have always taken the line, that it is not a good practice to assign a task to multiple people, knowing that if more than one person is assigned, the chances of it being completed are diminished.  I just have to think about when my kids were younger and I asked them to mow the lawn.  Days would pass and each one would assume that the other was going to do it, and I frequently found myself, spending a Friday evening explaining to them, that if the yard was not mown before Saturday morning, then they would be spending the weekend inside instead of playing outside with their friends.  A poor decision on assigning the task, put me in the postion of having to drop the hammer on them.  In retrospect, it would have been much better to make it clear and easily understandable, that one of them specifically was to cut the grass.

Hildebrant International is a Global Leader in Professional Services consulting, and on their website  they have ten management principles.  Principle number seven is posted below.

Here is a the link to the source http://www.hildebrandt.com/Documents.aspx?Doc_ID=892

7. Do not assign an important task to any group that is co-led.
Co-leadership has multiple possible consequences and all but one of them is bad:

a) The work will be carried out competently,
b) Each co-leader will assume that the other will take responsibility for moving the matter forward and nothing will happen,
c) Both co-leaders will move forward independently creating:

i) A wasteful duplication of effort,
ii) Confusion among subordinates as to who is in charge of the matter,
iii)Maddening multiple subordination of subordinates and coworkers, or
iv) Contradictory instructions to coworkers and subordinates.

If you are serious about an assignment being carried out, assign it to one person, or to the leader of a group, such as a practice group leader, in his or her role as leader. Making assignments to a collectivity (e.g. ad hoc committee, partners meeting, practice group) defuses responsibility. Having delegated a task to an individual, neither disappear nor interfere, but maintain a consistent, light supervisory interest on the level of, “How are things going with such and such?”

Reading that just enforces what I learned years ago and I still subscribe to it today.

But are there exceptions?

As the main architect and developer of Junxure, I have learned a lot of things in the  14 years.  One of the things that I hold dear to my heart is the notion that our users are pretty smart people, and it is because of their ideas that Junxure has become a leader in the Financial services software space.  As I listened to many of them explain why they wanted to assign a task to multiple individuals, it became clear that what they really wanted is something a bit different.   About a month ago, one of our users articulated to me, a scenario that was like a beacon of light shining on the problem.

In their office, they have small teams of people who perform the same tasks for any or all of the advisors.  The advisors have gotten used to using Junxure’s Action Templates and Action Sequences, but they were having a problem making them work smoothly.  The problem was that certain tasks needed to be assigned to a team of people, because the advisor did not really care who did it,  they just needed to be sure that it got done.  They tried assigning all the tasks to the supervisor of the team, and then the supervisor would reassign the tasks out to the people on the team as they thought necessary.  While this was an improvement, it still had it’s flaws.

As is often the case, someone on the team would not come in one day, and the tasks that were assigned to that person were semi hidden from the view of the rest of the team.  While it is true, that with the report dashboard, the could have seen what was assigned and taken it, it required a change of course from their natural work flow.  In our discussions, I finally understood what they needed to make the system even more efficient.

Queues

Wikipedia defines a queue as  “,An area where a line of people wait. The verb queue means to form a line, and to wait for services. Queue is also the name of this line. “    This is the nature of this problem.  There is a line of work that needs to be done, and any of the team members can do it.  They just need and easy way to see the pending tasks for the queue and then they can grab one and do it.  Efficient and practical.

Rolling up the sleeves and getting to work.

After thinking this thru, I began to work on a solution that will make Junxure even more practical for bigger offices where for efficiencies sake, they employ queues to assign out tasks.  The first thing I did was I added a new task area, called “View Queues”  From this area, you can easily create a new queue and then, once it is created, you can assign tasks to the queue instead of a person.  Team members can easily view the queues, right from the main menu and work on the tasks as the come in from the different areas of the company.  Often times, with larger companies, the tasks come in from an office that is not even in the same city, so knowing who the actual person who is going to perform the task is not only not necessary, but also not practical.

The managers of the teams can easily monitor the queues and make sure that people are picking up the tasks in a speedy manner.  They also still have the full ability to assign a task to an individual just like before.  It really is the best of both worlds.  In fact, if you never setup a queue, then Junxure will work exactly how it did in the past, but once you create a queue, you can assign any type of action to the queue instead of a person.

My initial feedback on the queuing system has been great and I think that it will help solve a longstanding issue for many of our users. While I am not really that old, I am getting older and just a bit wiser, proving that you can teach an old dog new tricks.

If you would like to see more information about the new queuing system you can click this link.  http://s3.amazonaws.com/Junxurebasics/ActionQueues/default.htm

Let me know how you like the new queueing system.

New Feature (Multi Custom Fields for Accounts)

Tracking Account Information

Over the last year, I have had many requests from our users,  some work in the accounts area of the program.  Basically they were asking for 2 things.

More fields that we did not have

Better ability to report on the Accounts.

Due to the enormous efforts what went into rewriting Junxure 7, we did not have time to get these items into the main release, but we were listening and they will be in the next release.

Here is what is coming.

Unlimited Custom Fields

Accounts Rule Builder

Account Report Wizard

Enhanced Client User fields

I have just added a few new features to the Accounts details form.   These features are in the latest code, and they will be released as soon as testing gets underway.  Probably within 3-4 weeks.

 

Unlimited Custom Fields on Accounts

In order to use them, you will have to first set them up.

Go to List Data Maintenance click on the Program Setup Category,  and then select Multi Custom Fields.

There you will find a form where you can setup unlimited fields that will be associated with each account.

There are 4 attributes that make up each field

Data type – This determines the User interface control that you will use when you access this field.  The following types are available

Combo – this will present a drop down for the field.

Date – this will present a date time picker for the field.

Number – this will present a numeric editor for the field.

Text – this will present a text box for the field.

Currency – this will present a currency editor for the field

Y/N – This will present a check box for the field

Field name - This will be the name of the field on the Account form

Sort – This determines the order that the fields appear.  They will appear in two columns, down then across.

Key- This is a button that you can click to enter the contents of the drop down if you select Combo as the data type.  The button will only appear when you select Combo

Here is a screen shot where you setup the fields.  Another benifit of this enhancement it that it will allow us to expand the custom fields to the insurance at a later date.

Once the fields are setup, you can see them on any account from the clients form.

Account Rule Builder

This is a rule builder, that works for Accounts.  You can use any of the fields that are on the account form, along with any of the custom fields that you have setup.

You can create unlimited rules and check them on the fly, or from the Account Rule list.

Account Report Wizard

This is a report wizard, similar to the Report wizard and the Action Report Wizard, but it is for Account information.  You use an Account Rule to determine what accounts you want to see.  You can then enter a client rule to limit the list to a group of clients, and then you select what fields you want to see in your report.  Just like the other, you can send the results to a portrait or landscape report, or to an Excel spreadsheet.

Enhanced Client User Fields

I have taken the ability to select the data type for a custom field,  and have applied that to the existing user fields on the client form.  By default, they will all be combo fields, but you can change them to any of the above listed data types, so no conversion is necessary.

It is important to be sure that you do not change a field that has text in it to a Number.  I have put some checks into the system so you will not be able to do this.  When using this feature, if you do not see one of the data types in the drop down, it is because you have data already entered in that field, and it would conflict with the missing data type.

 

 Hopefully, these changes will go a long way to making Junxure a more capable program, assisting you to run a better practice.

Efficiency with Junxure

This is the first entry into a new blog that I have started,  hoping to help the users of Junxure, get more out of the system.  Hopefully you will find it useful and be moved to share a comment or two, to help get a discussion going.  At a mininum, I hope to achieve 2 things with this blog.

1.  To make you a better user of Junxure, finding out what the features are and how to use them.

2.  To engage users in a discussion of ways to improve your practice, and your insight on how to improve the program.

In this turbulent time of markets that are seemingly moving in a continuous downward spiral it is all the more important to have an efficient and easy to use CRM System.  Many financial planners, insurance agents CPA’s and others in the financial services industry have chosen to use Junxure to improve efficiency and customer service.  I want to give you a couple of examples of how others are using the system to impove service and hang on to client’s who are searching for competency and assuredness when it appears that none can be found.

Relating a Story

Here is a scenario that will illuminate one of the reasons why you need a quality CRM.  Imagine if you will, that you are an office that works with attorneys to take care of certain estate planning items.  In this case, you are working with a client to get a will created.  The client and the advisor have a meeting and discuss the things that need to be done, and one of the items was a will.  The advisor gets the information and sends it to the attorney.  The next day the attorney calls back but the advisor is not is, so they leave word with an associate telling them that it all looks good and they would be able to have the job completed by next Thursday.  Everything is good and life went on.  Later that week, the client calls back in to check on the status of the work.  When the client calls in, they get the receptionist, and they ask her if she knew the status of the work on the will.  Unfortunately, the advisor was out of the office, and the receptionist did not know the status of the work.  This begins a series of steps that highlight the reasons that any professional organization needs to have a system in place, and every employee in the company needs to utilize that system to its fullest.  Here is what happens.

1. The receptionist tells the client that she will check on it and get back with them.

2. She then calls the advisor who was not avilable so she leaves a message.

3. The advisor gets the message and calls the attorney to find out what the status is.

4. The attorney is out, so the advisor leaves a message.

5. The attorney calls the advisor back, and tells him “Yesterday I left a message with your associate but you must have not gotten it.  The work will be done next Thursday”

6. The advisor, armed with this new information called the receptionist back and tells her to call the client and let them know that it will be done next Thursday.

7. The receptionist calls the client, but the client is not avilabe, so she leaves a message telling them that the work will be ready next Thursday.

This scenario can be expanded upon but it is something that happens on a regular basis, causing people at every level of the company to waste precious time chasing information.

In this age of Google, and instant searches, you cannot afford to run a business like that.

If you were using a CRM system like Junxure, you would have had a much different scenario.  When the attorney called and told your associate the status of the work for that client, he would have went to the client’s record, and made an entry updating the status.  Then when the client called it would have went something like this.

1. The client calls and the receptionist opens the record, sees the note and tells the client that they have spoken with the Attorney and the work will be ready on next Thursday, “Is there anything else that I can help you with?”

DONE!

NOTICE A DIFFERENCE?

The first scenario, easily could have taken over a half hour to get back to the client. 

People at every level would have wasted time, accomplishing the same task. 

What would happen if the Advisor was out of touch, maybe on a plane, it could be hours or possibly days before the client got their answer?

What do you think the client would think about your firm’s competence when they cannot get an answer to something as simple as the status of some work?

Conclusion

When everyone in a company uses the system to document what they are doing, it will improve customer service at all levels.  It is important to deliver what you promise, and when you are prospecting for a new client, you are probably telling them that you are a high service, high touch, professional firm.  Without the proper systems in place and people using them as they are designed, you will not be able to deliver what you have promised.