• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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: 2.23.22.9  
SN: 732345    DATE: 04/03/2002  LEVEL: 2.23.22.2
SN: 732346    DATE: 04/05/2002  LEVEL: 2.16.22.0  
etc....

I am very new to all this so as much detail and hand holding would be greatly appreciated and rewarded.  
cheers
DPB  
0
dbusher
Asked:
dbusher
  • 10
  • 5
  • 3
  • +4
1 Solution
 
ArunkumarCommented:
Well....

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...

-Arun.

0
 
ArunkumarCommented:
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.
0
 
ArunkumarCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ArunkumarCommented:
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.
0
 
ArunkumarCommented:
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!
Arun.
0
 
pcorreyaCommented:
dbusher


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.

Thanks
Pat
0
 
ArunkumarCommented:
Welcome Pat!

Big pts huh ?

:-P
Arun
PS: Just got up and a little big of hangover hee hee hee...
0
 
Jean Marie GeeraertsCommented:
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.
0
 
ArunkumarCommented:
Grrr jerrith...you have compiled all my comments huh ?
0
 
Jean Marie GeeraertsCommented:
Yep, compiled all in one brief comment :-)
0
 
ekommineniCommented:
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.
0
 
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 : 2.23.28.0   TOTAL: 54
LEVEL : 2.33.90.0   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?

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

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.

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

LOL !

:-P for points!

-Arun
0
 
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
0
 
Jean Marie GeeraertsCommented:
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)
0
 
ArunkumarCommented:
You have not answered if its a web or a notes requirement!
0
 
dbusherAuthor Commented:
Arunkumar,  see my response to Pat above.  To the web, file, or client..it 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
0
 
zvonkoCommented:
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:
@Command([ToolsRunMacro];"SearchSNdocs")

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/vnd.ms-excel"
  Print ""
  Print "<FONT FACE='Arial'  SIZE=2>"
  Print "<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>"
  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)
  Wend
  Print "</TABLE>"
  Print "</FONT>"
  Exit Sub
errormessage:
  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,
zvonko

0
 
ArunkumarCommented:
hi bro email me now got questions for u about Groove !!!
0
 
zvonkoCommented:
Hello DPB,

any progress here?
Do you need more support?

0
 
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.
0
 
MindphaserCommented:
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 **
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 10
  • 5
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now