Solved

Form Queries

Posted on 2002-05-13
24
356 Views
Last Modified: 2013-12-18
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
Comment
Question by:dbusher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 3
  • +4
24 Comments
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7007346
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7007349
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7007353
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
Independent Software Vendors: 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!

 
LVL 9

Expert Comment

by:Arunkumar
ID: 7007356
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7007359
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
 
LVL 1

Expert Comment

by:pcorreya
ID: 7007877
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7008024
Welcome Pat!

Big pts huh ?

:-P
Arun
PS: Just got up and a little big of hangover hee hee hee...
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7008097
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7008184
Grrr jerrith...you have compiled all my comments huh ?
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7008271
Yep, compiled all in one brief comment :-)
0
 

Expert Comment

by:ekommineni
ID: 7008808
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
 
LVL 2

Author Comment

by:dbusher
ID: 7008985
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
 
LVL 2

Author Comment

by:dbusher
ID: 7009484
I created an Agent that runs manually that searches the db. I will keep playing.
0
 
LVL 1

Expert Comment

by:pcorreya
ID: 7009920
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7009942
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
 
LVL 2

Author Comment

by:dbusher
ID: 7010110
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
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 7010284
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7010562
You have not answered if its a web or a notes requirement!
0
 
LVL 2

Author Comment

by:dbusher
ID: 7012006
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
 
LVL 10

Expert Comment

by:zvonko
ID: 7012538
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
 
LVL 9

Expert Comment

by:Arunkumar
ID: 7012540
hi bro email me now got questions for u about Groove !!!
0
 
LVL 10

Expert Comment

by:zvonko
ID: 7038923
Hello DPB,

any progress here?
Do you need more support?

0
 
LVL 2

Author Comment

by:dbusher
ID: 7041838
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
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 7045031
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question