Simple search site help

[Mike C]

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.

[Support Team]

Re: Simple search site help

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.

[Mike C]

Re: Simple search site help

I assume you are talking about merging the list collections? I will have to look into that.

Thanks

[Support Team]

Re: Simple search site help

Yes, exactly.

[AN-Tech]

Re: Simple search site help

We really need a way to search through all fields in Commit directly. It's too complicated to find what we are looking for.

[Mike C]

Re: Simple search site help

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.

[Support Team]

Re: Simple search site help

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.

[Mike C]

Re: Simple search site help

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

[Support Team]

Re: Simple search site help

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.

[Mike C]

Re: Simple search site help

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:

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

[Support Team]

Re: Simple search site help

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.

[Mike C]

Re: Simple search site help

you mean this? HistorySearch.AddCriteria(CommitCRM.HistoryNote.Fi elds.RelLinkREC_ID, CommitCRM.OperatorEnum.opEqual, tic.TicketREC_ID)

[Mike C]

Re: Simple search site help

When you add more addCriteria option such as:

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?

[Support Team]

Re: Simple search site help

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.

[Mike C]

Re: Simple search site help

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