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.
