|
|||
|
|||
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. |
|
|||
|
|||
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.
|
|
|||
|
|||
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 |
|
|||
|
|||
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. |
|
|||
|
|||
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:
|
|
|||
|
|||
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. |
|
|||
|
|||
When you add more addCriteria option such as:
Quote:
|
|
|||
|
|||
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 |
|
|||
|
|||
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. |
|
|||
|
|||
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 & "%") |
|
|||
|
|||
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. |
|
|||
|
|||
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 & "%") |
|
|||
|
|||
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.
|
|
|||
|
|||
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. |
|
|||
|
|||
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. |
|
|||
|
|||
Issue was found and I am once again connecting, however I am still having issues with the enumLink.linkOR.
Quote:
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. |
|
|||
|
|||
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 |
|
|||
|
|||
That didn't work, and actually broke the search completely. No return results at all.
Below is my code for this search. Quote:
|
|
|||
|
|||
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. |
|
|||
|
|||
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:
Quote:
Thanks |
|
|||
|
|||
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.
|
|
|||
|
|||
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. |
|
|||
|
|||
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. |
|
|||
|
|||
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.
|
|
|||
|
|||
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! |