Write a Junxure Custom Application in Visual Studio

Creating a custom  Junxure Application with VB.net

Problem.

Download the Code for this project

This office manages accounts for advisors who do not work directly for them.  The people who own the accounts are not clients of this firm, but rather are cleints of the external Adivsory firm.  They need to be able to see the following information:

How much each individual Investor has in each account
How much each external advisor has under management with the main firm.
How much revenue was generated via these accounts.

For our example we will use the following people.
Steve Findlay – An external Advisor that the firm works with.
Alan Anderson – A client of the External Firm

Solution

The solution to this issue was a 2 part item.
First we needed to figure out a way to use Junxure to track this information.  To do this we did the following.

Added a user to Junxure with the same lastname and first name as the external advisor.
Created an Employee Position to indicate that a contact record in Junxure has an External Advisor.  The Employee Postion is called “External Advisor”
Go the the clients of the External Firm and add an employee position to them and mark their External Advisor as “Steve Findlay”.

2

Writing the custom Application.

In this article it is not my aim to show you how to use visual studio to write a custom application, I am assuming that if you are reading this, then you have some level of experience in using visual studio, or another programming language.  I am just trying to show you the possibilities of what you can do using Visual studio to enhance Junxure.  I am however including the source code for this project, so you can explore the solution further.

What we now need to do, is to find all of the Contacts in Junxure, that have an External Advisor, and present a grid that shows their accounts and the revenue for each one.
We will use two views that already existing in Junxure, vqryAccountTotals and vqryRevenueFeesPaid, since these views totals up the details of the accounts and clients revenue.
Here is a graphical representation of what we are trying to do.

We can choose whatever fields we want from the above tables, so to
create the sql for this, we will use the following fields.  We
will exclude the Junxure Manually added accounts since they would not
have revenue

and we would not want them in the results.

SELECT   dbo.tblClientEmpJobPositions.ClientID,
dbo.tblEmployees.FName,
dbo.tblEmployees.LName,
dbo.tblClients.LastName,
dbo.tblClients.FirstName,
dbo.vqryAccountTotals.AccTotal,
dbo.ContactsAccountsXref.Account#,
dbo.vqryRevenueFeesPaid.Inception,
dbo.vqryRevenueFeesPaid.YTD,
dbo.vqryRevenueFeesPaid.Last12,
dbo.vqryRevenueFeesPaid.Last3
FROM         dbo.tblEmployees RIGHT OUTER JOIN
dbo.vqryRevenueFeesPaid RIGHT OUTER JOIN
dbo.tblClients ON dbo.vqryRevenueFeesPaid.ClientID = dbo.tblClients.ID RIGHT OUTER JOIN
dbo.tblClientEmpJobPositions LEFT OUTER JOIN
dbo.tblLookJobPositions ON dbo.tblClientEmpJobPositions.JobPositionID = dbo.tblLookJobPositions.ID ON
dbo.tblClients.ID = dbo.tblClientEmpJobPositions.ClientID ON dbo.tblEmployees.EmpID = dbo.tblClientEmpJobPositions.EmpID LEFT OUTER JOIN
dbo.vqryAccountTotals RIGHT OUTER JOIN
dbo.ContactsAccountsXref ON dbo.vqryAccountTotals.CLTNO = dbo.ContactsAccountsXref.Account# ON dbo.tblClients.ID = dbo.ContactsAccountsXref.ContactID
WHERE     (dbo.tblLookJobPositions.JobPosition = N’External Advisor’) AND (NOT (dbo.ContactsAccountsXref.Account# LIKE N’Junxure%’))

This will return all of the records in Junxure that have an Employee

position of External Advisor, along with the totals in the accounts and
the revenue paid from those clients.

Now we need to make sure that when we are looking at the Junxure
Contact Record for the External Advisor of Steve Findlay, we are only
seeing the contacts that he is the External Advisor for.

Since our custom program will be launching in the context of the
current client in Junxure, we will not be able to open the custom query
showing the records for the Employee Steve Findlay.

In order to do that, we will need to convert his Junxure Client ID that
is show on the top of the client form, into the Junxure Employee ID for
the records that you added for Steve Findlay.

When we setup the Steve Findlay record we were careful to enter the
lastname and firstname the same in the Employee form as it was in the
Client form.  So we will have to do a lookup to find the Employee
id, like is shown in this diagram.

We will pass the ClientID into the new program and lookup in the
employees table for the same lastname, firstname, returning the
employee ID.  We will then filter the above sql statement to only
show the people where Steve Findlay is and External Advisor.

Junxure has the ability to launch custom programs and pass in context
sensitive information as a command line argument.  We will setup a
custom launch command to launch our CustomAdvisorReport.exe and pass in
the argument of <ID> for the current client.

This will launch our CustomAdvisorReport.exe program and it will also
pass in the ID as an argument.

It will be up to our program to read the argument and do everything
else.

So on the form load we read the arguments, and since there should only
be one, we will assign that argument to the ClientID variable.

We will use the Environment.GetCommandLineArgs to get this
value.   The GetCommandLineArgs always includes the program
name as the first element, so we will check if there are 2 arguments
and if so, we will stuff the second argument into the client ID

Dim arr() As String
arr = Environment.GetCommandLineArgs
If arr.Length <> 2 Then
MsgBox(“You need to supply a ClientID to report on”)
End If
clientID = arr(1)

Once we have the client ID, we will need to get the firstname and
lastname so we can do a lookup in the clients table.

We will use a bit of SQL to get the Employee ID.

SELECT     dbo.tblEmployees.EmpID
FROM
dbo.tblEmployees INNER JOIN
dbo.tblClients ON dbo.tblEmployees.FName = dbo.tblClients.FirstName AND dbo.tblEmployees.LName = dbo.tblClients.LastName
WHERE     (dbo.tblClients.ID = 2620)

This will return the EmpID for the user in Junxure that we
entered to match this employee.  We will now apply that Employee
crtieria to only show the accounts that match that Employee.

You can download the sample code for this application here.
Download the Code for this project

There are a few things that you will need to be aware of when using
this sample code.

We have a class that is called SqlServer.vb and it uses the connection
string that is defined in the external.config.  If you are going
to use this application as a framework for additional custom
programming, you will need to copy your external.config from
c:\junxure\codedn into the debug folder so it will know how to connect
to your database.  Or you can just edit the one that comes with
this zip file.  The line you want to change is the line that looks
like this.

<add
name=”Junxure” connectionString=”Data Source=i7\SQLExpress;Initial Catalog=Junxure;Integrated Security=True” providerName=”System.Data.SqlClient” />

You will have to change the Highligted portion to reflect your server
and instance name as it is on your network.

Once we get the data to display we are just loading it into a grid.

Additional Implementation.

We added another button to display all of the externally managed
clients, instead of the one that you were on in Junxure.  It is
basically the same code, but we skip the filtering by client ID.

Once we get the data to display we are just loading it into a grid.

We added some code to open the client when you double click the row.

We added a button to open the Advisor when you click that button.

Advertisement