Programming formulas in views 1

[Fret4u]

I have designed a view. One column displays time spent on a specific action in a project en the next column should display the sum of the value (in previous column)from above unto this position; for example. I have spent a week on a project: on monday 2 hours, tuesday 5 hours, wednesday and thurday 3 hours and friday 1 hour. One colum column displays the values 2-5-3-3-1 and the following column should display 2-7-10-13-14. But I don't know with formule to use. Using @SUM(Time_units) only displays the same values (2-5-3-3-1 in my example) and doesn't add up.
Any body any tips on what formula to use ????
By the way, I know I can have totals in a column, but if I select a subsection (for example using search <projectnumber>) the column does'not display the totals anymore.
Thanks anybody (in advance) for your suggestions.

 

[pmonett]

Designing a view in Notes can be rather complicated. You must understand that a View is not a spreadsheet - totals are made by document, then by category.
There is no way of adding the total of a previous category to the next - you will only get the global total at the bottom of the view.

As far as your day totals are concerned, from what I see I think you have put all hours into a multiple-value field. Is that true, or do you have one field per day ?

Pascal.

 

[Fret4u]

Pascal,
The form that I have to fill out has several fields: project, date, timeunits, kind of action, description of action. Project is linked to an other view and retrieves its data from there (thus linking the project with the spent time) Date is a "date" field and shows date+time. Time units is numeric and shows the units in 2 decimals (eg 1,50 or 2,75 etc) kind of action is a text field and description is a rich text field.
Then there is that view (in respect to my problem) that shows data from project and time spent. Column one sorts on form a first and the on form b. The view only selects form a and b (excluding all other forms). Then I have a column that retrieves the description of the project. The next column give the specific time. In the next column (total time) I use the following formula.

@DbColumn("";"";"(Time_units_history)");
@Member("Time";"Time_units_history");
@If(@Member("Time";"Time_units_history")=0;Time_units_history;" " )
"Time" is the column name with the data from the form "History/action"

I found the formula on the internet <

http://www.keysolutions.com/NotesFAQ/howretrieve.html>.

I have tried to tranfer the formula to my purposes and only succeded as far as show above. But the formula on internet displays the subtotals and promisses to do what I am trying to do. But I'm stuck here and maybe you can help me out. Th ecolumn time total only displays the values of the column "time" and does not add the values. In fact I'm trying to display cummulative values.
If you would know a solution or maybe help me further with tranfering the said formula, I would be greatful.

 

[pmonett]

Okay, first point :

"[@DbColumn] does not work in column or selection formulas"

Check in your Designer Help db - I copied this sentence from it. This means that you cannot use @Db formulas in a view.
I checked the link, but I cannot agree with the code presented. @Db formulas have always been forbidden from views as far as I know (I learned Notes on R3). In any case, I am not surprised that your code does not work in a view. In a field, yes, in a column, no.

A view is destined to present some of the data that is stored in a document - one doc at a time. You should be able to do sums, but only on fields inside the document.

Second point : as I said before, you will not get cumulative totals per category. Forget it, it won't work. The only cumul is the grand total at the bottom.
Inside a category, you can have total per branch (or sub-category), that is it.
These are the calculation limitations of views, you must submit to them. If you want to do anything more complicated, export the view to Excel and users play around with it.

I don't mean to be blunt, I'm just stating things as I understand them. The way I see it, you are wasting your time with this @DbColumn thing in the view. Try to modify your process to do the calculation in the document, if possible.

In any case, keep posting your issues in the forum, and keep posting in this thread if you need additional information. My goal is not to put you off your work, it is to understand what you think you need, and how best to give you the information required to choose the best course.

Pascal.

 

[Fret4u]

Pascal...sofar and I guess I just have to accept the limitations of the view. There is one thing to achief what I want to. In the view, is it possible to have the entries categorized on the project number ? Suppose ther are some project: nrs 1, 2, 3 In the first form (form A) I specify the project eg project 1 is a contract, project 2 is programming, projet 3 is advice. The second form (form b) specifies the kind ow work. In the view where the two forms are combined all project and action are displayed, not beeing categorized.
Now I want the view to display as follow:
project 1
action project 1
action project 1
action project 1
project 2
action project 2
action project 2
project 3
action project 3
action project 3
It should not be of any concern at what time an action or project has been created. Because now it displays as follow
project 1
action project 1
project 2
action project 1
action project 2
project 3
action project 2
action project 1
action project 3
action project 1
action project 2
So if I can get them to be grouped on number I can simply select the documents and print the view with its (sub)total.
Thus having printed a report like I wanted to create with the column formula.
Can you help me with that problem ? You might have guessed it but I'm copletely new to this stuff and I don't get any hepl from our domino designer or administrator.

 

[pmonett]

That is easy to solve : you simply need a hidden column that sorts the project by type, inserting an order.

ex: action 1 is given value "1", action 2 value "2", etc.

By having a first column categorized by project, then the hidden column, then the second (visible) column, you would get this :

project 1
action project 1
project 2
action project 1
action project 2
project 3
action project 1
action project 1
action project 2
action project 2
action project 3

The formula in the hidden column would be something like this :
@if(action=type1;1;action=type2;2;3) and sorted by smallest first.

Pascal.

 

[pmonett]

Don't worry about any percieved lack of experience. Designing views is tricky - even experienced developers can get issues that are very hard to resolve in the initial understanding of what they wanted.

And the fact that Notes is unlike any database system that has ever been created only compounds the issue.

Hang in there,

Pascal.

 

[Fret4u]

Sorry...I tried every thing...but I didn't get it to work.
I work with files and they are numbered: eg 3031, 3035, 5001, 5002, 5003 etc.... The actions are linked with these files and in the view the first column displays the file number of the form "files: and of the form "action" with the formula > @If(Form = "Dossier" ; Dossiernummer ;
Form = "History" ; dossier_nr_history ;" "). So oone column displays the values out of two forms. The first column is hidden and sorts on form > @If(Form = "Dossier" ; 1 ; Form = "History" ; 2 ; 3).
The view now displays 3031*, 3035*,5001* , 5002*, 5003*, 3031#, 5001#,5001#,5001#. (* means that this number is retrieved from form "Dossier" and # means , data retrieved from form "History". Mind you that these characters * and # are not shown but only used here to explain the displayed view.
What I want the display to show is the following:

3031*
3031#
3035*
5001*
5001#
5001#
5001#.
5002*
5003*

I do not succed in creating the view like above and I cannot get the view to display the indent.
If it is not enough trouble, please help

 

[pmonett]

Okay, I get the problem.

You need to make your first column sort simply on the number, e.g. :

@If(Form = "Dossier" ; Dossiernummer ;
Form = "History" ; dossier_nr_history ;" ")

That way, your first column will regroup all identical values together. By the way, that column should be hidden.

Your second column is hidden and should sort by form type, e.g. :

@If(Form = "Dossier" ; 1 ; Form = "History" ; 2 ; 3)

Your third column will present the first visible category, based on the document number, e.g. :

@If(Form = "Dossier" ; Dossiernummer ;
Form = "History" ; dossier_nr_history ;" ")

but this time you sort and categorize.

If I have thought it out well (no client to try on now), then you should get what you want.

Pascal.

 

[Fret4u]

Pascal, Thanks !!! If I could give you three stars for that I would. Allthought in the beginning it didn't work and I was kinda frustrated. And just when I was about to give up I added some thing to the formula for the first (hidden) column. The formula now is: @If(Form="Dossier";@Text(Dossiernummer);Form="History";@Text(dossier_nr_history);" "). Note the "@Text" formula. Now it works perfect. So I was happy and satisfied and started to enter data into the database. But when I tried to retrieve it (search for) I encounterd an "index" problem. This is it: I didn't put the database on my hard disk (c:\) but I have stored the database on a server (on a private disk drive - the server is not a domino or mail server) because this server gets backed up every night. The problem now is that it doesnot get indexed automatically but I have to do that manually (which ofcourse I forget after adding data). From our IT I learned that the indexinging only works locally on the hard disk in the folder C:\lotus\notes\data\ or on the domino server. Is that true ? Can't I get the indexing to be performed automatically (maybe some kind of agent)?

 

[pmonett]

Your database is not stored on a Domino server ? Isn't the Domino server backed up ?

If you store Notes databases outside the server, there is no index task to run on it. Your client (and any other that access the database - beware concurrent access violations !) is the only thing managing it.
It is possible to have background tasks scheduled, but that concerns exclusively agents. The client does not have an indexing task.

You might be able to design an agent that triggers the task - but scheduled agents cannot call UI elements, and I don't think that reindexing is part of db methods - I'll have to look into that.

In any case, I strongly suggest transferring this db to a proper Domino server - especially if the data in it needs to be shared among several users.

Pascal.

 

[Fret4u]

Thanks Pascal for your help and suggestions in this matter. I have a nother problem which I have started a new thread for because it doesn't have to do anything with the formula's in the column. Thanks again.

 

[pmonett]

Glad to be of help.