?
Solved

View column formular

Posted on 2000-03-21
9
Medium Priority
?
671 Views
Last Modified: 2013-12-18
Hi,

I have the following formula which works perfectly in a field in a document. However I want to use it in the column of a view, but when I try, nothing appears.

@Sum(@DbLookup( "" ; "" ; "($Per Period)" ; @Text(Year)+Office+Name ;4 ))

What is going wrong?

Thanks,

Zaphod.
0
Comment
Question by:Z_Beeblebrox
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Expert Comment

by:bennyliaw
ID: 2640550
It is documented that @DbLookup as well as any other @Db commands will not work in view column formula.

What you can do is create a computed field in the form say "Sum" with the formula you want. And then refer to that field name in the view column formula.

One draw back is that the formula is only calculated when you refresh or save the document. If you by some reason need to recalculated the formula you may want to use scheduled agent to recalculate the "Sum" field.
0
 
LVL 7

Author Comment

by:Z_Beeblebrox
ID: 2640782
And how can I refresh just a specific field in a specific document when a document is saved? This database will have hundreds of documents in it so refreshing all is not an option, and the data must be instantly updated.

Thanks,

Zaphod.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 2642880
Hi Zaphod,

Write an agent that runs on selected documents.

Let the code contain,

@Command([ToolsRefreshSelectedDocs])

Now, Select the docs you want to compute the values and use actions and run the agent you have created.

Good Luck !

-Arun

0
Industry Leaders: 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 7

Author Comment

by:Z_Beeblebrox
ID: 2644695
And how do I "select" a document using an agent?

Zaphod.
0
 
LVL 7

Author Comment

by:Z_Beeblebrox
ID: 2644992
OK, I can now refresh the specific document by opening a view to a selected document and then doing the refreshselecteddoc command in the query close event. However I have now run into two problems.

1) It beeps when it does the refresh. Is there any way to stop this?

2) I have totals enabled on the column from where I am grabbing the information, but the total displayed is consistently incorrect. If I open the view in design mode, the total is correct, and the formula which also totals the data is also correct. I have tried refreshing all documents, closing and opening Notes and a few other things, but with no success. Even if I create another document, that number is added to the total, which is still short the same amount. Any ideas?

Thanks,

Zaphod.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 2648504
Hi Zaphod,

Why do you want to use the refresh in the queryclose of the view ?

Try doing it manually by selecting the required documents and running the code from an agent...

Good Luck !

-Arun
0
 
LVL 1

Accepted Solution

by:
bennyliaw earned 80 total points
ID: 2648720
I think why the refresh agent is called from queryclose is that Zaphod wants it to be triggered automatically whenever the documents is updated, because it may change the result on the @DbLookup.
But I don't understand why it beeps when refreshing the documents though. Is there any error message you see in the status bar?

Abt the inconsistency of the total, it could because the view or database corruption. Try create a new replica and see whether it fixes the problem. One other thing to check with you is that whether you have any Reader fields in the form. Even you cannot see the protected documents (because you are not in the reader field), they still constitute to the total.

I have another alternative for refresh agent. It is using lotus script instead of formula. Even though the code is a lot more longer it perform much faster than the Refresh agent.

Dim dcol as NotesDocumentCollection
Dim view as NotesView
Dim session as new NotesSession
Dim curdb as NotesDatabase
Dim doc as NotesDocument

set curdb = session.CurrentDatabase
set view = curdb.GetView( "($Per Period)" )

key = curdoc.Year(0) & curdoc.Office(0) & curdoc.Name(0)
dcol = view.GetAllDocumentsByKey( key )

Dim sum as double
sum = 0
doc = dcol.GetFirstDocument
while not doc is nothing do
   sum = sum + doc.ColumnValues( 3 ) 'fourth column value
   doc = dcol.GetNextDocument( doc )
wend

'Now you have the sum, update all affected documents
'you can select the affected documents by another GetALlDocumentsByKey
doc = affecteddocs.GetFirstDocument
while not doc is nothing do
   doc.Sum = Sum
   Call doc.Save( True, True )
   doc = affecteddocs.GetNextDocument( doc )
wend
0
 
LVL 7

Author Comment

by:Z_Beeblebrox
ID: 2649986
Hi,

When I came in this morning the total problem was fixed. Hopefully it will not happen again.

As far as the refreshing thing goes, using your script as a model since it doesn't actually compile, I was able to produce the following fully functional script:

Sub Queryclose(Source As Notesuidocument, Continue As Variant)
     Dim view As NotesView
     Dim session As New NotesSession
     Dim curdb As NotesDatabase
     Dim doc As NotesDocument
     Dim curdoc As NotesDocument
     
     Set curdb = session.CurrentDatabase
     Set view = curdb.GetView( "($Per Period)" )
     
     Set curdoc = source.Document
     key = curdoc.Year(0) & curdoc.Office(0) & curdoc.Name(0)
     
     Dim sum As Double
     sum = 0
     Set doc = view.GetDocumentByKey( key )
     
     Do While curdoc.Name(0) = doc.Name(0)
          sum = sum + doc.ColumnValues( 3 ) 'fourth column value
          Set doc = view.GetNextDocument( doc )
          If ( doc Is Nothing ) Then
               Exit Do
          End If          
     Loop
     
     Set view = curdb.GetView( "($Reps)" )
     key = curdoc.Year(0) & curdoc.Name(0)
     Set doc = view.GetDocumentByKey( key )
     If doc Is Nothing Then
          Msgbox "There is no record of this person for this year in this database."
     Else
          doc.YTDProduction = Sum
          Call doc.Save( True, True )      
     End If
     
End Sub

I think the only serious problem with yours was that you tried to use a document collection when summing the values in the columns. Apparently this does not work since the view that you are referring to is not clear. As a result, I converted it to the same format as was suggested in the help file. I could also just have grabbed the field directly, but the end result is the same.

I definately prefer the script solution since it is completely invisible to the user, unlike the formula method which beeps and flashes up the progress bar.

Thanks for your help,

Zaphod.
0
 
LVL 1

Expert Comment

by:bennyliaw
ID: 2654232
Don't mentioned!
I am happy to help others
besides I also earn points in return :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

  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…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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