RangerMSP Business Automation for successful ITs

 
January 2nd, 2015, 03:21 PM
Mike C
 
Posts: 41
I could use some help in implementing a simple search page to search through the tickets and display the ticket and any associated history notes.

I have an API connection setup and am able to return data, however I am stuck on adding the history notes to the search as well. I am a novice with VB.net but have been tasked with this.

Again thanks in advance for any help.

My search is
accountSearch.AddCriteria(CommitCRM.Ticket.Fields. Description, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")

I am assuming that I would want to create a custom sql script to be able to search in the account, ticket, and historynote tables at the same time and return, however I am not seeing a way to do this in the API documentation.
 
January 5th, 2015, 06:10 AM
Support Team
 
Posts: 7,510
Thanks for asking. To achieve this you would need to separately query the different areas (e.g. Tickets, Accounts, etc.) and then merge the results and display them as needed.
 
January 5th, 2015, 08:49 AM
Mike C
 
Posts: 41
I assume you are talking about merging the list collections? I will have to look into that.

Thanks
 
January 5th, 2015, 08:57 AM
Support Team
 
Posts: 7,510
Yes, exactly.
 
January 5th, 2015, 09:35 AM
AN-Tech
 
Posts: 478
We really need a way to search through all fields in Commit directly. It's too complicated to find what we are looking for.
 
January 5th, 2015, 10:19 AM
Mike C
 
Posts: 41
How would I go about combining via ticket number? Again I would like to show any entry with search value. If it is a history note I still want to supply the ticket number. Sorry if this is basic I am very rusty in my programming.
 
January 5th, 2015, 10:25 AM
Support Team
 
Posts: 7,510
When querying History you can receive the RECID value of the linked Ticket. You then use this internal unique key for the ticket to query the Tickets table and get the value of the Ticket Number, or any other field that you may want to display with the results.
 
January 5th, 2015, 10:36 AM
Mike C
 
Posts: 41
Something like this?

Dim accountSearch As New CommitCRM.ObjectQuery(Of CommitCRM.Ticket)
Dim HistorySearch As New CommitCRM.ObjectQuery(Of CommitCRM.HistoryNote)
Dim accounts As New List(Of CommitCRM.Ticket)
Dim history As New List(Of CommitCRM.HistoryNote)
accountSearch.AddCriteria(CommitCRM.Ticket.Fields. Description, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")

For Each account In accountSearch.FetchObjects()
accounts.Add(account)
HistorySearch.AddCriteria(CommitCRM.HistoryNote.Fi elds.Description, CommitCRM.OperatorEnum.opEqual, account.Fields.TicketREC_ID)
Next
 
January 5th, 2015, 11:31 AM
Support Team
 
Posts: 7,510
If I get this correctly then the above seems like it queries a list of tickets for a text and then for each ticket query its history by searching the ticket recid in the history description.
I don't think that this is the desired behavior.
Maybe you can post back, in pseudo code, what exactly you're trying to query for and we'll see if we can provide some additional insights.
 
January 5th, 2015, 11:50 AM
Mike C
 
Posts: 41
Search: "Monkey"

The desired result would be a dataset with any ticket or history note with the word "monkey" in it. Each item would have the primary ticket number whether it is a ticket or a history note.

IE: Ticket with/without history notes
<TicketNumber> - <Status_Text>
TicketDescription:<ticket.Description>
- If any history notes with "monkey" for this ticket display as
HistoryNote:<historynote.Description>

IE: - No ticket with search word but history notes exist
<TicketNumber> - <TicketStatus>
HistoryNote:<history.Description>

There are other sections but I am struggling with this one. The end result will be a column with Quote results, Ticket results, Knowledge base results.

this is what I have in code currently:
Quote:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
Dim config As CommitCRM.Config
Try
' For the sake of simplicity of this sample, all the settings are hardcoded in this source file.
config = New CommitCRM.Config
config.AppName = "CommitWebITF"
config.CommitDllFolder ="_"
config.CommitDbFolder = "_"

' CommitCRM.Application.Initialize must be the first call before invoking any other CommitCRM library method
' and it should be done only once in the program's lifetime.
CommitCRM.Application.Initialize(config)
Catch ex As Exception
Console.Out.Write(ex.Message)
End Try


End Sub



Protected Sub cSearchbtn_Click(sender As Object, e As EventArgs) Handles cSearchbtn.Click

Try

Dim ticketSearch As New CommitCRM.ObjectQuery(Of CommitCRM.Ticket)
Dim HistorySearch As New CommitCRM.ObjectQuery(Of CommitCRM.HistoryNote)
Dim tickets As New List(Of CommitCRM.Ticket)
Dim history As New List(Of CommitCRM.HistoryNote)

ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.D escription, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
HistorySearch.AddCriteria(CommitCRM.HistoryNote.Fi elds.RelLinkREC_ID, CommitCRM.OperatorEnum.opEqual, "%" & Ctxt.Value & "%")

For Each tic In ticketSearch.FetchObjects()
tickets.Add(tic)

HistorySearch.AddCriteria(CommitCRM.HistoryNote.Fi elds.Description, CommitCRM.OperatorEnum.opEqual, tic.TicketREC_ID)
For Each hist In HistorySearch.FetchObjects()
history.Add(hist)
Next
Next
DView1.DataSource = tickets
DView1.DataBind()
Repeater1.DataSource = tickets
Repeater1.DataBind()



Catch ex As Exception
Console.Out.Write(ex.Message)
Finally
' Before exit we should call CommitCRM.Application.Terminate to gracefully release all application resources
' and this should be done only once in the program's lifetime.
CommitCRM.Application.Terminate()
End Try
End Sub
 
January 5th, 2015, 12:00 PM
Support Team
 
Posts: 7,510
Thanks. It looks like the ticket search should work (though you may want to add search criteria for the searched keyword in the ticket's Resolution and Notes fields as well).

When it comes to History search it looks like there's a glitch as it seems like it searches for the Ticket.RECID value within the History Description and this will not work. The keyword is to be searched within the History Description and the resultset will include the TicketRECID of each such record matched.
You will then be able to query the Tickets table with the ~History.TicketRECID returned value per result to get the Ticket number, status, etc. so you will be able to display it together with the results.

Hope this helps.
 
January 5th, 2015, 12:17 PM
Mike C
 
Posts: 41
you mean this? HistorySearch.AddCriteria(CommitCRM.HistoryNote.Fi elds.RelLinkREC_ID, CommitCRM.OperatorEnum.opEqual, tic.TicketREC_ID)
 
January 5th, 2015, 12:52 PM
Mike C
 
Posts: 41
When you add more addCriteria option such as:
Quote:
ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.D escription, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
'ticketSearch.AddCriteria(CommitCRM.HistoryNote.Fi elds.Description, CommitCRM.OperatorEnum.opEqual, "%" & Ctxt.Value & "%")
ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.R esolution, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.N otes, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
is it an And or Or query?
 
January 5th, 2015, 01:02 PM
Support Team
 
Posts: 7,510
That's an AND.

But, you cannot mix between different objects, if you query Accounts the criteria needs to be constructed of Accounts fields, when querying Tickets - then tickets fields, etc.
 
January 5th, 2015, 01:08 PM
Mike C
 
Posts: 41
Is there a way to do an OR query. Such as "monkey" in Description, or resolution, or notes?

Or would that have to be multiple queries?
like

[quote]
ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.D escription, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
For Each tic In ticketSearch.FetchObjects()
tickets.Add(tic)
Next

ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.R esolution, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
For Each tic In ticketSearch.FetchObjects()
tickets.Add(tic)
Next

ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.N otes, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
For Each tic In ticketSearch.FetchObjects()
tickets.Add(tic)
Next

rptTicket.DataSource = tickets
rptTicket.DataBind()

Then somehow remove duplicates
 
January 5th, 2015, 01:50 PM
Support Team
 
Posts: 7,510
Yes, this is possible. When creating the QueryObject, instead:

RangerMSP.ObjectQuery<RangerMSP.Account> accountSearch = new RangerMSP.ObjectQuery<RangerMSP.Account>();

one can call:

RangerMSP.ObjectQuery<RangerMSP.Account> accountSearch = new RangerMSP.ObjectQuery<RangerMSP.Account>(linkOR);

Hope this helps.
 
January 5th, 2015, 02:05 PM
Mike C
 
Posts: 41
I'm not sure I understand, sorry not a very experienced .net programmer
 
January 5th, 2015, 02:18 PM
Mike C
 
Posts: 41
Not really sure what you are saying with turning this into an OR statement.

Dim ticketSearch As New CommitCRM.ObjectQuery(Of CommitCRM.Ticket)
Dim tickets As New List(Of CommitCRM.Ticket)

ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.D escription, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.R esolution, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
ticketSearch.AddCriteria(CommitCRM.Ticket.Fields.N otes, CommitCRM.OperatorEnum.opLike, "%" & Ctxt.Value & "%")
 
January 6th, 2015, 06:05 AM
Support Team
 
Posts: 7,510
When create the ObjectQuery you can pass it with a parameter that sets it to OR, unlike the default AND.

In any case, we've discussed it here and it seems like achieving what you're trying to do may be much easier with using the ODBC API that lets you query the database directly using SQL. With a single query you can merge results between database tables, perform joins, include advanced boolean conditions and anything else the SQL language supports.

We recommend that you check ODBC API and see how it works for you. You can then continue using the API libraries, discussed above, to edit existing records, insert new ones, etc.

Hope this helps.
 
January 6th, 2015, 09:10 AM
Mike C
 
Posts: 41
Thanks. I was thinking the same thing. Using a SQL script would be more beneficial in this case. I currently don't have plans to update/add records through the API (though that could change) so a readonly query is ideal.
 
January 7th, 2015, 12:29 PM
Mike C
 
Posts: 41
Have you created a Linkedserver to CommitCRM db?
 
January 7th, 2015, 01:33 PM
Support Team
 
Posts: 7,510
We're not following you on this one, please elaborate.
 
January 8th, 2015, 03:44 PM
Mike C
 
Posts: 41
Using the above examples how would I pass it that OR parameter I have searched around and am not seeing this anywhere.
 
January 9th, 2015, 06:02 AM
Support Team
 
Posts: 7,510
Once you initialize the object with the OR condition, all query criteria that you add will have an OR condition between each other.
 
January 9th, 2015, 09:08 AM
Mike C
 
Posts: 41
Thats my issue I cannot find how to initialize the OR condition. Using VB.net the info you gave me is not working. I cannot seem to find any info about (linkOR)

ticketSearch.AddCriteria(Ticket.Fields.Description , OperatorEnum.opLike, "%" & Ctxt.Value & "%")(linkOR)
ticketSearch.AddCriteria(Ticket.Fields.Resolution, OperatorEnum.opLike, "%" & Ctxt.Value & "%")
 
January 9th, 2015, 09:11 AM
Support Team
 
Posts: 7,510
The OR should be added/set when the RangerMSP.ObjectQuery object is created and not with each criteria added. Then the condition between all of the different criteria that you add is OR.

Hope this helps.
 
January 9th, 2015, 09:25 AM
Mike C
 
Posts: 41
Like so?

Dim ticketSearch As New CommitCRM.ObjectQuery(Of Ticket)(linkOr)
 
January 9th, 2015, 10:36 AM
Support Team
 
Posts: 7,510
This should work:

Dim ticketSearch As New RangerMSP.ObjectQuery(Of RangerMSP.Ticket)(RangerMSP.LinkEnum.linkOR)
 
January 9th, 2015, 12:13 PM
Mike C
 
Posts: 41
What would cause the application to not be able to load the CmtDbEng.dll? I have tried to disassemble the DLL with dotPEEK and it is "not supported". Commit is working fine, so I am assuming the DLL's are fine as well.
 
January 9th, 2015, 12:40 PM
Support Team
 
Posts: 7,510
It's hard to tell. This is probably not related to the dll itself. One thing though - try running your app from the folder the dll is found under ../ThirdParty/... folder, and do not simply copy the dll elsewhere as it has other dependencies to other dlls, etc. that are located in the same folder.
 
January 9th, 2015, 12:43 PM
Mike C
 
Posts: 41
Dim config As CommitCRM.Config

config = New CommitCRM.Config
config.AppName = "CommitWebITF"
config.CommitDllFolder = "\\...\...\CommitCRM\ThirdParty\UserDev"
config.CommitDbFolder = "\\...\...\CommitCRM\Db\"


This is my setup and it was working just fine till yesterday. Verified that there were no changes to permissions, etc.
 
January 9th, 2015, 12:49 PM
Support Team
 
Posts: 7,510
All looks good. As it was working it has to be related to a change that was made, of some sort. Unfortunately this is not something we can probably help with... please do update here once you find the cause. Thanks!
 
January 12th, 2015, 06:52 PM
Mike C
 
Posts: 41
The error I am getting is "Unable to load DLL 'CmtDbQry.dll': The specified module could not be found" I am running this from a webserver on the same network as the CommitCRM application. I verify that the DLL exists in the given path. Any thoughts?
 
January 13th, 2015, 06:04 AM
Support Team
 
Posts: 7,510
This is all external and related to your configuration so it's hard to say. Our guess is permissions where your program, or the user it runs under, does not have the privileges to access the folder or the dll file itself over the network. Please try to first make it work locally on the server and only then try to see how it goes with running it from other machines on your network pointing to the dll on the server.

Hope this helps.
 
January 20th, 2015, 09:05 AM
Mike C
 
Posts: 41
Issue was found and I am once again connecting, however I am still having issues with the enumLink.linkOR.

Quote:
Dim ticketSearch1 As New CommitCRM.ObjectQuery(Of Ticket)(CommitCRM.LinkEnum.linkOR)

ticketSearch1.AddCriteria(Ticket.Fields.Descriptio n, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch1.AddCriteria(Ticket.Fields.Resolution , OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch1.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & SearchString & "%")
The quantify this, there is a ticket with monkey in the Description and Resolution but not in Notes. When I test the results I get nothing. If I comment the AddCriteria for notes I get the result I expected.

I have scoured the web for information on AddCriteria and/or LinkEnum.linkOR and can find NOTHING. Any help is appreciated or a push to find out more info on AddCriteria or linkOR.
 
January 20th, 2015, 09:59 AM
Support Team
 
Posts: 7,510
Thanks for posting. Apparently you'll need to apply the following fix to the VB.NET API version that you have, this should resolve this issue:

In the file QueryCommand.vb, the function at line 14:


Protected Sub New(ByVal dataKind As DataKind, ByVal linkEnum As LinkEnum, ByVal nMaxRecordCount As Integer)

objWhere_ = New CriteriaExpressionGroup(linkEnum_)
objSort_ = New SortExpressionGroup()

queryRequest_ = New QueryRequest(dataKind, nMaxRecordCount)
End Sub


should become:


Protected Sub New(ByVal dataKind As DataKind, ByVal linkEnum As LinkEnum, ByVal nMaxRecordCount As Integer)

linkEnum_ = linkEnum
objWhere_ = New CriteriaExpressionGroup(linkEnum_)
objSort_ = New SortExpressionGroup()

queryRequest_ = New QueryRequest(dataKind, nMaxRecordCount)
End Sub
 
January 20th, 2015, 11:42 AM
Mike C
 
Posts: 41
That didn't work, and actually broke the search completely. No return results at all.
Below is my code for this search.

Quote:
Imports CommitCRM
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text.RegularExpressions
Imports System.Web.UI

Partial Class _Default
Inherits Page

Private SearchString As String = ""

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub



Public Sub cSearchbtn_Click(sender As Object, e As EventArgs) Handles cSearchbtn.Click
SearchString = Ctxt.Value.ToString()
'MsgBox(SearchString)
Try

Dim ticketSearch As New CommitCRM.ObjectQuery(Of Ticket)(CommitCRM.LinkEnum.linkOR)
Dim HistorySearch As New CommitCRM.ObjectQuery(Of HistoryNote)(CommitCRM.LinkEnum.linkOR)
Dim tickets As New List(Of CommitCRM.Ticket)
Dim history As New List(Of CommitCRM.HistoryNote)

ticketSearch.AddCriteria(Ticket.Fields.Description , OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Resolution, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddSortExpression(Ticket.Fields.DueDa te, SortDirectionEnum.sortDESC)
If tickets.Count > 0 Then
For Each tic In ticketSearch.FetchObjects()
tickets.Add(tic)
HistorySearch.AddCriteria(HistoryNote.Fields.RelLi nkREC_ID, CommitCRM.OperatorEnum.opEqual, tic.TicketREC_ID)
For Each hNote In HistorySearch.FetchObjects()
history.Add(hNote)
Next
histNotes.DataSource = history
histNotes.DataBind()
Next
End If

rptTicket.DataSource = tickets
rptTicket.DataBind()


Catch ex As Exception
Console.Out.Write(ex.Message)
End Try
SearchString = ""
End Sub



End Class
 
January 20th, 2015, 11:47 AM
Support Team
 
Posts: 7,510
We cannot debug this source code. Please ignore the change suggestion then and roll back any change you performed to the library.
Things will work as they have before and instead of querying using the OR condition run three separate searches, merge the resultset into one (e.g. merge the three list of RECIDs into one) and then fetch the relevant ticket records. This will actually implement the OR condition in a different, kinda longer, way though it should work well.
 
March 4th, 2015, 12:37 PM
Mike C
 
Posts: 41
Again Thanks for your support. I am finally coming back to this project and have since moved some of the config from the Global.aspx to the CommitCRM dll, primarily the config info. Am I missing something? When I attempt a connection it is not returning anything and then giving me a NULL reference error.

DLL (config.vb) private information removed
Quote:
Public Class Config
Public AppName As String = "CommitCrm"
Public CommitDllFolder As String = "\\...\CommitCRM\ThirdParty\UserDev\"
Public CommitDbFolder As String = "\\...\CommitCRM\Db\"
Public InitCommitApiDll As Boolean = True
Public InitCommitQryDll As Boolean = True
Public Params As New List(Of KeyValuePair(Of String, String))
Public RaiseExceptionIfDatabaseFieldTruncated As Boolean = False
Public DbEngPassword As String = ""
Public DbQryPassword As String = ""
Public UseWebAPI As Boolean = False
Public WebAPIUrl As String = ""
End Class
End Namespace
search vb
Quote:
Try
'Commit Database
Dim ticketSearch As New CommitCRM.ObjectQuery(Of CommitCRM.Ticket)(LinkEnum.linkOR)
Dim tickets As New List(Of CommitCRM.Ticket)
'Add Search Criteria
ticketSearch.AddCriteria(Ticket.Fields.Description , OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Resolution, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & SearchString & "%")
ticketSearch.AddSortExpression(Ticket.Fields.DueDa te, SortDirectionEnum.sortDESC)

For Each tic In ticketSearch.FetchObjects()
I get to this stage when the NULLReference error is thrown. Can you see anything that I am missing or have backwards? That will at least allow me to know the connection coding is solid and I can focus my troubleshooting elsewhere. I'm not getting the "cannot access *.dll errors so I am assuming the connection is good but something else is the issue.

Thanks
 
March 4th, 2015, 01:10 PM
Support Team
 
Posts: 7,510
From a quick review it seems to be in order, however, it's hard to tell, it might be related to privileges accessing the folders, something in your RAD setup and plenty of other stuff. Maybe you can start from scratch and follow the exact and detailed samples, accessed from API page.
 
March 4th, 2015, 03:40 PM
Mike C
 
Posts: 41
Thanks. At least I know the coding is good on the CommitCRM portion. I'll come back with questions and answer when I get it.


Thanks
 
May 29th, 2015, 09:31 AM
Mike C
 
Posts: 41
Back to working on this. I have been able to get all aspects working, however the return is unbearably slow. If I create a linked server and use that connection then the resultset return is almost instantaneous and I also get a more consistent dataset. However I frequently run out of users using this method (assumed as I can log off a user and the page starts working again). So two questions.
1- Can I somehow limit to one user when using ODBC connections
2- Is there a way to use a SQL query with the API. (not finding it in the WIKI). I would be willing to use the API if I can use a query and improve the speeds I get when using it.
 
May 29th, 2015, 09:49 AM
Support Team
 
Posts: 7,510
Thank you for the update. What do you refer by linked-server?
Also, the API itself converts your query into SQL.

If you query with the API then our main tip would be to select which fields you want to query, as when you do not it uses * (e.g. all fields from all related tables of the item your querying, like tickets).

For example, here is how you could fetch the accounts for only two fields:

lstAccounts = objQuery.FetchObjects(RangerMSP.Account.Fields.Acc ountREC_ID.Key + "," + RangerMSP.Account.Fields.FileAs.Key)

So, basically you need to pass a comma separated string with the field names that you need access to.

Hope this helps.
 
May 29th, 2015, 11:34 AM
Mike C
 
Posts: 41
a linked server is something used in SQL Server to link to a remote database and still use in queries on the local database instance. For me at least they seem to perform better than a direct ODBC connection in .net, but again I am a .net novice. But as I mentioned we seem to be running out of users when using the search. The server also says that we have 4 fewer users than what we purchased.
 
May 29th, 2015, 12:02 PM
Support Team
 
Posts: 7,510
Thank you. From what we can tell you use SQL and have up to 10 concurrent database connections. Each machine or remote session that connects to the database consumes a single connection and this includes ODBC connections. In case you do not free the connection some might get wasted.

In case you wish to discuss this specific issue further please email us directly and this has stuff to do with licensing.

Thanks!
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search



All times are GMT -6. The time now is 04:05 AM.

Archive - Top    

RangerMSP - A PSA software designed for MSPs and IT Services Providers
Forum Software Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.