Local Database where a form is sent to several notes users - how to get data reports from the email data

I'm not quite sure I understand it all. We'll see...

I'm not very much in favour of sending documents and forms to lots of people. I assume you did the following:
- one central database with forms and documents
- no document and/or form are sent to lots of people, instead, only a doclink is sent

Now you say
> ... I will get emails from everyone that goes to this one folder in notes ...
> ... build a view within notes to 'show' all of these documents ...
No comprendo...

Hi Sjef,
One database that will be sent to everyone.
The database will be stored locally on the users desktop, and when they submit the form it automatically sends a note to myself and a couple of other people.  The contents of which is a doclink.  

Is there any way I can get at the data, ie field contents by doing this?  (Because its a doclink, will this only reside on the users cworkstation?  I have included the form as stored in the document?)

If not I have seen it could be viable to change the submit button to export the file as a csv or ascii file.  Therefore I would just receive the field data as text.

I am unsure on how to do this if this is a better way to rebuild a view on my client to show the data.  Please could you advise how this would work and how to set this up if this is a better solution?

Sorry for not being too clear as I am completely new to domino designer.  

Thanks.

One database? Sent to everyone? So they all have the same local replica? No replica on the server, because THEY don't want that?

Please don't go there, too difficult to do this in Notes and Domino. It is not even in the spirit of Notes to do this the way you describe... :(

Regarding "company restrictions": please read my EE-profile.

The best approach is to ask you boss, and his boss, and his one (or her) to back you up. I can understand that THEY want ultimate control, but THEY could also allow some initiatives to the ordinary human beings. THEY might even help you, or do the project for you!

If you insist to go on, we'll help you of course.

The documents you receive come by email? So you just have to move them from your mail database to the other database? A rule can do that for you.
Then you should have an agent in your database that checks for duplicates, and does the same as a domino server would do for you during the replication process: newer fields are put into the existing document. Finally, you can delete the old document.

I don't want to copy the replication algorithm. Do you?

Hmmmm, Really confused now.

The database itself only really stores the form for the users to fill out and submit.

Once this has been submitted, I receive an email with 'hopefully' some data from the fields that I can work on to create reports etc within a spreadsheet or a view.

Surely there must be a way to do this?

Reading about (but not fully understanding) COL and CSV files, there must be a way to export the field values on the form to my email so that I can place them into a spreadsheet ?????

Please could anyone offer more advice on this.  Any help gratefully received.  Thanks for your reply Sjef.

Every email I get sent will hold the field data, and this will need to be appended to the bottom row of a spreadsheet, hence no duplication.  The form is for ordering taxi's, therefore the boss wants to know who ordered the taxi, when, why, etc.  These fields contain this info, hence it would just be appending each email data to an existing spreadsheet.
Therefore as soon as I receive an email in the "submitted taxi form" folder, Notes automatically takes the data from the email and adds it to the bottom row of the table/spreadsheet.

The spreadsheet will then contain all the data for me to work on, ie creating reports etc.

I will be leaving work shortly, so I won't be able to look at this post until tomorrow.
I know nothing about LotusScript, therefore any @commands that I can put in place to automate this would be great.
I would like to 'learn' to do this as I go along, so any references to @functions, commands etc would be really useful.

Thanks for your help Sjef - Much appreciated.

Graham

Al that you need to do is to create a view showing the fomr fields that you want to export.
In the view formula set the select statement to the Form name you are using.

If this does not help then you will need to filter based on the folder where they are located.

You can then export easily from the view to CSV etc.

I hope this helps !

Let me rephrase this, to see if I got it now...

- you have one database, with one form, no documents and no views (virtually)
- the database will never contain any documents
- everything is handled by mail
- the usual process:
    - a user opens the database and the form,
    - enters data,
    - and clicks on the Send button;
    - document with form is mailed to internal Notes users;
    - each user opens the mail
    - and then... your problem starts
    - the receiver is supposed to do something with the data received

Contrary to my first statement, I think it is doable. You need (at least) two buttons on the form, one to Send the document and one to Process the data when received. If you put them in the form, the receiver will also have those buttons. The Send button must be hidden for the receiver, which is no problem (based on @IsNewDoc).

The Process button assumes that processing is identical for all users. That's the main question, for when all users do something different with the data you're ... eh... in trouble. The only thing you could do then is create a that is identical csv-file for all users. For that, you don't need the process button, nor the form to be sent, but you could already create the csv-file when generating the mail document.

How 'bout this?

WHat I normally do for something like this is set up a Mail-IN DB on the server, and send all the info to it, rather than my personal mail.

ALso I make sure that the form name is not memo, so that I can distinuish between that and regular mail.

I hope this helps !

If I understand gray180 correctly, he is not allowed to do anything on the server.

Well, it isnot clear.

He was not allowed to create a DB with a Form users could access.  A restricted Mail in Db may be another story.

Hi Sjef,
That's exactly what I want to do, but due to my lack of knowledge I'm unsure how to do it.

I have everything set up on the form except the processing button to handle the data.

I will probably get around 50 emails a day from users submitting these forms, hence I would like, somehow, to automate this process within the Notes Client.  I already have the email going into a certain folder, so what I would like to do is: -

Any new email received in the folder,

1.  Transfer the data held in the fields to a spreadsheet held locally on my workspace.
2.  Once done, auto archive the mail that has just been used to do step 1.

If I could do this using @commands and not lotusscript I am willing to have a pop at it, but I will need some direction please.  Thanks for your help Sjef. Any commands you could pass on would be really useful.

PS Sysexpert.  My first line did say that I couldn't use a server to work from.  Thanks anyway.

Thanks,
Graham

If you want to handle this automatically, you need a background agent. The folder can be used to find the mails that should be handled. When a mail is done with, the mail could be moved to a different folder, hence preventing it being processed twice. That's all not such a big problem.

The problem is in the last few lines of my earlier post:
> The Process button assumes that processing is identical for all users...
Will all users use the same agent? What will they do with the data in the form?

There will only be 2 other users including myself (3 in all) - the final goal will be an identical spreadsheet.  The data in the form will be transferred into a spreadsheet.  The reason for this is if I am on vacation or ill etc.  I know its not pretty but is a working solution to get around the server problem.  Therefore the processes will be identical for the 3 users who get the same email with the form inside it.  

So, the end product will be 3 identical processes happening on each of the 3 users workstations to produce an identical spreadsheet.

Ah! Brilliant. So one agent can do it all.

Next topic: assuming we can guide you how to extract the values from these document, will you then be able to write code to put them into the spreadsheet? Are the places fixed in the spreadsheet? It is also possible to manipulate the spreadsheet from within Notes, using COM. You'd need some thorough knowledge of VBA for that.

I have no VBA knowledge but am willing to learn!

Plus I don't know how to extract the values from the documents in the first place.  Like you say, hopefully an agent can do this process.

I've looked at the exporting to ascii but not sure about the delimiter files etc.

Apologies for the lack of knowledge!  This is why I joined EE to help my learning.

I have no spreadsheet set up as yet but it will just have column headings and the data from the forms.
Hopefully, each row will contain the field data from 1 form.

Willing to learn, eh? When does this project of yours have to be finished? I can point you at a lot of examples here at EE, that explain very well how to combine Notes and Excel. As you might (not?) have realised, EE is not a rent-a-coder site, lots of coding is "not done". Instead, the general approach is: you code, we fix.

In this case, I'll give you an example of an agent that opens all documents in a view, reads some values and puts them in a file.
    http:Q_21497579.html "Create a file based on current date and then attach it to a database"
    http:Q_11020721.html "Exporting Field Values from Form"

And just to think that you could have been ready with this with just one simple database on the server... Seems rather pathetic :-S.

I will look into it.  

I will just address these points: -

I did not know this wasnt a rent a coder site as a lot of the info on here for some users seem to have coding on them - When I first viewed this site I couldnt find a solution to the problem so i paid for a 6 month subscription.  Also, I wouldnt want anyone to do it for me as I wouldnt learn anything that way.

I want to learn as much as i can, not just 'find' a solution.

Your last remark sounds out of turn.  For what I can receive on here I dont think that paying for a subscription is anytihng different to what I can get elsewhere for paying nothing.

Thanks for your help.

I'm sorry, I didn't want to offend anybody here. I do want to help, but you have to do the bulk of the thinking and programming. It's more a "please, by all means, start developing!" so we'll have a solution sooner or later. I tried to look up existing examples for you, but I couldn't find any. As you might know, most experts at EE are happy to supply their help for free. It's the idea of community, sharing ideas, some competition, receiving points for good solutions. The only perk one can earn is the right to search the database and ask questions for free.

And yes, you're right, I probably shouldn't have made that remark, but I'm not criticizing you! The whole application seems such a detour. I'm very amazed that your company is not willing to help you in improving the quality of work for you and your co-workers.

Anyway, I hope the links I gave will provide the examples you need to build your own form. If you get stuck somewhere, just post a question in here...

No problem Sjef,
I'm on my placement year and know nothing about Lotusscript etc, but I want to find these things out.  There seems to be a lot of code everywhere where i could use but I wouldnt understand, hence joining this site.  It will also be useful for other subjects.

I will look into the links and get back to you probably tomorrow now - my heads fried!

Thanks for your help throughout anyway.  No doubt I will need more.........

http:Q_21497579.html "Create a file based on current date and then attach it to a database" - jeez whats that all about!  I think I need to learn Lotusscript !!!!!  

The other sample link is really useful though.  

I will take a look again in the morning to see if I can get something up and running.....

Just one well-meant piece of advice: first, get your logic right, then start coding...

I'm giving up on this as its a major headache.  I don't understand any of the code from the above examples.

Thanks for your help in trying to point me to 'similar' questions but I dont understand it so its pointless carrying this on due to not knowing the Lotusscript language.

Thanks
Graham

Oh dear... I'm sorry to hear that... :|

I assume you'd still want to have your problem solved. Are there other ways in your company you might get what you want? Although there are the restrictions as you indicated, I assume there is some way to get good ideas executed. Isn't it possible to do some study and write a proposition for your boss ('s boss ('s boss...))? Address also the cost/benefit issue: it might take them 16 hours to implement your request, and if it saves you and your colleagues 1 hour per week the ROI is 16 weeks.

A new day a new start as they say!

I can't give up on this now so here goes.

I have the data from the fields into a text file using Lotusscript which Sjef pointed me to.
I can just about figure out whats going on there, so I think its best I take small steps at a time as it may help me learn a little bit easier.

The text document I get is just what I want to work with, as I can then try to figure out how to automate the transfer of this data into a spreadsheet.

So, first things first.

Previously I had a button on the form using the following: -

@MailSend("email1":"email2":"email3";"";"";"SUBMITTED TAXI FORM";"";"";[IncludeDoclink]);
@Command(SaveOptions=1);
@Command([FileSave]);
@Command([FileCloseWindow])

This sent an email to the 3 members of staff it should go to with the heading "submitted taxi form".
I set up notes to automatically filter the subject "submitted taxi form" into a designated folder.
Then I could go to this folder and open up the note and the doclink to view the form.

So, now I need to know how to transfer the @mailsend("..........") command into Lotusscript ?

Will I need to write a separate function for this and where do I put it.
Will the command go in the same function as I've put on the button to create the .txt file to get the data from the fields?

I think I will need to use the following but don't understand certain parts: -
Call notesDocument.Send( attachForm [, recipients ] )

Eg, attachForm and recipients - will I need to put the values in these fields, or do I declare them and then reference them?  
Also how do I close the window once the button is pressed using LS?  Again, found this in the domino help: -
Call notesUIView.Close

An example of this would be really useful if you can help in any way.  I will keep percevering in the mean time.

Thanks!
Graham

I have worked out the close window part of the script now.
I'm now embarassed to have asked that now!!!!!!!!

May be able to work out the send mail now :)

But any advice would be welcome in the mean time just in case it doesnt work!

There are 3 issues, as far as I can see:
1) the Send button
2) handling incoming Taxi forms (towards the folder)
3) handling the incoming data

Let's do this step by step, I'm easily confused ;)

When you send the mail, you send a doclink. The document is apparently stored in your local database. The receiver of the mail will not have access to that local database. A doclink is pointless therefore, I'd say.
You could send out the current form and document, by using @MailSend without parameters. With code like this:
    Field SaveOptions:= "1";
    Field SendTo:= "email1":"email2":"email3";
    Field Subject:= "SUBMITTED TAXI FORM";
    @MailSend;
    @Command([FileSave]);
    @Command([FileCloseWindow])
The receiving end would then have both the form and the document with values. No need to save the data in an external file yet.

Is this an idea for issue 1?

Yes that's what I had as the button property, but now I have added the LScript to that button now to transfer the field data to a text file.  Can I somehow invoke a call to the LScript function once the button is pressed - or add the email properties for sending mail to the script below?

Eg; (The script to save the txt file)
Sub Click(Source As Button)
      Dim workspace As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Set uidoc = workspace.CurrentDocument
      Dim doc As NotesDocument
      Set doc = uidoc.Document
      
      Dim FileName As String
      Dim FileNum As Integer
      FileNum = Freefile()
      FileName = "c:\temp\doc.txt"
      Open FileName  For Output As FileNum
      
      Forall item In doc.Items
            Print #FileNum, "Item " & item.Name & "  =   " & item.Text
      End Forall
      
      Close #FileNum
      Call uidoc.FieldSetText("SendTo","email1")
      Call uidoc.Send
      Call uidoc.Close
End Sub

Call uidoc.FieldSetText("SendTo","email1")      
I am trying to use the above line to set the properties of the SendTo field and then use 'Call uidoc.Send' to send to the email address.

So, what I need to fathom out at the moment is to do this

Call uidoc.FieldSetText("SendTo","email1":"email2":"email3")  //so that it populates SendTo with the 3 email addresses
      Call uidoc.Send  // Send the completed form to these email addresses

Also, I would like to send the C:\temp\doc.txt file that the function creates to these addresses too so that I can progress to Stage 2.

Apologies for the lack of knowledge with the classes and method properties.

Sjef, do you know of any good Lotusscript books that would teach me the basics????!!!!!

Thanks for your continued support.

Excellent Sjef!!!!!

Just what I was after.

In the following piece of code:-

Forall item In doc.Items
    Print #FileNum, "Item " & item.Name & "  =   " & item.Text
End Forall

could I possibly 'print' only relevant fields that I need for stage 2, for example: -
getting rid of the For all statment and trying to print the values to the text file for fields, "NAME", "REFERENCE", etc:

Print #FileNum, "Item " & "NAME" & "  =   " & NAME
Print #FileNum, "Item " & "REFERENCE" & "  =   " & REFERENCE

Then for stage 2 I can try to work out how to get an agent to select these fields I want to produce the spreadsheet on.

PS - This will answer stage 1 of the question, therefore I will accept this as an aswer if I can get this to work.

I will write another question for Stage 2 elsewhere.

Thanks

Of course you can! :) What you have to get familiar with are the methods of NotesDocument and some shorthand:
- doc.GetItemValue("SomeField") will get you an ARRAY of values from the field SomeField in the document
- the shorthand version is doc.SomeField that also produces an array
- arrays can as usual be indexed using (i), so doc.GetItemValue("SomeField")(0) returns the first element of the field
- in shorthand again: doc.SomeField(0)
- so even for fields with only a single value you need to use (0)
- to put values in a field on the document, use doc.ReplaceItemValue("SomeField", value)
- in shorthand: doc.SomeField= value
- this value can be a scalar value, a string, or an array of scalars or strings

The Forall you can replace by
    Print #FileNum, "NAME=" & doc.NAME(0)
    Print #FileNum, "REFERENCE=" & doc.REFERENCE(0)

Superb Sjef !!!!!!

That's what I wanted to do.  Also thanks for the help throughout. It's frustrating for me not knowing much about using LotusScript but I have learnt a lot, especially the last couple of parts you submitted, so thankyou alot.

I still have a fair way to go to get it doing what I want but this is a major starting point.
I will no doubt be posting other questions for the other stages early next week.
This will mostly be to do with automating an agent to get the data for the doc.txt file.
Once this has been done I can look at using the agent to transfer it into an excel spreadsheet.

Would you recommend posting the question from scratch, or continuing to add a question inside this thread? (if thats possible?).

Thanks again for your support and knowledge.

Graham.

Hm, a new question would be better, you'll get help from others as well, so you'll have the solution sooner.

Come to think of it: you don't need the file. You can also put everything in the mail, in the Body, as plain text. The receiving end would just have to read the body of the mail to get the data. For you to think about, homework for the weekend! :-P

Or you try to format the file directly as a CSV file, so you can easily import it in Excel. How to do that programmatically is lesson 87b...

By the way, thanks for the grade!

No problem Sjef - Very thorough answer and I learned a lot from it.

I've had a really busy weekend but I would like to try the CSV way first as it will be really useful for me for the future I think.  Even so, I think the 'body text' would be more relevant, but I will post another question if I start to struggle.  In the mean time I will have a think about it!

I like the quote about lesson 87b ha ha!!!!

Thanks again for all your help.

Graham

Sjef,
Ok you've got me thinking now!

I really would like to add the data to the 'body' of the note instead of attaching it as a file.

This is due to the size of the file and also I feel it may be easier to get at the data when wanting to write an agent to export the data to the spreadsheet.

Any help would be great - and I will give you 200 points (if i know how to give you those points directly)??

I'm assuming to get rid of the 'file' parts of the function and to place the fields in the body, eg: -

doc.body="NAME=" & doc.Name(0)

But that doesnt work - it still sends the form as the body of the note.

Please can you help

Thanks

If you want things in the Body-field, you can do two things:
- create a RichText item, that's the proper way (but don't go there)
- create a String variable, put everything in there, and assign it to doc.Body

Instead of the Body-field, you can easily use any other fieldname. How about FileParts? So use
    Dim s As String
    Dim RT

    RT= Chr$(13) ' just a return character
    s= "Some text" & somefield & RT
    s= s + "Some more text" & morefield & RT
    ...
    doc.FileParts= s

No additional points required. :) Thanks

Superb stuff Sjef.

That looks much better thanks!

It took me a while to assign the FileParts field to the body field.

I put it in as : -

doc.Body=doc.FieldParts

At first I forgot the doc part when assigning it - It works so I presume its correct.

Thanks for your help.

Now I feel i'm in a better position to look at the agent to write to excel.

Your help and explanations have been invaluable so thanks again!!!!
Excellent stuff!