Solved

Form Queries

Posted on 2002-05-13
24
350 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Grrr jerrith...you have compiled all my comments huh ?
0
 
LVL 8

Expert Comment

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

Expert Comment

by:ekommineni
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Author Comment

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

Expert Comment

by:pcorreya
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You have not answered if its a web or a notes requirement!
0
 
LVL 2

Author Comment

by:dbusher
Comment Utility
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
Comment Utility
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
Comment Utility
hi bro email me now got questions for u about Groove !!!
0
 
LVL 10

Expert Comment

by:zvonko
Comment Utility
Hello DPB,

any progress here?
Do you need more support?

0
 
LVL 2

Author Comment

by:dbusher
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

8 Experts available now in Live!

Get 1:1 Help Now