Solved

Form Queries

Posted on 2002-05-13
24
351 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now