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.

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.