pratigan
asked on
Need to Create an Automated On Call escalation list
Need to have an escalation list for on call support. The need to enter names, contact info and dates of support range (calendar field).
Wondering the best way to do this. the list would be able to be filtered by an operations staff for looking up support names for specific areas. Update capability for actual support staff to update their info and for managers to change support resources like a rotation or date range. How to display the filtered results ?? Easy of updating for support staff and managers.
Is there anything out there in notes that would be able to do this without having to create something from scratch ?? I current have an application lotus notes database that has a lot of functionality that I'd like to add this feature to.
Wondering the best way to do this. the list would be able to be filtered by an operations staff for looking up support names for specific areas. Update capability for actual support staff to update their info and for managers to change support resources like a rotation or date range. How to display the filtered results ?? Easy of updating for support staff and managers.
Is there anything out there in notes that would be able to do this without having to create something from scratch ?? I current have an application lotus notes database that has a lot of functionality that I'd like to add this feature to.
Hi Paul,
Could you give an example of the use of the data you intend to collect and use? If I understood your question, you want to
- find all users specialized in X
- or search one of their peers
- or a manager
in case a problem needs to be escalated.
I suppose you want to select names from a list? And maybe an extra list with people to escalate to? Is that it?
By the way, afaik the names.nsf database contains fields for managers, but it's a rather static thing. Not sure here.
Could you give an example of the use of the data you intend to collect and use? If I understood your question, you want to
- find all users specialized in X
- or search one of their peers
- or a manager
in case a problem needs to be escalated.
I suppose you want to select names from a list? And maybe an extra list with people to escalate to? Is that it?
By the way, afaik the names.nsf database contains fields for managers, but it's a rather static thing. Not sure here.
ASKER
Hello,
Thanks for the feedback. Let me clarify more.
I currently maintain an in house lotus notes database that contains multiple areas of IT functionality and automated emailings for request forms.
i need to add a function section that allows the operations to filter escalation contacts through a possible menu selections. This would then give them names and contact information for off hours support and dates if jobs fail and need to be resolved. What I'm thinking is a means of entering the contact info initially along with date ranges for support staff. Then a feature where operations staff can filter the contacts based upon a jobname or support area or something. The support info and names would be able to be updated by support staff managers but be read only by the operations staff. This would be an in house function created.
hope this clarifys.
Thanks,
Paul
Thanks for the feedback. Let me clarify more.
I currently maintain an in house lotus notes database that contains multiple areas of IT functionality and automated emailings for request forms.
i need to add a function section that allows the operations to filter escalation contacts through a possible menu selections. This would then give them names and contact information for off hours support and dates if jobs fail and need to be resolved. What I'm thinking is a means of entering the contact info initially along with date ranges for support staff. Then a feature where operations staff can filter the contacts based upon a jobname or support area or something. The support info and names would be able to be updated by support staff managers but be read only by the operations staff. This would be an in house function created.
hope this clarifys.
Thanks,
Paul
In your database, I assume you have a Person document. Can you add a field Subordinates, multi-value, that contains zero or more names of other Persons? Likewise, you could add a field Peers if you need one, to contain the Person's colleagues (in case colleagues can come from different managers).
Then build a view, categorised, first column contains the field Subordinates, 2nd the Person's name, so you can do a simple @Lookup. Peers can be found in a similar way, in a different view.
Is that what you're looking for?
Then build a view, categorised, first column contains the field Subordinates, 2nd the Person's name, so you can do a simple @Lookup. Peers can be found in a similar way, in a different view.
Is that what you're looking for?
ASKER
I believe something like that. I was thinking a form that allows support users to enter their information, contact numbers, available dates and support areas. Then a view that would be filtered and generated based upon another person going into that form area and selecting an area that needs support. then the view below the filter would show just the results of the filter selections from the above.
Something like this.
Something like this.
Can't get a clear picture of it in my mind...
In a Person doc, each person can enter one or more support areas. A Person view can be categorized by support area, no problem.
But where's the escalation then?
In a Person doc, each person can enter one or more support areas. A Person view can be categorized by support area, no problem.
But where's the escalation then?
ASKER
The escalation is more of a look up and narrow down on the support person for a particular area. The datacenter staff has a situation that requires a support person to be called. They go into this function and enter "filter" criteria to narrow down the names and contact details in order to have the name and info of the correct support person for that particular problem. This is the escalation. A list that identifies staff based upon a filter query.
I was thinking of something like a form that has say 6 or 7 fields, drop downs that support staff and managers would enter names and info into. this is also the area where the datacenter staff would select filter info such as subsystem name or areas. when they are done with the selections they press a go button or something and an imbedded view within the form displays the entries that match the criteria they entered.
Something like that ???
I was thinking of something like a form that has say 6 or 7 fields, drop downs that support staff and managers would enter names and info into. this is also the area where the datacenter staff would select filter info such as subsystem name or areas. when they are done with the selections they press a go button or something and an imbedded view within the form displays the entries that match the criteria they entered.
Something like that ???
Ok, but I don't know if it can be done using an embedded view.
To search matching people, I'd use an FTSearch in LotusScript, using a query based on the fields on your form. You could of course put those the documents found in a folder and display the folder like a view, but you could also display a text-based extract of the documents matched. Creating the query can sometimes be a little tricky but it's doable.
To search matching people, I'd use an FTSearch in LotusScript, using a query based on the fields on your form. You could of course put those the documents found in a folder and display the folder like a view, but you could also display a text-based extract of the documents matched. Creating the query can sometimes be a little tricky but it's doable.
ASKER
I've created a query before, and yes it's a lot of work. I'm trying to come up with a way that the support staff would input all their contact information. Then the only changes they would make are the date ranges as time goes on. Possible a new support person would add themselves as resources change but once their in there they only would update dates. The top of the form would contain the necessary fields they would have to enter and that would be where data center staff would enter info their looking for.
I'm thinking the creation of the support records would be entered and saved using the form and showable in a view.
you don't think an embedded view in the bottom of the form would work ??? can't filter onn that view by the criteria selected. ?
I'm thinking the creation of the support records would be entered and saved using the form and showable in a view.
you don't think an embedded view in the bottom of the form would work ??? can't filter onn that view by the criteria selected. ?
ASKER
any other ideas ??? This is starting to seem more difficult than I originally thought.
Sorry, was away...
No, creating a query is not a lot of work; it might not be easy, that's all. But the result of a query can never be shown in a view. You'd have to put the documents you found in a folder, and then you can show the folder. But what if two people execute a different query at the same time? So you need a folder per query, and that would make the application a lot more complex to maintain.
Hence my suggestion to present contacts in a list, e.g. using @Prompt with the OkCancelList style (or the equivalent in LotusScript).
No, creating a query is not a lot of work; it might not be easy, that's all. But the result of a query can never be shown in a view. You'd have to put the documents you found in a folder, and then you can show the folder. But what if two people execute a different query at the same time? So you need a folder per query, and that would make the application a lot more complex to maintain.
Hence my suggestion to present contacts in a list, e.g. using @Prompt with the OkCancelList style (or the equivalent in LotusScript).
ASKER
Hi Sjef,
I've pretty much got the query layout coded. I was thinking if I display the query results in the bottom of the query criteria screen then it would appear like a view. I'm not concerned about retaining the results of the query so the folder shouldn't be a concern.
Tell me more about the contacts and contact info in a list using prompt. if that is a better way to go about this, then I'm open.
Thx.
I've pretty much got the query layout coded. I was thinking if I display the query results in the bottom of the query criteria screen then it would appear like a view. I'm not concerned about retaining the results of the query so the folder shouldn't be a concern.
Tell me more about the contacts and contact info in a list using prompt. if that is a better way to go about this, then I'm open.
Thx.
It's not retaining the results, it's the parallel execution: imagine what happens when A does a query, then B, and then A fetches the folder contents...
The list can be a string array, each array element could be like this:
"John Doe (Asst Pooper Scooper)|NT0001AFFA"
As almost everywhere in Notes, the first part (before the vertical bar) is the text shown to the user in the list, the 2nd part stays internal, so you could put any key in it. In this case I used a NoteId. The return value of the call to Prompt is then a key that should identify the person selected.
The list can be a string array, each array element could be like this:
"John Doe (Asst Pooper Scooper)|NT0001AFFA"
As almost everywhere in Notes, the first part (before the vertical bar) is the text shown to the user in the list, the 2nd part stays internal, so you could put any key in it. In this case I used a NoteId. The return value of the call to Prompt is then a key that should identify the person selected.
ASKER
I see what your saying. The fetch from user A would actually return user Bs results.
It's unlikely, yet not impossible, especially when a user can refresh the view ("OOPS!").
ASKER
lol .. right.
So tell me more about the user contact details list.
I'm not catching the full idea.
So tell me more about the user contact details list.
I'm not catching the full idea.
In pseudo-pseudo code:
assemble the query
do a search
list= [] // empty array
for all documents found
text= name + function + phone
key= doc.NoteId
list add (text + "|" + key)
end for
returnkey= ws.prompt(OkCancelList, list)
if returnkey is a key then
:
:
end if
Please look up the parameters for ws.prompt in the Help database, and also how to add elements to an array.
ASKER
ok.. I think I've got the query assembly completed.
I'm going to test it with simple reporting to the screen for now and then address the list add feature after.
I will be in touch with results
I'm going to test it with simple reporting to the screen for now and then address the list add feature after.
I will be in touch with results
*crossing fingers*
ASKER
ok.... I've completed the query. I've input test data in the search and I ran the query. I keep getting 0 results. Trying to debug now. here is the query. Do you see anything wrong ?? I know it's a lot. But it reads pretty good.
'Search parameter fields are identified by RPT_ and the results document fields by RES_
Sub Click(Source As Button)
'High level variables
Dim ws As New NotesUIWorkSpace
Dim session As New NotesSession
Dim db As NotesDatabase
'Query parameters
Dim cFTQuery As String 'Full text - final query
Dim cFormQuery As String 'escalation Form
Dim cAccntQuery As String 'What account ?
Dim csubsysQuery As String 'What subsystem ?
Dim cjobQuery As String 'What job qualifier ?
Dim cSuptypQuery As String 'What support type ?
Dim cNameQuery As String 'What Name ?
Dim cDateQuery As String 'What Date Range or criteria ?
'Document parameters
Dim dcToClear As NotesDocumentCollection
'Folders will be cleared using this doc.collection
Dim dc As NotesDocumentCollection
'Docs. matching the query are collected in this document collection
Dim doc As NotesDocument 'Result document - for displaying information
Dim resultsDoc As NotesDocument 'Report document
Dim uiresults As NotesUIDocument 'Report UI document
Dim uidoc As NotesUIDocument 'Search parameter UI doc.
Dim docSRC As NotesDocument 'Search parameter doc.
'Other variables
Dim richStyle As NotesRichTextStyle 'RTF style for formatting results page
' (Note:The results RTF will be created below)
Dim item As NotesItem
Dim n As Long, i As Integer
Dim nPercentDone As Integer 'Status bar
Print "Preparing report requirements..."
Set db = session.CurrentDatabase
Set uidoc = ws.CurrentDocument
'Get ready to read the parameters
If uidoc.EditMode Then
Call uidoc.Refresh
Call uidoc.Save
End If
' Clean the report - folders ( remove all documents from the folder )
Set dcToClear = db.AllDocuments
Call dcToClear.RemoveAllFromFol der("Searc h Results")
'Get parameters and start building individual queries - that take care of
' the parameters set by the user. We will latter create a combined query from
' these partial ones.
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
'1. Type of document - Production Escalate
cFormQuery = "FIELD Form = PRDescalate"
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
'2. Account Name
' Here is a list of projects. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_ accnt") = "All" Then
'If 'ALL' accounts wanted then do not subject to any
' query - and hence all projects will be returned
cAccntQuery = ""
Else
Set item = uidoc.Document.GetFirstIte m("PRDes_a ccnt")
n = 0
cAccntQuery = ""
Forall cValues In item.Values
cAccntQuery = cAccntQuery + "FIELD Account = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cAccntQuery = Left(cAccntQuery, Len(cAccntQuery) - 3)
End If
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
'3. Subsystem ' Here is a list of subsystems. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_ subsys") = "All" Then
'If 'ALL' Groups wanted then do not subject to any
' query - and hence all groups will be returned
csubsysQuery = ""
Else
Set item = uidoc.Document.GetFirstIte m("PRDes_s ubsys")
n = 0
csubsysQuery = ""
Forall cValues In item.Values
csubsysQuery = csubsysQuery + " FIELD Subsystem = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
csubsysQuery = Left(csubsysQuery, Len(csubsysQuery) - 3)
End If
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
'4. Job Qualifier ' Here is a list of qualifiers. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_ jobqual") = "All" Then
'If 'ALL' Teams wanted then do not subject to any
' query - and hence all Teams will be returned
cjobQuery = ""
Else
Set item = uidoc.Document.GetFirstIte m("PRDes_j obqual")
n = 0
cjobQuery = ""
Forall cValues In item.Values
cjobQuery = cjobQuery + " FIELD Job Qualifier = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cjobQuery = Left(cjobQuery, Len(cjobQuery) - 3)
End If
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
'5. Support Type ' Here is a list of support types. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_ sup_typ") = "All" Then
'If 'ALL' Teams wanted then do not subject to any
' query - and hence all Teams will be returned
cSuptypQuery = ""
Else
Set item = uidoc.Document.GetFirstIte m("PRDes_s up_typ")
n = 0
cSuptypQuery = ""
Forall cValues In item.Values
cSuptypQuery = cSuptypQuery + " FIELD Support Type = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cSuptypQuery = Left(cSuptypQuery, Len(cSuptypQuery) - 3)
End If
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
'6. Name ' Here is a list of Names. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_ contactnam e") = "All" Then
'If 'ALL' Teams wanted then do not subject to any
' query - and hence all Teams will be returned
cNameQuery = ""
Else
Set item = uidoc.Document.GetFirstIte m("PRDes_c ontactname ")
n = 0
cNameQuery = ""
Forall cValues In item.Values
cNameQuery = cNameQuery + " FIELD Name = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cNameQuery = Left(cNameQuery, Len(cNameQuery) - 3)
End If
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
'5. Date Criteria
If uidoc.FieldGetText("DueDat eSpecified ") <> "Support Date" Goto NoDateselect
If uidoc.FieldGetText("DateCr iteriaType ") = "Date Range" Then
cDateQuery = "FIELD PRDes_strt_dt >= " + uidoc.FieldGetText("Date1T ext") + " And " + "FIELD PRDes_end_dt <= " + uidoc.FieldGetText("Date2T ext")
Else
If uidoc.FieldGetText("DateCr iteriaType ") = "Specific Date" Then
cDateQuery = "FIELD PRDes_strt_dt = " + uidoc.FieldGetText("Date1T ext")
Else
If uidoc.FieldGetText("DateCr iteriaType ") = "All Entries After" Then
cDateQuery = "FIELD PRDes_strt_dt > " + uidoc.FieldGetText("Date1T ext")
Else
cDateQuery = ""
End If
End If
End If
NoDateSelect:
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
'Build the query. Append parts using AND but ignore if they are empty.
cFTQuery = "( " + cFormQuery + ")"
If cAccntQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cAccntQuery + ")"
End If
If csubsysQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + csubsysQuery + ")"
End If
If cjobQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cjobQuery + ")"
End If
If cSuptypQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cSuptypQuery + ")"
End If
If cNameQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cNameQuery + ")"
End If
If cDateQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cDateQuery + ")"
End If
'Display query
If uidoc.EditMode Then
Call uidoc.FieldSetText("RPT_FT Query", cFTQuery)
End If
'Run query against the database
Print "Searching..."
Set dc = db.FTSearch(cFTQuery,0)
'Create the Report document and choose the appropriate Reporting form
Set resultsDoc = db.CreateDocument
resultsDoc.Form = "Search Results for Escalation Lookup Query"
'Create the Rich Text Item. This is where the report goes. Also create a
' Rich Text Style object so that we can format the report
Dim richText As New NotesRichTextItem(resultsD oc, "RES_Results")
Set richStyle = session.CreateRichTextStyl e
'Basic report body text format
richStyle.NotesFont = FONT_HELV
richStyle.FontSize = 8
richStyle.Bold = False
Call richText.AppendStyle(richS tyle)
'Store the query title, query, Author and no. of docs. found, for latter reference
resultsDoc.RPT_QueryTitle = uidoc.FieldGetText("RPT_Qu eryTitle")
resultsDoc.RPT_Author = uidoc.FieldGetText("From")
resultsDoc.RES_Query = cFTQuery
resultsDoc.RES_NoOfDocs = dc.Count
'Start generating the report. The document collection 'dc' contains
' the documents fetched by the query.
For n = 1 To dc.Count
Set doc = dc.GetNthDocument(n)
Call richText.AddNewLine(1)
Call richText.AppendText(Cstr(n ) + ".")
'Add doc-link
Call richText.AddTab(1)
Call richText.AppendDocLink(doc , "Click on link to launch the document")
Call richText.AddTab(1)
'UserName & Dates in BOLD type
richStyle.Bold = True
Call richText.AppendStyle(richS tyle)
Call richText.AppendText(doc.Ge tFirstItem ("PRDes_co ntactname" ).Text)
Call richText.AddTab(1)
Call richText.AppendText(Dateva lue(doc.Ge tFirstItem ("PRDes_st rt_dt").Te xt)+ " - ")
Call richText.AppendText(Dateva lue(doc.Ge tFirstItem ("PRDes_en d_dt").Tex t))
Call richText.AddTab(1)
'Subsystem
richStyle.Bold = False
Call richText.AppendStyle(richS tyle)
Call richText.AddTab(1)
Call richText.AppendText(doc.Ge tFirstItem ("PRDes_su bsys").Tex t)
'View like reports
Call doc.PutInFolder( "Search Results")
'Update status bar
nPercentDone = (n * 100)/dc.Count
Print "Percent done : " + Cstr(nPercentDone) + "%"
Next
'Save results document
Call resultsDoc.Save(True, False)
Msgbox Cstr(dc.Count) + " documents match your search criteria" + Chr$(10) + _
"Click 'Ok' to display the Report.", 64, "Search results"
'Display results document
Set uiResults = ws.EditDocument( False, resultsDoc, True)
End Sub
'Search parameter fields are identified by RPT_ and the results document fields by RES_
Sub Click(Source As Button)
'High level variables
Dim ws As New NotesUIWorkSpace
Dim session As New NotesSession
Dim db As NotesDatabase
'Query parameters
Dim cFTQuery As String 'Full text - final query
Dim cFormQuery As String 'escalation Form
Dim cAccntQuery As String 'What account ?
Dim csubsysQuery As String 'What subsystem ?
Dim cjobQuery As String 'What job qualifier ?
Dim cSuptypQuery As String 'What support type ?
Dim cNameQuery As String 'What Name ?
Dim cDateQuery As String 'What Date Range or criteria ?
'Document parameters
Dim dcToClear As NotesDocumentCollection
'Folders will be cleared using this doc.collection
Dim dc As NotesDocumentCollection
'Docs. matching the query are collected in this document collection
Dim doc As NotesDocument 'Result document - for displaying information
Dim resultsDoc As NotesDocument 'Report document
Dim uiresults As NotesUIDocument 'Report UI document
Dim uidoc As NotesUIDocument 'Search parameter UI doc.
Dim docSRC As NotesDocument 'Search parameter doc.
'Other variables
Dim richStyle As NotesRichTextStyle 'RTF style for formatting results page
' (Note:The results RTF will be created below)
Dim item As NotesItem
Dim n As Long, i As Integer
Dim nPercentDone As Integer 'Status bar
Print "Preparing report requirements..."
Set db = session.CurrentDatabase
Set uidoc = ws.CurrentDocument
'Get ready to read the parameters
If uidoc.EditMode Then
Call uidoc.Refresh
Call uidoc.Save
End If
' Clean the report - folders ( remove all documents from the folder )
Set dcToClear = db.AllDocuments
Call dcToClear.RemoveAllFromFol
'Get parameters and start building individual queries - that take care of
' the parameters set by the user. We will latter create a combined query from
' these partial ones.
'-------------------------
'1. Type of document - Production Escalate
cFormQuery = "FIELD Form = PRDescalate"
'-------------------------
'2. Account Name
' Here is a list of projects. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_
'If 'ALL' accounts wanted then do not subject to any
' query - and hence all projects will be returned
cAccntQuery = ""
Else
Set item = uidoc.Document.GetFirstIte
n = 0
cAccntQuery = ""
Forall cValues In item.Values
cAccntQuery = cAccntQuery + "FIELD Account = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cAccntQuery = Left(cAccntQuery, Len(cAccntQuery) - 3)
End If
'-------------------------
'3. Subsystem ' Here is a list of subsystems. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_
'If 'ALL' Groups wanted then do not subject to any
' query - and hence all groups will be returned
csubsysQuery = ""
Else
Set item = uidoc.Document.GetFirstIte
n = 0
csubsysQuery = ""
Forall cValues In item.Values
csubsysQuery = csubsysQuery + " FIELD Subsystem = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
csubsysQuery = Left(csubsysQuery, Len(csubsysQuery) - 3)
End If
'-------------------------
'4. Job Qualifier ' Here is a list of qualifiers. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_
'If 'ALL' Teams wanted then do not subject to any
' query - and hence all Teams will be returned
cjobQuery = ""
Else
Set item = uidoc.Document.GetFirstIte
n = 0
cjobQuery = ""
Forall cValues In item.Values
cjobQuery = cjobQuery + " FIELD Job Qualifier = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cjobQuery = Left(cjobQuery, Len(cjobQuery) - 3)
End If
'-------------------------
'5. Support Type ' Here is a list of support types. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_
'If 'ALL' Teams wanted then do not subject to any
' query - and hence all Teams will be returned
cSuptypQuery = ""
Else
Set item = uidoc.Document.GetFirstIte
n = 0
cSuptypQuery = ""
Forall cValues In item.Values
cSuptypQuery = cSuptypQuery + " FIELD Support Type = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cSuptypQuery = Left(cSuptypQuery, Len(cSuptypQuery) - 3)
End If
'-------------------------
'6. Name ' Here is a list of Names. We must build a query that accomodates all
' the options selected by the user. We traverse the multiple-choice list
' and build a set of OR-ed parameters.
If uidoc.FieldGetText("PRDes_
'If 'ALL' Teams wanted then do not subject to any
' query - and hence all Teams will be returned
cNameQuery = ""
Else
Set item = uidoc.Document.GetFirstIte
n = 0
cNameQuery = ""
Forall cValues In item.Values
cNameQuery = cNameQuery + " FIELD Name = """ + item.Values(n) + """ OR "
n = n + 1
End Forall
'An extra 'OR' gets appended - strip the last OR
cNameQuery = Left(cNameQuery, Len(cNameQuery) - 3)
End If
'-------------------------
'5. Date Criteria
If uidoc.FieldGetText("DueDat
If uidoc.FieldGetText("DateCr
cDateQuery = "FIELD PRDes_strt_dt >= " + uidoc.FieldGetText("Date1T
Else
If uidoc.FieldGetText("DateCr
cDateQuery = "FIELD PRDes_strt_dt = " + uidoc.FieldGetText("Date1T
Else
If uidoc.FieldGetText("DateCr
cDateQuery = "FIELD PRDes_strt_dt > " + uidoc.FieldGetText("Date1T
Else
cDateQuery = ""
End If
End If
End If
NoDateSelect:
'-------------------------
'Build the query. Append parts using AND but ignore if they are empty.
cFTQuery = "( " + cFormQuery + ")"
If cAccntQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cAccntQuery + ")"
End If
If csubsysQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + csubsysQuery + ")"
End If
If cjobQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cjobQuery + ")"
End If
If cSuptypQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cSuptypQuery + ")"
End If
If cNameQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cNameQuery + ")"
End If
If cDateQuery <> "" Then
cFTQuery = cFTQuery + " AND (" + cDateQuery + ")"
End If
'Display query
If uidoc.EditMode Then
Call uidoc.FieldSetText("RPT_FT
End If
'Run query against the database
Print "Searching..."
Set dc = db.FTSearch(cFTQuery,0)
'Create the Report document and choose the appropriate Reporting form
Set resultsDoc = db.CreateDocument
resultsDoc.Form = "Search Results for Escalation Lookup Query"
'Create the Rich Text Item. This is where the report goes. Also create a
' Rich Text Style object so that we can format the report
Dim richText As New NotesRichTextItem(resultsD
Set richStyle = session.CreateRichTextStyl
'Basic report body text format
richStyle.NotesFont = FONT_HELV
richStyle.FontSize = 8
richStyle.Bold = False
Call richText.AppendStyle(richS
'Store the query title, query, Author and no. of docs. found, for latter reference
resultsDoc.RPT_QueryTitle = uidoc.FieldGetText("RPT_Qu
resultsDoc.RPT_Author = uidoc.FieldGetText("From")
resultsDoc.RES_Query = cFTQuery
resultsDoc.RES_NoOfDocs = dc.Count
'Start generating the report. The document collection 'dc' contains
' the documents fetched by the query.
For n = 1 To dc.Count
Set doc = dc.GetNthDocument(n)
Call richText.AddNewLine(1)
Call richText.AppendText(Cstr(n
'Add doc-link
Call richText.AddTab(1)
Call richText.AppendDocLink(doc
Call richText.AddTab(1)
'UserName & Dates in BOLD type
richStyle.Bold = True
Call richText.AppendStyle(richS
Call richText.AppendText(doc.Ge
Call richText.AddTab(1)
Call richText.AppendText(Dateva
Call richText.AppendText(Dateva
Call richText.AddTab(1)
'Subsystem
richStyle.Bold = False
Call richText.AppendStyle(richS
Call richText.AddTab(1)
Call richText.AppendText(doc.Ge
'View like reports
Call doc.PutInFolder( "Search Results")
'Update status bar
nPercentDone = (n * 100)/dc.Count
Print "Percent done : " + Cstr(nPercentDone) + "%"
Next
'Save results document
Call resultsDoc.Save(True, False)
Msgbox Cstr(dc.Count) + " documents match your search criteria" + Chr$(10) + _
"Click 'Ok' to display the Report.", 64, "Search results"
'Display results document
Set uiResults = ws.EditDocument( False, resultsDoc, True)
End Sub
Well well well... May I suggest some code changes? The code is probably more or less correct, but you can optimize a lot, using fairly standard array operations and the powerful Join() function. The idea is to create a string array and join them, separated by OR-s. But that's besides the point right now. The resulting string you can then put in a different string array, that you join later on with AND-s. A real programmer would create an FTQuery class in LotusScript... ;-)
I think your query 1) lacks some necessary parentheses and 2) should work a lot better when you use [fieldx] instead of FIELD fieldx (don't ask me why, but I often found that the keyword FIELD doesn't work). Could you show a complete query string, e.g. the one that came back with 0 results?
I think your query 1) lacks some necessary parentheses and 2) should work a lot better when you use [fieldx] instead of FIELD fieldx (don't ask me why, but I often found that the keyword FIELD doesn't work). Could you show a complete query string, e.g. the one that came back with 0 results?
ASKER
lol I do not pretend to be a lotus programmer. It's not on my resume as such, even though I'm the only one in my division that has any hobby experience with Lotus Notes development. I've gotta be honest, I don't have much script experience. I got this query from an old database and forum and I have modified almost all of it. but the structure was in place.
I'm not sure why I keep getting 0 results when I see data in a view that represents that form that would indicate I should see data in the results.
If I do a simple query run allowing all the inputs to say ALL. the query just says : Query : ( FIELD Form = PRDescalate)
No. of documents found : 0
still checking things...
I'm not sure why I keep getting 0 results when I see data in a view that represents that form that would indicate I should see data in the results.
If I do a simple query run allowing all the inputs to say ALL. the query just says : Query : ( FIELD Form = PRDescalate)
No. of documents found : 0
still checking things...
Silly question: is your database full-text indexed?
Less silly: can you do a search in the Search bar in Notes (View/Search This View), using this query:
[Form] = PRDescalate
Last question: did you try the LotusScript debugger ?
Less silly: can you do a search in the Search bar in Notes (View/Search This View), using this query:
[Form] = PRDescalate
Last question: did you try the LotusScript debugger ?
ASKER
yes the database is full-text indexed
interesting. When I pull up that view and search this view using the basic query: [Form] = PRDescalate the rows that I originally see in the view go away and the view now shows nothing.
I was using the debugger, yes. The debugger didn't give me any errors and I'm now trying to validate the paths that the logic is taking. But this latest search using view has given different insight as to where to check. I still have the date problem to fix but this is also the big problem of 0 results.
interesting. When I pull up that view and search this view using the basic query: [Form] = PRDescalate the rows that I originally see in the view go away and the view now shows nothing.
I was using the debugger, yes. The debugger didn't give me any errors and I'm now trying to validate the paths that the logic is taking. But this latest search using view has given different insight as to where to check. I still have the date problem to fix but this is also the big problem of 0 results.
ASKER
I'm still unable to get any results. Frustrating... the query script all looks correct. The based query if I provide no filters just the form name even comes back with nothing. And I know the form name is correct. any ideas ?
Darn... What now? I'd love to have a copy of your database, or some hands-on action, because I'm convinced it's just one or two parameters that need to be corrected. What can we do? Is there an EE-version of TeamViewer or Skype? You can contact me if you want, my address is in my EE profile.
ASKER
I have stripped down a copy of the design template to minimize it's size. I can send that to you. It contains the main form and the escalation search form that has a button containing the search query. If I have an email address I can send it to you via that.
thx.
thx.
Oh, please do! My mail address is in my EE profile.
ASKER
I'm not seeing an email address.
That's strange... Someone must have removed it, maybe for a good reason (was I violating some rule, again??). Anyway, I put it back in, in the middle of the About section.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The subsystem field in the escalate form is using a formula. I setup the cross reference fields in the search form to utilize views that contain a trimmed list of entered values . This would allow the selection of the search criteria based upon the subsystems that have entries in the escalate form versus the complete list of subsystems. You think it better to use the complete list like it is in the escalate form ?
I've corrected the jobqual query. I don't see any problem in the name query.
I'm thinking I need to revamp the date criteria. Thinking more about it. I wouldn't request a range query. I would want to select a resource based upon a particular date. I would never get a match on a range unless it is exactly falling within start and end date and I don't see that as valueable. I would think just selecting a resource by a particular date and then have the query check to see if that date is after start date and before end date. What do you think ??
Thanks Sjef.
I've corrected the jobqual query. I don't see any problem in the name query.
I'm thinking I need to revamp the date criteria. Thinking more about it. I wouldn't request a range query. I would want to select a resource based upon a particular date. I would never get a match on a range unless it is exactly falling within start and end date and I don't see that as valueable. I would think just selecting a resource by a particular date and then have the query check to see if that date is after start date and before end date. What do you think ??
Thanks Sjef.
ASKER
EUREKA !!!
I recreated the index. made some minor tweaks and I've got the query working.
I'm now going to working on changing some of the business about the layout but the query logic is working.
Thank You !!
Do you recommend any additional changes ??
Thx. Sjef
I recreated the index. made some minor tweaks and I've got the query working.
I'm now going to working on changing some of the business about the layout but the query logic is working.
Thank You !!
Do you recommend any additional changes ??
Thx. Sjef
ASKER
Thank You for all your continued Support.
You are an Excellent resource and friend.
Very Much Appreciated.
Thanks Again !!
Paul
You are an Excellent resource and friend.
Very Much Appreciated.
Thanks Again !!
Paul
AAAAAAAAAHHHHH, I love that! Congrats!
No, no suggested changes, let's be pragmatic: it works, so why change it. ;-)
If, one day, you have a lot of spare time and you're interested in a different approach, ask a new question or contact me. If I come across a similar problem one day, I'll develop something like the following:
See you next problem :-)
No, no suggested changes, let's be pragmatic: it works, so why change it. ;-)
If, one day, you have a lot of spare time and you're interested in a different approach, ask a new question or contact me. If I come across a similar problem one day, I'll develop something like the following:
Class FTQuery
Sub AddOrCondition(c As String)
...
End Sub
Sub AddOrQuery(a As FTQuery)
...
End Sub
Sub AddAndCondition(c As String)
...
End Sub
Sub AddAndQuery(a As FTQuery)
...
End Sub
Function GetQuery() As String
...
End Function
End Class
The idea would be to create one top-level FTQuery object and add strings and sub-queries where you need them. The GetQuery function is supposed to return a correct full-text query string, i.e. with all the necessary parentheses. No need to do this, it's a academic problem, art for art's sake. Nevertheless, very interesting to a developer!See you next problem :-)
ASKER
lol..... very well said. Thanks Sjef.... Its a pleasure.
If you want to create an escalation list of your Lotus Notes Contacts or names then the possible way is to export your Lotus Notes contacts or email to Microsoft Excel then follow these easy steps:
Steps to Exports Lotus Notes Contacts:
1. Open Lotus Notes
2. Click on Home Tab
3. Click on Contacts Button
4. Now click on File tab then click on Export Contacts
5. Choose your directory where you want to save the file; just below that you will see “File Name”, fill the file in the box.
6. Then choose Comma Separated Value (.csv) format in Save as Type.
The easiest way to export
7. Then click on Export button.
To import the file into Microsoft Excel format:
1. Open Microsoft Excel
2. Click on File tab then click “open” (you can use ctrl+o as shortcut)
3. In drop down menu choose "All Files"
4. Browse you .csv file and click on open.
5. Here you will see your contacts with their emails. Now you can edit any contact or you can remove extra cells.
Hope my answer can help you. If you need any other help you can reply.
Thanks
Edwin J Hopper