API - Trying to pull all tickets that include X

[Mike C]

I am working on a standalone search engine that will return all tickets and history notes, where the search word(s) are in the ticket or history notes.

I have the ticket portion working, but I am missing tickets where the word(s) are not in the TICKETS but are in the HISTORYNOTE description.

So the question is there a way to add a search with the tickets that will also search the historynotes and return the ticket result? I have a section that will then go into the historynotes and return all the historynotes for the given ticket.

If not what route would you suggest? I am trying to use the API first before going down the ODBC/linkedserver route.

Also trying to create the search to search for multiple words in any order within the tables (IE: if I enter "Jump new" in search it will look for tickets that contain both jump AND new in either description, resolution, or notes. So it could return "Sally has a new jumprope" or "John jumped the new truck")


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

For Each n In SearchString.Split(New Char() {" "c})
ticketSearch.AddCriteria(Ticket.Fields.Description , OperatorEnum.opLike, "%" & n & "%")
ticketSearch.AddCriteria(Ticket.Fields.Resolution, OperatorEnum.opLike, "%" & n & "%")
ticketSearch.AddCriteria(Ticket.Fields.Notes, OperatorEnum.opLike, "%" & n & "%")
Next


For Each tic In ticketSearch.FetchObjects()
Response.BufferOutput = True
tickets.Add(tic)
' Get associated HistoryNotes
Dim HistorySearch As New CommitCRM.ObjectQuery(Of HistoryNote)(LinkEnum.linkOR)
HistorySearch.AddCriteria(HistoryNote.Fields.RelLi nkREC_ID, CommitCRM.OperatorEnum.opEqual, tic.TicketREC_ID)
'HistorySearch.AddSortExpression(HistoryNote.Field s.Date, SortDirectionEnum.sortDESC)
Dim history As New List(Of CommitCRM.HistoryNote)
For Each hNote In HistorySearch.FetchObjects()
history.Add(hNote)
Next
histNotes.DataSource = history
histNotes.DataBind()
Next
rptTicket.DataSource = tickets
rptTicket.DataBind()


Again I am stuck at pulling tickets where the result is in the history notes only and the multiple results

Any direction would be appreciated

[Mike C]

My thoughts on this would be to maybe create two lists
-Tickets
-History Notes
This would give me a list of all the ticketId's of all the tickets and history notes.

Then combine those two lists and return all tickets with history notes for the tickets in the combined list.


'Generate Lists to combine
For Each x In histSearch.FetchObjects()
hlist.Add(x)
Next

For Each y In ticketSearch.FetchObjects()
tlist.Add(y)
Next


This would create the two lists just not sure how to combine them and then pull all the tickets from the api. PLEASE HELP

[Support Team]

Yes, this makes sense, you should query tickets, query history notes and then based on the ticket identifier as part of the returned history notes bring the ticket details of that note. Then you can list all tickets after merging to two lists, ones that results were found as part of the ticket together with tickets that are listed because to search term was found in an history note linked to them.

Hope this helps.

[Mike C]

Probably the wrong forum for this, but still having issues with combining the lists and using them to query the ticket table. Any help or direction would be appreciated. I hve not programmed this stuff in far too long.

[Mike C]

I could use the UNION(of T) but the resultsets are not the same. In my fetchobjects() can I fetch just the Ticket ID and RelLinkREC_ID?

[Support Team]

Yes, you can select only specific fields, for example, here is how you could fetch only two fields of accounts:


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.

In any case, there isn't probably much we can help with as, as you mentioned yourself, it seems to be related to coding in more general and less to RangerMSP. Maybe someone here will be able to help or maybe you can get someone working with you for a very reasonable fee on sites like eLance (www.elance.com) or oDesk (www.odesk.com).

Hope this makes sense and helps.

[Mike C]

One last question on this. Are the search results case insensitive? IE Parker is the same as parker.. I don't recall seeing it in the Wiki

[Mike C]

What is lstAccounts? List(of CommitCRM.Account) , List (of String) ...?

[Support Team]

Sure, thanks for asking. It's case insensitive.

[itognet]

select * from TICKETS where contains(*, 'what to find');

select * from HISTORY-NOTE where contains(*, 'what to find');


It is possible that you have to enable full text search on the database.

[Support Team]

Some full text indexes exist, but in general we would not recommend basis too many queries on these. This recommendation will most likely change in a future release. Thanks for asking.

[Mike C]

Already tried a contains clause using ODBC with errors. I am still attempting to use the API to return results.

[Support Team]

The above SQL queries, asked about by itognet should not be used.