Form Queries

Warning : I am a newby!

I created a form that is accessable via our corp intranet. When the user fills it out and clicks the SUBMIT button it is posted into our Notes DB (@Command([FileSave])).

This db currently has about 300 posts to date.

The form has many fields but in particulary there are DATE, SERIAL NUMBER, LEVEL fields.

I want to provide some sort a query option that allows a manager to punch in a starting date and a SN range or wild card (ie. sn= 7*****  to get all sn starting with 7) and generate a report that looks something like this:

SN: 732344    DATE: 04/02/2002  LEVEL:  
SN: 732345    DATE: 04/03/2002  LEVEL:
SN: 732346    DATE: 04/05/2002  LEVEL:  

I am very new to all this so as much detail and hand holding would be greatly appreciated and rewarded.  
Who is Participating?
MindphaserConnect With a Mentor Commented:
Points refunded and moved to PAQ.

dbusher, instead of deleting this question, I refunded your points and keep the question in our Previuosly Asked Questions area so that others can prifot from these comments.

** Mindphaser - Community Support Moderator **

I guess you can start with building a nice view....

Sort the view by Date on the first column...
The second column by the Serial Number...
The third column by level... etc...


Once you build few views sorting the first column based on the date or the SN or Level...

you could use the simple quick search to find your document.  Quick search could be made by focussing the view and start typing the first few characters of the first column.
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

If you want to complicate...then you can create a folder,
And write a lotusscript to find the documents that match your search criteria and fill the folder with similar documents.

Designing a folder is just like designing a view except for the view selection is not available here.
Interestingly you can use the database search itself if you have indexed the db.

Now, Just design some junk view, and hit Cntrl + F.

You will get a search dialog.  Type in few characters of any field and the closest matching document will be on top of the view filtered.  From here you could drill down whateer document you want.
Haa Haa Haa !!!

I was so very crazy typing so many messages. But did not ask if the query is going to be on WEB or Notes.

If its going to be on WEB then you could design a simple WebSearch form and use it too.

Let us know if we could be of any use Okay ?

I am boozed heavily now..and i dont think i can get back to this question until tomorrow. By then you might get expert answers for sure !

Good Luck!

I do agree with Arunkumar that you can use the
search function , however here is another approach.

You can write a simple agent that will accept the
parameters you want and pump out the report to the
web. You can also redirect output to excel if you required.
If this is what you are after and need further help let
me know.

Welcome Pat!

Big pts huh ?

PS: Just got up and a little big of hangover hee hee hee...
Jean Marie GeeraertsApplication EngineerCommented:
Is this for web of client use ?
I develop all my applications for web, so I could easily provide you with a step by step procedure to create a search form and output the results to a web page.

(It's equally easy in the client).

Just let us know what exactly it is you want to do.
Grrr have compiled all my comments huh ?
Jean Marie GeeraertsApplication EngineerCommented:
Yep, compiled all in one brief comment :-)
The following is an example format we used to run web based queres:

Create a form that has filed(s) to input "Serial Number" or what ever fields that you want to query, with a submit button.  When user clicks on the button pass the search information and create a results document that will display the results in what ever format you design in the results form.

Hope this helps.
dbusherAuthor Commented:
Pat,   I think what you recommend is what we are after. It would be nice to get the info onto the web but also into a spreadsheet.  Eventually, we want to manipulate this information such as:

LEVEL :   TOTAL: 10  etc....

I am not familiar with agents.  I will read up on these.
Can you tell me what the code in the Agent would look like to generate the report I mentioned in my earlier post?

dbusherAuthor Commented:
I created an Agent that runs manually that searches the db. I will keep playing.

If you have a view with the first column as the date and second column as serial number. You can then use the getdocumentbykey method of the view to seek out the starting point and process the documents in a loop. If you need further help let me know.

Yea dbusher.  Keep playing...once you get the solution award the points to me.  Because the suggestion was mine right ?


:-P for points!

dbusherAuthor Commented:
Arunkumar,   I am still playing but my original question still seems to be unanswered. :

I want to provide some sort a query option that allows a manager to punch in a starting date and a SN
range or wild card (ie. sn= 7*****  to get all sn starting with 7) and generate a report
Jean Marie GeeraertsApplication EngineerCommented:
So this manager, would click a button in the view, enter a starting date and SN range (700000-702000) or wild card (7?????) click OK and the report would be generated to web/client and to a file?

For web, I would create a search form in which the manager can enter the required search criteria and then hit the Search-button.
This search-button would then execute an agent that uses the parameters in the document to perform the search and output the data to a resource on the server and to a web page.

I'll try to wip up your code later on today. (Takes a bit of work, not too much, but still some work)
You have not answered if its a web or a notes requirement!
dbusherAuthor Commented:
Arunkumar,  see my response to Pat above.  To the web, file, or doesnt matter that much. I suppose via web would be prefered.

Jerrith,  Thank you! I am eager to get this project out of my way.  

All,  everyone has been so helpful I will certainly deplete all my QPs when done.  cheers
Hello dbusher,

here my proposal.

Make a query form.
On this form are at least a field: SerialNumber
And a button: Search
Behind the button is formula:

And here the agent code for this "SearchSNdocs" agent:
Sub Initialize
  Dim session As New NotesSession
  Dim db As NotesDatabase
  Dim view As NotesView
  Dim doc As NotesDocument
  Dim SN As String
  On Error Goto errormessage
  Set db = session.CurrentDatabase
  Set doc = session.DocumentContext
  SN = doc.SerialNumber(0)
  Print "Content-Type:application/"
  Print ""
  Print "<FONT FACE='Arial'  SIZE=2>"
  Print "<TR VALIGN=top>"
  Print "<TH NOWRAP ALIGN=left><B>SN#</B></TH><TH>Date</TH><TH>Level</TH>"
  Print "</TR>"
  Set view = db.GetView( "SNview" )
  Set doc = view.GetFirstDocument
  While Not ( doc Is Nothing )
    If (Instr(1,doc.SerialNumber(0),SN) = 1) Then
      Print "<TR>"
      Print "<TD>" & doc.SerialNumber(0) & "</TD> "
      Print "<TD>" & doc.Date(0) & "</TD> "
      Print "<TD>" & doc.Level(0) & "</TD> "
      Print "</TR>"
    End If
    Set doc = view.GetNextDocument(doc)
  Print "</TABLE>"
  Print "</FONT>"
  Exit Sub
  Print "<br>Error: " & Error$
  Print "<br>Error in line: " & Erl
End Sub

Do some tests and tell me what improvements you like to have.

So long,

hi bro email me now got questions for u about Groove !!!
Hello DPB,

any progress here?
Do you need more support?

dbusherAuthor Commented:
Thanks everyone for your help. We found a solution based on your input.  Reviewing the posts it appears as though the following contributers were key:
Arukumar - for initial DB view ideas
Pcorreya - for initial agent ideas
Zvonko - for great source code

I will be deleted this question and posting a 100 pnt post for each of you.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.